MDX Dynamic parameter syntax

SQL Server Reporting Services

jrock1


I have a SSRS report that I'm passing a dynamic parameter to another SSRS report.

The datasourse is a SSAS cube. Can anyone tell me the if following syntax is correct for receiveing report.

I'm having trouble creating the datasource for the second report. I'm new at SSRS so any help would be appreciated.

When I parse this code in management Studio I get the following

An MDX Statement was expected. An MDX expression was specified.

'= [SELECT NON EMPTY { [Measures]].[Qty Paid]], [Measures]].[US Cost]], [Measures]].[Qty Recd]] } ON COLUMNS, NON EMPTY

{ ([Division_Plant]].[Hierarchy]].[Plant Desc]].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM ( SELECT ( STRTOSET(([

+ " Parameters ! DivisionPlantDivisionDesc.Value + "[), CONSTRAINED) ) ON COLUMNS FROM [New GOLD]])

WHERE ( IIF( STRTOSET(([ " + Parameters ! DivisionPlantDivisionDesc.Value

+ " [), CONSTRAINED).Count = 1, STRTOSET(([ " + Parameters ! DivisionPlantDivisionDesc.Value + " [),

CONSTRAINED), [Division_Plant]].[Division Desc]].currentmember ) )

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS'

Top

Re: MDX Dynamic parameter syntax

Brian Welcker - MS


In general, you actually don't need to create the MDX statement from an expression. Are you not able to mark the levels in the target report as parameters in the query After that, make sure you are passing the memberuniquename properties.






Top

Re: MDX Dynamic parameter syntax

jrock1

Like I said I'm very new to SSRS and SSAS...

Here is my problem. I have a SSRS report that uses a SSAS cube for as it's datasource. I have a drop down parameter box where the user can select a year (2005,2006,2007,etc..) They click the View Report button and the report is generated. On one of the textboxes properties, on the navigation tab I have Jump to Report activated.

What I'm trying to do and can't, is to jump to that report passing the contents of the dropdown box. The receiving report is a SSRS report with a datasource being the SSAS. How do I make the receiving report open based on the parameter that was sent

All ideas are welcome...

Thanks ..



Top

Re: MDX Dynamic parameter syntax

Yao-Jie Tang - MSFT

Hi,

May I know that which tool you used for making these reports, Visual Studio or Report Viewer If you used Visual Studio, let¡¯s go on.

If the reports are running well and you just want to know how to pass the parameter from Report1 to Report2, please try the following steps:

1. There is a button named Parameters besides the Jump to report. Click it.

2. In the Parameter Name, type the name just as the parameter you dim in the Report2.

3. In the Parameter Value, click the expression in the dropdown list.

4. In the Edit Expression window, type the expression like ¡°=Parameters! DivisionPlantDivisionDesc.Value¡±

5. Click OK to finish.

I hope this helps. If there is anything that I misunderstood, please post back here and let me know.

Thanks.





Top