Let’s say you use a query builder to create an MDX query for a report. Building the query is pretty intuitive: you just drug and drop measures and dimension levels on the grid. After you build your query, most probably, you will want to create filters, which is also pretty intuitive. What not intuitive is what created behind the scene.
Let’s create any simple MDX query Using Adventure Work cube, let’s also create three filters: Geography.Country, Product.Category and Reseller.AnnualSales. The query itself is not important because I want to show how filters work, and what happenes if you change them. For all of the created filters let’s check the “Parameters” checkboxes. Checking the checkboxes will create dataset parameters and report parameters one parameter for each filter (don’t confuse report parameters with the dataset parameters). As the result of all these steps, VS creates three dataset parameters for the “main” dataset: (GeographyCountry, ProductCategory and ResellerAnnualSales) and three report parameters with the same names. The dataset parameters use the report parameters to pass the values selected by the user.
If you expand your dataset dropdown box you will see four datasets. The first one has the name similar to the name of your data source or your database name, for example: “Adventure_Works_DW_Standard_Edition”, let’s call this dataset “main” dataset, because this dataset is used for the report you are creating and this dataset contains all the “Fields” you will use in your report. The MDX query of this dataset is generated automatically by the query builder and it contains parameters also generated automatically according to the filters you created. In our case the names of the parameters would be: GeographyCountry, ProductCategory and ResellerAnnualSales. Since we checked the “Parameters” checkboxes in the query builder the report parameters will be generated for each of the filters, and the report server will generate special controls at run time (most probably dropdown or list boxes) which will contain all possible values of the Countries, Product Categories and Annual Sales. The second, third and forth datasets were created to pull the default values from the database. It is very convenient for users, instead of typing the values; they have a multiple choice to select from.
Now if you try to investigate the second third and forth datasets you will see that the second dataset, created to pull all values for the first filter (which pulls all existing Countries) contains no parameters:
WITH MEMBER [Measures].[ParameterCaption] AS ‘[Geography].[Country].CURRENTMEMBER.MEMBER_CAPTION’ MEMBER [Measures].[ParameterValue] AS ‘[Geography].[Country].CURRENTMEMBER.UNIQUENAME’ MEMBER [Measures].[ParameterLevel] AS ‘[Geography].[Country].CURRENTMEMBER.LEVEL.ORDINAL’ SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Geography].[Country].ALLMEMBERS ON ROWS FROM [Adventure Works]
this dataset will just pull ALL countries from the database.
The third dataset, created to pull the values for the second filter (Product Categories) contains one parameter (Country):
WITH MEMBER [Measures].[ParameterCaption] AS ‘[Product].[Category].CURRENTMEMBER.MEMBER_CAPTION’ MEMBER [Measures].[ParameterValue] AS ‘[Product].[Category].CURRENTMEMBER.UNIQUENAME’ MEMBER [Measures].[ParameterLevel] AS ‘[Product].[Category].CURRENTMEMBER.LEVEL.ORDINAL’ SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Product].[Category].ALLMEMBERS ON ROWS FROM ( SELECT ( STRTOSET(@GeographyCountry, CONSTRAINED) ) ON COLUMNS FROM [Adventure Works])
it will pull only categories for the previously selected countries.
The forth dataset contains two parameters Country and Product Category which means that Annual Sales will be selected only for the Countries and Categories selected before:
WITH MEMBER [Measures].[ParameterCaption] AS ‘[Reseller].[Annual Sales].CURRENTMEMBER.MEMBER_CAPTION’ MEMBER [Measures].[ParameterValue] AS ‘[Reseller].[Annual Sales].CURRENTMEMBER.UNIQUENAME’ MEMBER [Measures].[ParameterLevel] AS ‘[Reseller].[Annual Sales].CURRENTMEMBER.LEVEL.ORDINAL’ SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Reseller].[Annual Sales].ALLMEMBERS ON ROWS FROM ( SELECT ( STRTOSET(@ProductCategory, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@GeographyCountry, CONSTRAINED) ) ON COLUMNS FROM [Adventure Works]))
For each created filter there is a report parameter which uses the created datasets to pull the lists of default values.
At run time it works this way: the report server generates dropdown boxes Countries, Categories, Annual Sales to allow users to filter the “main” dataset. After a user selects countries in the first filter, the Report Server updates Categories dropdown to show only Product Categories for the selected countries, Annual Sales are updated the same way after you select the Product Categories.
So far everything makes perfect sense, the problem will start if you try to change (or delete) any of the filters except the last one. Let’s say that in the first filter (Geography.Country) you decided to change the hierarchy from Country to City, so now the first filter will be Geography.City (instead of Geography.Country). When you change the hierarchy the “Parameters” checkbox will be unchecked, let’s check it again.
The new dataset (GeographyCity) is created by VS, but the old dataset (GegraphyCountry) is still there, though it definitely is not supposed to be there because nobody needs the list of countries any more, now we need only the list of Cities.
If you take a look at the first “main” dataset, you will notice that the parameter was changed correctly (from GeographyCountry to GeographyCity) but in the third and forth datasets, the dataset parameter is still GeographyCountry.
When you try to execute the report you will get one of the errors:
“An error occurred during local report processing. The definition of the report ‘…’.is invalid. The Field expression for the data set “….” refers to the field ‘ParameterLevel’. Report item expressions can only refer to fields current data set scope or, if inside an aggregate, the specified data set scope”.
“An error occurred during local report processing. The definition of the report ‘ ‘ is invalid. The value expression for the query parameter ‘ ‘ refers to a non-existing report parameter”.
The cause of the error is that the report is trying to execute the queries (created for the second and third filter) which are still using the old parameter (GegraphyCountry).
If you check the report parameters you will also notice that the new report parameter (GeographyCity) was created but the old parameter (GeographyCountry) is still there and it uses the old dataset (GeographyCountry) to retrieve the list of default values.
Just deleting the unused parameter and dataset is not going to fix the problem (see my blog “Report Server gotchas”).
The easiest solution in this case, most probably, would be:
· Delete all filters
· Delete all created datasets except the “main” one (VS won’t delete the created datasets for you after you delete the filters).
· Delete all report parameters (don’t mistake them with dataset parameters).
· Recreate all filters again.
Alternately you can change the dataset parameters in the generated queries manually, but then don’t forget to change the report parameters appropriately.
