locked
Can you use a hierarchy as a parameter in SSRS? RRS feed

  • Question

  • All,
     
    I was attempting to create a SSRS report and then will take that report and add it into performance point.  In PerformancePoint, I am able to have one dropdown parameter list with a tree like hierarchy coming from a SSAS cube.  For instance, my geography parameter in PerformancePoint is one drop down that goes from Region > Area > Country in a tree form.

    In SSRS I don't see any way at all to create ONE parameter that has a dropdown in this form.  It seems like I have to bring in each component separately.  So I would have one Region drop down, One Area dropdown and one Country drop down.  Yes I can make them cascading.  But is there

    1) ANY way at all to create one parameter from a SSAS hierarchy and if NOT then

    2) How can I link the SSRS report into my dashboard filter that is one drop down?  I do NOT want the users to have to select from region, then from area then from country.

    Thanks,
    Mindy
    Monday, February 16, 2009 8:46 PM

Answers

  • Yes.  You can do this using an MDX query and expressions to build-out the levels of a user hierarchy by using Dimension.Hierarchy.CurrentMember.Level.Ordinal in a calculated member to return a generated report dataset field.  This is used to pad the appropriate number of spaces in front of each member in a multi-valued parameter list.  Fortunately, the easiest way to accomplish this is to let the MDX query designer do it for you.

    Define a data source using the Analysis Services type/processing extension.
    Create a dataset with this data source to invoke the MDX query designer.
    Drag attributes, hierarchies & measures to create the query.
    Drag a user hierarchy into the slicer pane at the top of the query designer.
    Scroll to the right and check the Parameter box.
    The rest is magic performed by the report designer - a hidden MDX dataset is created to build the hierarchal checkbox list parameter tree. 

    If you want to replicate this manually, you can pick apart the MDX query used to populate the parameter list.  In the report designer, check the field expressions for the parameter list dataset.  All of the necessary logic has been defined using VB expressions and generated fields.
    Paul Turley, Hitachi Consulting
    Tuesday, February 17, 2009 9:16 AM

All replies

  • Yes.  You can do this using an MDX query and expressions to build-out the levels of a user hierarchy by using Dimension.Hierarchy.CurrentMember.Level.Ordinal in a calculated member to return a generated report dataset field.  This is used to pad the appropriate number of spaces in front of each member in a multi-valued parameter list.  Fortunately, the easiest way to accomplish this is to let the MDX query designer do it for you.

    Define a data source using the Analysis Services type/processing extension.
    Create a dataset with this data source to invoke the MDX query designer.
    Drag attributes, hierarchies & measures to create the query.
    Drag a user hierarchy into the slicer pane at the top of the query designer.
    Scroll to the right and check the Parameter box.
    The rest is magic performed by the report designer - a hidden MDX dataset is created to build the hierarchal checkbox list parameter tree. 

    If you want to replicate this manually, you can pick apart the MDX query used to populate the parameter list.  In the report designer, check the field expressions for the parameter list dataset.  All of the necessary logic has been defined using VB expressions and generated fields.
    Paul Turley, Hitachi Consulting
    Tuesday, February 17, 2009 9:16 AM
  • Thanks Paul, I will give this a try.

    Mindy
    Wednesday, February 18, 2009 8:23 PM
  • I dragged my Geography Hierarchy that is under my Dim Geography to the slicer, selected MDX and dragged up my hierarchy so I got [Dim Geography].[Geography Hierarchy] then I added the CurrentMember.Level.Ordinal so it looks like

    [Dim Geography].[Geography Hierarchy].CurrentMember.Level.Ordinal

    The syntax was successful but when it tried to run my query again I got the error:
     
            The Axis0 function expects a tuple set expression for the argument.  A string
            or numeric expression was used

    You'll have to forgive me my MDX skills are in the beginning stage.

    Thanks,
    Mindy

    Wednesday, February 18, 2009 8:35 PM
  • You don't need to modify the MDX at all.  When you check the Parameter box in the slicer pane, a new dataset is automatically generated with the script I mentioned.  Unless you need to hand-roll the query script, I would recommend that you just let the graphical query designer do the work for you.

    If you do find that you need to build a solution by hand (which is unusual) you can use the query builder tool to create the datasets and parameters and then pick them apart to learn the mechanics.  This is where you will see the expressions I referred to earlier.  If you find that you need a reference to help with this, chapter 9 of our Wrox 2008 book covers this thoroughly.
    Paul Turley, Hitachi Consulting
    Wednesday, February 18, 2009 8:51 PM
  •  When I drag my hierarchy over there, initially the parameter checkbox is there...when I change it from Equal to MDX, then the parameter checkbox disappears.  Am I doing something wrong?

    Thanks again!
    Wednesday, February 18, 2009 11:32 PM
  •  

    Hi Mindy,

     

    Thank you for your posting.

     

    Unfortunately the tree parameter control is not supported in the current version of SQL Server Reporting Service.

     

    I suggest that you could submit your feedback here:

    https://connect.microsoft.com/SQLServer/feedback

     

    I also suggest trying this solution to solve the issue:

    1.    In main report, display the country in hierarchy.

    2.    Link “country” to sub report.

    3.    The sub report is the main report in current situation.

     

    If anything is unclear, please feel free to ask.

     

    Thanks,

    Jin


    Jin Chen - MSFT
    Thursday, February 19, 2009 1:10 AM
  • Jin,

    Thanks for the information.  Unfortunately I have 6 dimensions, one with 5 levels deep, the other 2 levels deep, I really don't want to do subreports for each of these.

    I am really struggling here because what I am trying to do is pull everything into a PerformancePoint dashboard utilizing these parameters and then publishing out to Sharepoint and creating webparts and making a web interface from this dashboard.  It appears that the performancepoint parameters and the report parameters that are coming in have to be in the same format, and tree form is the best way but SSRS doesn't support that, so I am utilizing Excel Services and that allows me to.  This being said then I have another issue is that one of my dimensions contains @ 9000 entries and I can't find a way to break this down...it seems like there just isn't consistency across PerformancePoint, SSRS, ReportBuilder or Excel.

    Thanks,

    Mindy

    Thursday, February 19, 2009 1:54 AM
  • Is new version of version of SQL Server Reporting Service will provide this tree parameter control? Is that version Denali ?

    Sumedh Save

    Saturday, November 26, 2011 3:52 AM
  • Still no answer?
    Wednesday, October 17, 2012 11:21 AM
  • Paul, exactly how do you do this using BIDS and Analysis Services?

    I get the idea but not the specifics. Any chance you can do step by step instructions for a simple 3 levelled heirarchy data set?

    Specifically how do you create a "...Dimension.Hierarchy.CurrentMember.Level.Ordinal in a calculated member to return a generated report dataset field"??

    As you can see I do not fully understand Analysis Services yet, and I cannot find a decent example anywhere on the net that shows how do do this for an SSRS parameter.

    A good example would show how to do this using BIDS.

    A step by step guide (with pictures) is called for and would really be appreciated.

    Wednesday, June 12, 2013 5:37 AM
  • You can refer the below link on how to create a tree view parameter in ssrs 

    http://sqlmall.wordpress.com/2014/04/08/tree-view-ssrs-parameter-for-analysis-services-cube/ 

    Wednesday, April 9, 2014 4:03 AM
  • Link now dead

    Web dev and DBA

    Tuesday, September 13, 2016 1:20 PM
  • can you PLEASE make a blog post or something about this and post it here?
    Wednesday, September 13, 2017 11:57 AM