.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

SSRS 2005 with SSAS Parameter is not declared

Posted By:      Posted Date: September 09, 2010    Points: 0   Category :Sql Server
Hello, I'm builind a mdx query to my report in ssrs 2005 with ssas 2005 all on SP3. I'm having the hardest time to add a custom parameter to the query, I add it to the dataser parameters tab ("..." button). Then when I try to use it in my query I get an error: The query uses a parameter, which is not declared. I've seen some examples on the goo.. internet but none seems to work. My query runs fine like this STRTOMEMBER("[dimension].[entity].&[16]") but it fails if I try to do this.. STRTOMEMBER("[dimension].[entity].&[" + @entityid + "]") Am I trying to do impossible? :) Any idea on how to get such filter to work? My filters are a bit complex that's why I can't simply use the "Parameter" checkbox in the query designer mode. Thanks!

View Complete Post

More Related Resource Links

Shared dataset in SSRS 2008 R2 running on SSAS 2005

I am trying to create a report in SSRS 2008 R2 using SSAS 2005 as a datasource. My dataset has a date parameter. When I create an embedded dataset in my report everything works fine, but when I create a shared dataset with the exact same mdx query, I get the following error: An error occurred during local report processing. The definition of the report 'xxxx' is invalid. The Value expression for the report parameter 'DateCalendarHierarchy' contains an error: [BC30034] Bracketed identifier is missing closing ']'. Does anybody have any ideas? Is this a bug? Thanks, Louis

Multivalued parameter & Data driven subscription for SSRS 2005

I need to create data driven subscription for our reports. Most our reports have multi valued parameters (7-12 parameters with at least 4-5 with multiple values)and need to be send to a users between the range of 25-200. So standard subscription is really not a choice.I have gone through most of the threads before. Question 1:Does any one have a solution for the SSRS 2005 regarding passing mutiple values for a parameter for the Data subscription table. If yes, could you please share the information. Question 2:I found some solutions using the SOAP API. Can any one explain that in detail- how we can do it ?Reference:http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/8ab615b4-1373-4258-bf49-c2843cfea8e9/http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=516735&SiteID=1 Question 3:Here is the other solution I found in one of the postsApparently passing in values like {A, B, C} only works when you configure the parameter values not to come from a dataset so that SSRS doesnt check for ValidValues.Reference: http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/b2c50aea-2032-4025-a155-306c00fcb856/Any commends about this solution?Thanks

SSRS 2005 Drillthrough chart Passing Wrong parameter value

Hi I have a drillthrough report that in Report A I have a week of data, showing Sunday through Saturday on the X-axis. When a data point is clicked on this chart, it shpuld pass the date of the weekday for that data point to Report B. But sometimes, it passes the prior weekday date, eg click on the Monday date data point, but Sunday's date is passed to the next report. Has anyone observed this type of behavior? I should mention that it is intermittent.   Thanks all.   Bobrpress

SSRS 2005 - Change 2nd Parameter Value based on 1st Parameter Value (Non-queried)


I am wanting to filter my report based on Fiscal Year.  I have the first parameter as @STARTDATE and 2nd parameter as @ENDDATE.  The values for both of these are hardcoded in non queried.  When the user selects the @STARTDATE , I want to automatically set the value for the @ENDDATE parameter.  I'm doing it this way because I'm using BETWEEEN in my SQL Query.  Ideally, I will just have the @ENDDATE value hidden if possible.

So for example:

@STARTDATE has values of 10/1/2009 and 10/1/2010.
@ENDDATE has values of 9/30/2010 and 9/30/2011.

When @STARTDATE is selected to 10/1/2009, set the @ENDDATE value to 9/30/2010.
When @STARTDATE is selected to 10/1/2010, set the @ENDDATE value to 9/30/2011.

Any idea how/if this can be accomplished?

SSRS 2005 Matrix results affected by defaulting being set on report parameter?


We discovered some very odd matrix behavior a couple days ago and just today found out what in the RDL seems to be causing the issue.  The issue is that certain cells in the matrix will not be populated if a certain report parameter is defaulted.

The basics:  A matrix report with 6 report parms two of which are passed to the underlying procedure call.  There are 6 datasets behind the report.  4 are related to getting date and timespan information to allow for parameter defaulting.  1 gets a list of customers to populate a selection drop down and the last gets the data.

