Using parameters in SSRS 2008 with SSAS as datasource

  • My report pulls data from ssas 2008. I have a parameter that populates the drop-down by a query. By default ssrs adds a (Select All) to the drop-down and the mdx query also returns an All member as part of the result set. I need to remove one of the All parameters so the users aren't confused. Any ideas? I've tried a few things and haven't been able to successfully remove either one.

    Is there a way to filter the [All] member from the MDX result?

  • It depends on whether you're querying a dimension or a hierarchy. With a dimension it's easy, just change [Dimension].members to [Dimension].children. For the hierarchy, it depends on how many levels you want. You could try [Hierarchy].[All].Children, or the Descendants() function, or the Except() function.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Drew,

    Thank you for your response, but I'm not sure I understand exactly what you are referring to. My SSRS report uses an SSAS datasource with an mdx query containing two parameters @param1, @param2. The parameters are attributes of a dimension. Do the report parameters use the same mdx query uses to pull the required measures, etc? In the query parameters window (from query designer), there is no way to filter the "[All]" member of the dimension. The only mdx used as the dataset source is the only query I can modify. Is this what I need to change?

    Thank you for your help.

  • The "Available Values" for your parameter is set to "From a Query". It is this query that you need to change.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • I figured it out.... Finally. Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply