locked
How do I write Parameter using MDX query? RRS feed

  • Question

  • I am working on one report using Cube as Data source.

    There is month Dimension. I want Month Name as one of the parameter in report and there is another parameter in the report which basically store the numbers of the month we want to go back in the report from the selected month from the drop down.

    I don't know how can i work this out with this two parameter in the report.

    Thursday, January 5, 2012 2:31 PM

Answers

  • Hi Sam_1016,

    To this kind of requirement, you need to create the query string at run-time. However, the Analysis Services data source in Reporting Services doesn't allow you to use an expression to define the query string. So you can use an OLE DB provider to connect to the cube and then build up the query string by using an Dynamical MDX expression to achieve this. Firstly you have to create a OLE DB datasource, then give a dynamical MDX query based on your parameter.

    Just to your MDX query, could you please have a check to parameter @DimMonthYearMonth, it should be same as LagMonth syntax.

    You can refer to this about the dynamical MDX http://sqlblog.com/blogs/stacia_misner/archive/2010/10/08/29249.aspx

    Thanks,
    Challen Fu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Challen Fu Sunday, January 15, 2012 3:34 PM
    Tuesday, January 10, 2012 9:08 AM

All replies

  • Thursday, January 5, 2012 2:49 PM
  • Sam,

    You can refer below MDX Query

     SELECT 
    	NON EMPTY 
    	{ 
    		[Measures].[Sales Amount] 
    	} ON COLUMNS, 
    	NON EMPTY 
    	{ 
    		([Dim Product].[English Product Name].[English Product Name].ALLMEMBERS ) 
    	} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
    FROM 
    	(SELECT(STRTOSET(@Month, CONSTRAINED)) ON COLUMNS 
    	FROM [Adventure Works DW2008R2])
    


    Where @Month will be be your Report Parameter which you need to map with data set parameter.

    Make sure you will be passing unique member name to @Month parameter like "[Date].[Calendar].[Month].&[Monthkey]"

    Please refer StrToSet (MDX) for more information


    Thanks,
    Sandip Shinde
    Please remember to mark the replies as answers if they help you and unmark them if they provide no help.
    • Proposed as answer by Manish_Kaushik Thursday, January 5, 2012 3:28 PM
    Thursday, January 5, 2012 2:56 PM
  • I am not Quite Clear.

    I have @YearMonth (june 2010, july 2010......) created from the dataset.  and another custome parameter @lagmonth contains (12,24,36,48,60), which basically indicates if user wants reports that contain past 12 months records or 24 months based on @lagmonth records depending upon the selection of the @yearMonth from drop down list. 

    not i am trying to use  something like below in MDX Query.

     

    LastPeriods([Parameters!LagMonths.value],[Dim Month].[Year Month].[Year Month].&[Parameters!FirstMonth.Value] )

    it gives error like " The dimension '[Parameters!LagMonth.value]' was not found in the cube when the string, [Parameters!LagMonth.value], was parsed."

     

    I really don't know how to get through this. I am new to MDX.

    So, if anybody have better solution, kindly send me the hints.

     

    Thanks,

    Thursday, January 5, 2012 9:30 PM
  • not i am trying to use  something like below in MDX Query.


     LastPeriods([Parameters!LagMonths.value],[Dim Month].[Year Month].[Year Month].&[Parameters!FirstMonth.Value] )

     

    it gives error like " The dimension '[Parameters!LagMonth.value]' was not found in the cube when the string, [Parameters!LagMonth.value], was parsed."

     

    I really don't know how to get through this. I am new to MDX.

    Sam, above MDX expression seems to be totally dynamic depending on your Parameter Selection. You need to write your Data Set Query as expression as shown in below screen.

    Where in Expression you will write below sample MDX Expression

    ="SELECT " +
     "	NON EMPTY " +
     "	{ " +
     "		[Measures].[Sales Amount] " +
     "	} ON COLUMNS, " +
     "	NON EMPTY " +
     "	{ " +
     "		([Dim Product].[English Product Name].[English Product Name].ALLMEMBERS ) " +
     "	} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS " +
     "FROM " +
     "	(SELECT LastPeriods("+ Parameters!LagMonths.Value +",[Dim Month].[Year Month].[Year Month].&["+ Parameters!FirstMonth.Value +"]) ON 0 " +
     "  FROM [Adventure Works DW2008R2])"
    

    Let me know if you have any queries.

    Thanks,
    Sandip Shinde
    Please remember to mark the replies as answers if they help you and unmark them if they provide no help.
    Friday, January 6, 2012 9:13 AM
  • This is the Query i am using.

    =

    "SELECT " +

     

    " NON EMPTY " +

     

    " { " +

     

    " [Measures].[Customer_Cash_rebate] " +

     

    " } ON COLUMNS, " +

     

    " NON EMPTY " +

     

    " { " +

     

    " (" +

     

    " [Dim Month].[Year Month].[Year Month].ALLMEMBERS " +

     

    " * [Dim Month].[Year].[Year].ALLMEMBERS " +

     

    " * [Dim Month].[Month Number].[Month Number].ALLMEMBERS " +

     

    " *[Dim Month].[Month Name].[Month Name].ALLMEMBERS " +

     

    " * [Dim Brand].[Name].[Name].ALLMEMBERS " +

     

    ") " +

     

    " } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS " +

     

    "FROM " +

     

    " (SELECT LastPeriods("+ Parameters!LagMonth.Value +",[Dim Month].[Year Month].[Year Month].&["+ Parameters!FirstMonth.Value +"]) ON 0 " +

     

    " FROM [Cube])"

     

    Getting below error

    an

    error

    occured during local report processing

    An

    Error

    has occured dunring report Processing

    Cannot

    set the command test for dataset

    'Dataset1'

    Error

     

    during processing of the commandtext expression of dataset

    'Dataset1'

     

    Friday, January 6, 2012 2:12 PM
  • Get the query you used in rdl using profiler is it working  fine in MDX editior ?, May be this  error due to syntax. 

    or

    Post your Query you got through profiler


    Sanjeewan
    Friday, January 6, 2012 2:17 PM
  • Sam,

    Can you please check with your expression? is there any syntax error?

    One more things needs to consider while designing reports with dynamic MDX, your field list will not populate automatically.

    Another tip to tech your dynamic MDX query, add one text box to your report (without dataset), put your expression to your textbox. Execute report >> export to PDF >> Copy MDX query genrated dynamically >> Execute with SQL Server Management Studio.


    Thanks,
    Sandip Shinde
    Please remember to mark the replies as answers if they help you and unmark them if they provide no help.
    Friday, January 6, 2012 2:46 PM
  • I have changed the syntax at the end.

    =

    "SELECT " +

     

    " NON EMPTY " +

     

    " { " +

     

    " [Measures].[Customer_Cash_rebate] " +

     

    " } ON COLUMNS, " +

     

    " NON EMPTY " +

     

    " { " +

     

    " (" +

     

    " [Dim Month].[Year Month].[Year Month].ALLMEMBERS " +

     

    " * [Dim Month].[Year].[Year].ALLMEMBERS " +

     

    " * [Dim Month].[Month Number].[Month Number].ALLMEMBERS " +

     

    " *[Dim Month].[Month Name].[Month Name].ALLMEMBERS " +

     

    " * [Dim Brand].[Name].[Name].ALLMEMBERS " +

     

    ") " +

     

    " } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS " +

     

    "FROM " +

     

    " (SELECT LastPeriods("+ Parameters!LagMonth.Value +",[Dim Month].[Year Month].[Year Month].&[( STRTOSET( + @DimMonthYearMonth + , CONSTRAINED) )]) ON 0 " +

     

    " FROM [Cube])"

    It Doesn't give me any error. but when i preview the report there is no Data available. Actually I am Doing Line Chart.

     

    • Proposed as answer by bjotings Monday, November 7, 2016 12:39 PM
    Friday, January 6, 2012 4:39 PM
  • Basically I am Building Line chart report.

    I want to go back in the reports (12,24,36,48) months  depending upon the selected Month value from the Drop down list.

    Do you have better idea to do that?

    Friday, January 6, 2012 9:53 PM
  • Hi Sam_1016,

    To this kind of requirement, you need to create the query string at run-time. However, the Analysis Services data source in Reporting Services doesn't allow you to use an expression to define the query string. So you can use an OLE DB provider to connect to the cube and then build up the query string by using an Dynamical MDX expression to achieve this. Firstly you have to create a OLE DB datasource, then give a dynamical MDX query based on your parameter.

    Just to your MDX query, could you please have a check to parameter @DimMonthYearMonth, it should be same as LagMonth syntax.

    You can refer to this about the dynamical MDX http://sqlblog.com/blogs/stacia_misner/archive/2010/10/08/29249.aspx

    Thanks,
    Challen Fu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Challen Fu Sunday, January 15, 2012 3:34 PM
    Tuesday, January 10, 2012 9:08 AM