The data query takes a customer name and an end date.   Via the report parameters the end date parm is defaulted based on a dataset which is chained through the other parms and associated datasets.

The matrix is used to show a months worth of activity by day.  There are two int32 data points in each matrix cell one is a total and one is a failure count.  The data as returned by the reporting proc is already summarized so no aggregate is needed in the matrix.  However, using SUM or not in the matrix cell makes no difference to the strange outcome.

When the report is run with the end date parameter being defaulted one of the rows will incorrectly show 16 cells.  The cells appear as if there is no data.  However, if the proc

SSRS 2005 Error: Cannot add multi valued query parameter for data set because it is not supported b



getting the above error when running a report in SSRS 2005, using a stored procedure with many input parameters,

connected to a Sybase IQ database,

I want to have some of the prompts to be multi valued prompts.......

Please advise on how to fix......



howto: display only hierarchy-members (levels) which are selected in the parameter (SSAS and SSRS)


Hi all,

I'm a SSRS/MDX beginner, so this might be a basic question. Sorry for that.

I have a hierarchy, which I use as a parameter. The Hierarchy has 3 levels (level1, level2, level3).

Sample Hierarchy

Now I want to achieve the following:

  • If a user selects CH1 --> the report should show only the CH1 (which is the sum of CH11 + CH12 + CH13)
  • If a user selects CH11 and CH12 and CH13 --> the report should show only these leafs (no aggregations)
  • If a user selects CH1 and CH11 and CH12 and CH13 --> the report should show the leafs and CH1 (seperate)

I guess, this is not that hard, but somehow I don't get it.

