[Translation] SSRs compiling MDX reports with Parameters

Writing MDX reports has long been a pain for the report reporter. of course, if you use the query designer to drag a dataset directly, it is very convenient, but have you ever thought about how the query designer creates MDX. or how does the created parameter work? I have heard many report engineers who use analysis services as data sources (including me) say it is too difficult to write parameters, so they use the query designer to make reports. I think the key issue is that the MDX made by the query designer looks more complex than actually. if you know some MDX basics, you should be able to write MDX directly instead of using the query analyzer. It doesn't matter if you just like to use the query analyzer. the purpose of this article is to guide you to write basic MDX statements and parameterize them.

We will use two MDX functions:

Strtoset

Returns the set specified by the character expression (defined by msdn), which converts your write to the MDX set.

Strtomember

Returns the member specified by the character expression (defined by msdn). It converts the member you write to the MDX member.

We first install adventure works cube. If you do not have one, you can download it from www.codeplex.com. suppose you know some basic support for reporting services and not define each component of the tool that is not new for this example.

Create a new report server project and report, and use adventure works Cube as the data source. create a dataset named categorysales. in the query designer, click the design mode button and start writing the query. the following is an example of a query without parameters.

Select [Measures].[Internet Sales Amount] on Columns From [Adventure Works]

This query returns the total sales volume, but the report itself does not make any sense. Let's add the product catalog corresponding to the sales volume.

Select [Measures].[Internet Sales Amount] on Columns, [Product].[Category].Children on Rows From [Adventure Works]

This is not enough for our hypothetical user needs. Apart from viewing the sales volume of all products, they also want to get a parameter so that they can select the desired type from the drop-down box. to this end, we click the query parameter button to create a parameter.

• Variable name: productcategory
• Dimension and level: Product Dimension and category level
• Select the multi-value check box to accept multiple values.
• Finally, select a default value. In this example, we select bikes.

After clicking "OK", You need to modify the MDX to use the parameters you created. The modifications are as follows:

Select [Measures].[Internet Sales Amount] on Columns, StrToSet(@ProductCategory, Constrained) on Rows From [Adventure Works]

We use strtoset to convert the selected variable values. Yes, MDX can understand them. The constrained mark indicates that a member must be in the set.

Click "OK" and create it all the way... you will notice that the productcategory drop-down box has been automatically created.

Now let's further improve this example. let's add two more functions to create a date range. click the query parameter button again to add the startdate and enddate parameters, and then reference the date dimension and date attributes. set the default value as needed.

Add the WHERE clause in mdx to limit the time range. We use strtomember for conversion.

Select [Measures].[Internet Sales Amount] on Columns, StrToSet(@ProductCategory, Constrained) on Rows From [Adventure Works] Where StrToMember(@StartDate, Constrained) :StrToMember(@EndDate, Constrained)

The following is the preview result.

Original article connection http://www.bidn.com/blogs/DevinKnight/ssis/6252/writing-parametrized-mdx-for-reporting-services