I have a requirement for an SSRS report to dynamically change the category of a bar chart based on levels in an SSAS hierarchy. So when a user selects a level from a parameter, the bar chart category needs to change the category group expression to use a different fields in the SSAS dataset. We are trying to keep all of our datasets simple by using the SSAS dataset query designer as opposed to writing MDX. With that, the attribute levels returned in the dataset must remain fixed. This means when the chart category expression changes to a different column/level in the dataset, we must use the aggregate function for the chart values. This all sounded good until the report is run. Since the category group expression is using a conditional expression to dynamically set the category group value, the aggregate function is expecting the scope parameter set explicitly. Setting the scope parameter to the dataset name forces the dataset to only return a single aggregate value from the query so the category values in the chart are all the same value. Hopefully I've explained this well enough. What suggestions are there to solve the problem of dynamic chart categories with an SSAS dataset using the query designer. I appreciate the help in advance.Jeff T Jones

Hi Jeff Jones, Please refer to the following article, and check if it works for you. Using Dynamic MDX in Reporting Services: Part 1: http://sqlblog.com/blogs/stacia_misner/archive/2010/10/07/29231.aspx. Using Dynamic MDX in Reporting Services: Part 2: http://sqlblog.com/blogs/stacia_misner/archive/2010/10/08/29249.aspx. Please feel free to let me know if I misunderstand. Thanks, Eileen

Thank you Eileen. I know this can be done easily by using the generic query editor and writing MDX but we're trying to keep the technical bar low end empower the users to create and maintain reports like these without the need to write MDX queries. The blogs you sent above are for writing MDX queries. We are trying to push a self-serve model by pushing reports out to the businesses which typically don't have highly technical users. I've tried several different ways to make this happen using the graphical query editor and was hoping I was missing something. In a chart's category group I can create an expression to change the field used at runtime but that breaks the Aggregate function in the chart values attempting to pull the aggregate values from the SSAS query.Jeff T Jones