Here my sample mdx-query (the hierarchy I'm writing about is "Lieferantenstruktur" and the parameter "@Lieferant" (BOLD)):
(right now, only the leaf-level [Lieferant] is returend by the query)

SELECT NON EMPTY { [Measures].[Einheiten] } 

, NON EMPTY { ([Lieferantenstruktur].[Lieferantenstruktur].[Lieferant].ALLMEMBERS 
* [Artikel].[Artikel Kategorie].[Artikel Kategorie].ALLMEMBERS 
* [Artikel].[Artikel Gruppe Code Name].[Artikel Gruppe Code Name].ALLMEMBERS 
* {[Kundengruppen].[Kundengruppen].[Verkaufskanal].ALLMEMBERS 

YTD with custom query parameter (SSRS with SSAS)


Hi all,

I have a report which has SSAS as DataSource.

I also created a custom query parameter for date.
I am using the parameter type "date/time" (so I get the nice calendar for the parameter). Of course I have to convert this to a valid member of the SSAS hierarchy:

="[Datum].[Jahr - Halbjahr - Quartal - Monat - Datum].[Datum].[" + Format(CDate(Parameters!Datum.Value), "MMM d, yyyy") + "]"

So far, so good.

But I don't get the YTD function to work.
It should work like this.

WITH MEMBER [Measures].[Current YTD] AS

SUM(YTD([Datum].[Jahr - Halbjahr - Quartal - Monat - Datum].CurrentMember),[Measures].[Einheiten]) MEMBER [Measures].[Last YTD] AS SUM(YTD(ParallelPeriod([Datum].[Jahr - Halbjahr - Quartal - Monat - Datum].[Jahr],1,[Datum].[Jahr - Halbjahr - Quartal - Monat - Datum].CurrentMember)),[Measures].[Einheiten])

I tried to use STRTOMEMBER(@Datum) and STRTOSET(@Datum). But I guess reference to the hierarchy is missing.

Any tipps, how to get that working.

Thx a lot.

Reto E.

MDX Query parameter from SSRS


I've a MDX Query that has where clause as shown below.
I'm designing report using SSRS 2008. How can i pass date as parameter ? I tried to setup @from and @to as parameter but not working ?
any ideas....

WHERE ( {[Date Central].[Calendar Date].[2010-04-01 00:00:00]:[Date Central].[Calendar Date].[2010-08-30 00:00:00]} )

need it to work as
WHERE ( {[Date Central].[Calendar Date].[@From]:[Date Central].[Calendar Date].[@To]} )



Is Conditional Subscription possible in SSRS 2005?


Hi Guys,

Is there a way to set up a subscription that will send e-mails based on the contents of a report? For example, I want to schedule a daily subscription that will only send an e-mail if the report contains data, otherwise it will do nothing for that day. i.e If the report has more than one row, it should send the mail otherwise it should not send the mail.....

Is it possible in SSRS 2005 or is there any other way to do it???


SSRS 2005 "Mixed" Authentication Mode


I have a web application where users can authenticate using either SiteMinder or Windows domain accounts.  This is actually configured as two separate virtual directories within IIS 6.  Both applications need to use the same instance of SSRS.  Is it possible to configure the SSRS web application so that if a user is authenticated by SiteMinder it impersonates a Windows account, but if they are already Windows-authenticated that account is used instead?

Kerberos between MOSS 2007 and SSAS 2005


I realize this is probably going to be one of those vague questions that I am not going to get much help on here, but I thought I'd give this a shot before we go the MS Incident route on monday.

We have tried to setup Kerberos between MOSS 2007 AND SSAS 2005 to no avail.  We have been through the knowledge base articles outlining the setup multiple times with all the experts on MOSS and Security here where I work.  We've used other materials we have on kerberos here.  But the end result is that the double hop is not happening.  We are trying to connect three ways: excel services, ssrs 2005 in integrated mode, and Sharepoint KPI's (using analysis services).  In every case the connection is not happening.

Other details are that the ssrs integrated mode seems to be setup right because I do get a report (albiet all it has is a connection error message).  Excel services works fine if I use the unattended service account, but when I switch the odc file to windows (should cause kerberos to kick in) it fails.  When I try to add a kpi to the kpi list it can't retrieve a list of kpi's from ssas.

In all cases I am the user trying to perform these operations, and I have total access to the cube -- I'm the developer.  I have no problems connecting to the cube directly through excel, so the security at that end passes t

Data validation for datetime parameter in SSRS

Hi,   I wanted to know more about validation of SSRS parameters. I have a simple report which has a parameter called startdate of DateTime datatype. The datetime parameter in SSRS takes manual input as well. So, the user can enter any junk value. I want to ensure that the input parameter is in correct format and I want to display an error msg when the format is incorrect. My report has the following VB code for validation:   Public Function Validate( ByVal startdate As String) As BooleanIf IsDate(startdate) = True Then Return TrueElse Return FalseEnd IfEnd Function   And my report has a textbox which has the expression property set to; =Code.Validate(Parameters!startdate.Value) the textbox on the report has to display if the entered date is valid or not.   But, when i enter an erroneous date, SSRS doesn't render the report and throws a generic error. This happens even before the code written for validating the parameter executes.   Also couldn't find a way to disable the manual input for the datetime parameter. Even that would solve the problem.   Another alternative was to make the startdate parameter as string, but i want the calendar control button to be provided for the user.

Report printing problem(ssrs 2005)

HI   i have a SSRS 2005 report with width as 32 inches i have a requirement to print the report in a3 landscape  but when i print the report it comes in 3 page width   is there any way to auto scale the report into a3 size without affecting the view in the report viewer   Thanks varun kumar

ssrs 2008 R2 - report parameter with no default set throws java script error

We have recently upgraded from SQL Server 2008 to SQL Server 2008 R2....I am experiencing a problem with my report parameters on my SSRS reports. It seems that my report parameters that I do not have a specified 'default' value throws a java script error when I try and run/render the report under R2.  As soon as I put a 'default' value on the report param, everything is fine.  However, that is not the behavior we need for our reports...the user needs to pick their selection. Can anyone shed some light on this, it seems to be an AJAX problem...I need to be able to have report parameters with a list of values, but no 'default' value on the parameter.

ssrs 2005 default value generates report

Hi, If all parameters have default value the report generates as soon as i load the report. Is it possible to only run the report when clicking on the view report button even if all parameters have default values? Thanks

Question regarding IIF statement and Grouping in SSRS 2005

Hi Guys, I am using SSRS 2005. I am facing small prob in using IIF statement to hide a column. I am having a Grouping in my table. (Emp_Group and the values are 'Yes' and 'No') Now, I want to hide a column of the table if Grouping is Yes and I want to show the table if Grouping is No. (When exported to excel it will appear in two different sheets). Any suggestions please............
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend