none
SSRS MDX : Dimension Attribute selection dynamically.

    Question

  • Hi All, 

    Is it possible to select Attribute name dynamically in MDX with SSRS Reports. Did any one came across a similar requirment as bleow.

    I have requirement to create report using mdx, considering [Adventure Works] cube to explain the requirement.  Product Dimension has two attributes Category and Style, these two attributes are not related in any way but are part of same dimension. Considering only below two attributes (Category, Style,.....) to make it simple, this list can go up to 10.

    Category has members (Accessories,Bikes,Clothing,Components).
    Style has members  (Mens,Unisex,Womens)

    I am intrested in finding OrderCount by any of the above.

    Requirement is user will select the Category or Style (these two values are hardcoded in the report parameters list) from drop down in the report, If user select Style, Dataset should have below value

    Type       Order Count
    Mens        8,240
    Unisex     17,287
    Womens  3,711

    If user selects Category dataset should have

    Type            Order Count
    Accessories   18,208
    Bikes            15,205
    Clothing        7,461

    Thank You for your time, please refere to any blog or post which has similar implementation.

    Praveen

    Monday, July 01, 2013 12:10 PM

Answers

  • Hi Mark,

    I have managed to implement it in the report, but not the same way as I tried/mentioned earlier. Its bit tedious.

    1. Created MDX dataset with two  parameters (Category and Style). SSRS created two datasets for mdx parameter like CategoryDataSet for Category and StyleDataSet for Style.

    2. Create another report parameter @SelectType with two available values Category and Style.

    3. Create another dataset which has two columns Category and Style. Created third caliculated field SelectTypeColumn using if condition, if @SelectType  = Category calicualte field will have Category list else Style list.

    4. Create another report parameter @TypeValue using second datasets caliculated field SelectType. Now TypeValue can either have list (Mens, Unisex, Womens  or Accessories, Bikes, Clothing)

    5. Now filter the datasets created by SSRS in step one based on the user selection for @SelectType. If user selects Category for @SelectType, apply @SelectType  value for CategoryDataSet and show all for StyleDataSet.

    I will create a walk through document some time later, thanks for your earlier response.

    Praveen.


    Praveen

    Friday, July 05, 2013 1:47 PM

All replies

  • Praveen,

    There are probably more than a few ways to do what you want.  Here are a couple of ideas:

    1-create one table for category and one for style.  Hide and show them based upon the user's selection.  If the datasets are small, this is probably the easiest thing to do.

    2-Use dynamix MDX with an openquery to the cube.  This would allow you to swap out the column as a parameter, while calling a stored proc.

    Note that in SSRS, dimension attributes are specified in the definition of the dataset field, so it's not quite like SQL where you could just use a case statement to bring back the column that you want.  SSRS will be expecting the design time column to be returned.

    Hope that helps,

    Mark

    Monday, July 01, 2013 12:37 PM
  • Thank You for the response Mark,

    But can't go with option 1, as these attributes Category and Style can increase to any number(cant create table for each), wont be able to use openquery as our applications architecure wont support this.

    Currently looking into option for using dynamic MDX some way from report, using below query for creating the dataset in SSRS, all I need to do is replace the highlighted text depending on the user selection in report parameter.

    Select
    [Product].[Category].Children on Rows,
    [Measures].[Internet Order Count] on Columns
    from
    [Adventure Works]

    or

    Select
    [Product].[Style].Children on Rows,
    [Measures].[Internet Order Count] on Columns
    from
    [Adventure Works]

    Still looking into options considering what you mentioned earlier " in SSRS dimension attributes are specified in the definition of the dataset field, its not same as SQL"

    Praveen.


    Praveen

    Tuesday, July 02, 2013 8:04 AM
  • Hi Praveen,

    If category and style (or whatever attributes you are actually reporting on) have a high cardinality or are liable to grow to have have 1000s of values, they might not be a good candidate for a report -- or could, perhaps, use some filtering.  It may be worth a test to see how the performance is using the hidden table method.

    As for openquery, another alternative might be openrowset, if it is the linked server that is an issue.  Here is some info on that option: http://support.microsoft.com/kb/218592?wa=wsignin1.0

    Another option, would be to bring back both category and style in your query.  Then use SSRS to display one value or the other in a textbox or group.  This, however, would likely produce more rows than the first option for all the combinations of style and category.

    Lastly, a couple of notes on the MDX piece.  What you could easily do in SSMS is run a query like this:

      

    WITH SET [Report set] as
        Case WHEN 1=1 //in SSRS - @Parameter = "Style"
      THEN [Product].[Style].[Style]
      ELSE [Product].[Category].[Category] END

    Select 
        {[Measures].[Internet Order Count]} on Columns,
        NON EMPTY {[Report set]} on Rows
    FROM [Adventure Works]

    Note that SSRS will only accept the measures on the columns.  Also, note that the Non Empty keyword will return non empty rows which will make your query much more efficient.  When you run this, double click one of the style columns in the results (i.e. unisex).  You will see that the Level is this [Product].[Style].[Style].  If you change the case statement to 1=2, then the level will be [Product].[Category].[Category].  

    When you are using MDX with SSRS, it will store the metadata of the field with whatever it is at design time. If you try to change it dynamically with a parameter, this will throw an error.  

    Please see if one of the above methods will work for you,

    Mark


    Tuesday, July 02, 2013 11:30 AM
  • Hi Mark,

    I have managed to implement it in the report, but not the same way as I tried/mentioned earlier. Its bit tedious.

    1. Created MDX dataset with two  parameters (Category and Style). SSRS created two datasets for mdx parameter like CategoryDataSet for Category and StyleDataSet for Style.

    2. Create another report parameter @SelectType with two available values Category and Style.

    3. Create another dataset which has two columns Category and Style. Created third caliculated field SelectTypeColumn using if condition, if @SelectType  = Category calicualte field will have Category list else Style list.

    4. Create another report parameter @TypeValue using second datasets caliculated field SelectType. Now TypeValue can either have list (Mens, Unisex, Womens  or Accessories, Bikes, Clothing)

    5. Now filter the datasets created by SSRS in step one based on the user selection for @SelectType. If user selects Category for @SelectType, apply @SelectType  value for CategoryDataSet and show all for StyleDataSet.

    I will create a walk through document some time later, thanks for your earlier response.

    Praveen.


    Praveen

    Friday, July 05, 2013 1:47 PM