locked
How to return a list on a certain level RRS feed

  • Question

  • Hi,

    I´m quite new to SSRS...hope you can help me...How do I return a list to a parameter - but the names (not the values!) of the list shall only be of the "2nd level", not including children (the "3rd level"). The values, however, shall be calculated from the values of the 3rd level.

    Bye

    Michael

    Friday, February 9, 2018 10:28 AM

Answers

  • Hi ,

    To retrieve a certain level is a mdx question .. Try to use the descendants function : 

    https://docs.microsoft.com/en-us/sql/mdx/descendants-mdx


    Regards, David .

    • Marked as answer by Tontaube Tuesday, February 13, 2018 12:05 PM
    Monday, February 12, 2018 10:22 AM

All replies

  • Sorry didnt understand your scenario. what is 2nd level 3rd level etc?

    Please eloborate with some sample data and explain how you want values to be filtered based on your parameter


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, February 9, 2018 10:31 AM
  • Hi,

    it´s the different hierarchy-level of the Dimensions in the Datacube...e.g. [Geschäftsfelder].[Geschäftsfelder].[Geschäftsfeld].&[IT].

    Bye

    Michael

    Friday, February 9, 2018 11:52 AM
  • Hi,

    You could use the split function to pass multiple values to the parameter like:


    =Split(Fields!YourFieldName.Value.ToString(),",")

    If you are using the cube, you have to pass the context to the lookupcube function. The context is defined by parameters (subselect), the slicer axis (where) and the row context (dimensions on rows). Lookup cube returns a scalar that is calculated as an aggregation of provided subspace. So you need to pass the context in the form of a crossjoin of parameters and current row member(s).

    Reference: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/cd0c39d5-697d-42bc-a45e-cdb25168b56b/lookupcube-and-multiple-values-multi-select-parameters-of-same-dimension?forum=sqlanalysisservices

    BR,

    Henry 

    Monday, February 12, 2018 2:14 AM
  • I´m new to SSRS. Would it also be possible to just format the returned list so that it shows only the "2nd level" (i.e. without subgroups) in the Dropdown-List I get? If so, how?

    Monday, February 12, 2018 10:05 AM
  • Hi ,

    To retrieve a certain level is a mdx question .. Try to use the descendants function : 

    https://docs.microsoft.com/en-us/sql/mdx/descendants-mdx


    Regards, David .

    • Marked as answer by Tontaube Tuesday, February 13, 2018 12:05 PM
    Monday, February 12, 2018 10:22 AM
  • Thank you!
    Tuesday, February 13, 2018 12:05 PM