…continued from part 2:
Handling Parameters
When an MDX query is created using the graphic query designer, parameters can be added automatically along with the datasets needed to drive the parameter list values. In our case, when writing the query manually, it’s necessary to do this work yourself. One convenient option is to use the graphical query designer to build the field metadata and the parameter lists and then modify the query script using manual techniques. This will work as long as the field order, names and hierarchies don’t get changed.
In our case, we will add and setup the parameters manually. Let’s go back to the last version of the query and look for opportunities to extend the capability of the query and report with some parameters. The query has three values hard-coded into the logic. We can make this query much more flexible by making the following changes:
- Alias the Internet Sales Amount as an aliased member named Selected Measure. The ability to change out the measure at will by using this placeholder provides a great deal of flexibility. This means that one query could be used to measure practically anything in the database.
- On the Rows axis, the first date for the range is hard-coded. It would be preferable to allow a report user to pass in any date of their choosing and then to decide how many dates will follow the first date member in the specified range.
Let’s implement these changes. Start by adding the following three parameters to the report. Just use the default for any other property values.
1. Add the following parameters to the report. In the Report Data pane, right-click the Parameters node and choose the menu option to add a parameter.
2. Enter the appropriate name and properties for each new parameter and then close and save the each parameter with the properties shown in the table.
You’ll open and close the Parameter Properties dialog for each of these parameters:
Parameter Name | Prompt | Data Type | Default Value | |
MeasureName | Measure Name | Text | Internet Sales Amount | |
LagMonths | Lag Months | Integer | 12 | |
FirstDate | First Date | Date/Time | 1/31/2003 | |
My example for the FirstDate parameter default value uses a US formatted date. If your machine is configured for a different locale, use a date format appropriate for your region.
Passing Parameters
Since we’re building the dataset query manually, the parameters will be referenced right in the query script and there is no need to map report parameters to query parameters like you normally would if you were using a common T-SQL or MDX query. There are a few different approaches used to customize a query using parameters. Under some conditions you can simply embed a query parameter reference, prefixed with the ‘@’ symbol, into the query script but the query parser is a bit restrictive about the components of a query that can be parameterized. If your mission is to use parameters more creatively than these restrictions will allow, you can use an expression to convert the entire query into a string value. In exchange for this effort, you will have a query that can be used to control aspects of the report that would otherwise require starting over with a different report design. The trick with the technique is to write a query first to return all of the members and measures. Executing this query builds the report’s field metadata. After that, you can use code and expressions to customize the query as long as it continues to return results consistent with the metadata schema (in other words, all of the fields are returned using the same field structure.)
Building a Dynamic Query
To serve our purposes, the entire query will be converted to a string using a Visual Basic.NET expression.
3. In the Report Data pane, double-click the name of the dataset.
4. In the Dataset Properties dialog, click the Expression Builder button next to the Query box.
The Expression Builder button has an “fx” on it, which stands for “Expression Builder”. No, really, I think it actually stands for “Functions and Expressions” or something like that. Anyway, click that button.
5. Modify the query expression so it becomes a series of concatenated strings. You can use the ampersand (&) to combine each literal string or expression.
Every element of the query is a literal string except for the parameter references. In the following example I’ve shortened some of the strings into separate lines so they fit on the printed page. You’re welcome to simply this if you chose to but you can copy it verbatim as a working starting point.
=”with member Measures.[Selected Measure] as ”
& “[Measures].[Internet Sales Amount]”
& “member Measures.TrendValue as ([Measures].[”
& Parameters!MeasureName.Value
& “] , ParallelPeriod([Date].[Calendar].[Month], ”
& Parameters!LagMonths.Value & “, [Date].[Calendar].CurrentMember)) ”
& “select ”
& ” {[Measures].[Selected Measure], Measures.TrendValue} on columns ”
& ” , NON EMPTY LASTPERIODS(-30, [Date].[Calendar].[Date].&[”
& Format(Parameters!FirstDate.Value, “yyyyMMdd”) & “] ) on rows ”
& “from ”
& ” [Adventure Works];”
Handling Date Parameters
Working with date parameters and dimensions can be a bit tricky. To be successful, it’s important to understand some of the common unique differences between working with date values in SQL and the way that date values are represented in an OLAP dimension. The fact is that there is no hard-and-fast standard for all date attributes but there are some common approaches used by most experienced SSAS practitioners. In the Adventure Works sample cube, the Date attribute’s key value is an integer converted to a string using the format: yyyyMMdd. For example, the 9th of January, 2011 would be 20110109. Since the FirstDate parameter is a trued Date/Time data type, it would be converted to the intended format using the Format function using the appropriate format mask.
Parameterizing Measures
We have one more enhancement to make and then we’ll be done. Since we’re already focusing on the query design, let’s make this change and wrap it up.
6. Replace the literal measure name in the Selected Measure calculated member expression with a reference to the MeasureName parameter, as you see in the following example:
=”with member Measures.[Selected Measure] as [Measures].[”
& Parameters!MeasureName.Value & “] ”
& “member Measures.TrendValue as ([Measures].[”
& Parameters!MeasureName.Value
& “] , ParallelPeriod([Date].[Calendar].[Month], ”
& Parameters!LagMonths.Value & “, [Date].[Calendar].CurrentMember)) ”
& “select ”
& ” {[Measures].[Selected Measure], Measures.TrendValue} on columns ”
& ” , NON EMPTY LASTPERIODS(-30, [Date].[Calendar].[Date].&[”
& Format(Parameters!FirstDate.Value, “yyyyMMdd”) & “] ) on rows ”
& “from ”
& ” [Adventure Works];”
7. Close and save the expression and the Dataset Properties dialog.
Returning metadata with a Dynamic Management View
This is one of those little known secrets that, with some creativity, can be used to take your solutions to a completely new level. Data Management Views are used to return sets of metadata or information about database objects such as object names, attribute values and member sets. In addition to the actual data stored in the cube, DMV queries can return information about just about any system information and useful metadata. This example will only touch the tip of the iceberg.
Data Management View queries are parsed by the Analysis services data provider but the query execution is handled by a different set of objects than the OLAP storage engine. As such, the syntax is standard MDX. It’s a blend of SQL but it’s not really pure MDX and the rules are a little different.
8. Create a new dataset using the same data source. Let’s name it MeasureList. Since the DMV query isn’t MDX, you must use the Expression Builder, rather that the query builder, to enter the query script.
9. In the Dataset dialog, click the Expression Builder (fx) button and enter the following text:
Select MEASURE_NAME from $System.MDSCHEMA_MEASURES WHERE CUBE_NAME = ‘Adventure Works’
ORDER BY MEASURE_NAME
;
10.Close and save the Expression Builder and then click the Refresh Fields button.
11.Click the OK button to close the Dataset Properties dialog.
Figure 8
12.Edit the MeasureName parameter and set it to use the new dataset for the Available Values and then set the Default Value to Internet Sales Amount.
13.Close and save the parameter properties.
We should be ready to rock and roll so let’s make sure everything works now. Preview the report and it should look similar to my example.
Figure 9
The styling of your report may be a little different than mine. I’ve dressed up this report just a little by applying formatting to the vertical axis label and changed the series line colors and borders. This is typically the point where I’ll go through the design and make adjustments to make everything look good and consistent with my client’s company themes and standards.
Summary
I’ve shown you several techniques that should get you started building more dynamic and flexible report designs that use MDX to consume data from Analysis Services cubes databases and cubes. By parameterizing things like the measure and field names, function arguments and key property values, you can add a tremendous amount of flexibility to your reports. With just a little guidance, your business users can use a dynamic report to gain valuable insights and get answers to many important business questions without having to design additional reports.
Check out www.reportsurfer.com to see live examples of several unique report designs. As I said, this was just a starting point but you now have the tools to apply these techniques to your own unique problems and build flexible reports to meet your own business needs. Happy Reporting!
Thanks. Your post is still bringing relief to those in need!
Thank u so much…….. i gained much knowledge for my interviews,
plzzz update few more reports using different functions 🙂
Greate post. I learned a lot. Thank you very much.
The only problem I found in the MDX query is that, since you set LagMonths as integer, you have to use str(LagMonths) in the fx to add it as a string.
In addition, ‘+’ works for me insead of ‘&’. It took me a while to figure this out. The dynamic MDX query for me is:
=”with member Measures.[Selected Measure] as [Measures].[”
+ Parameters!MeasureName.Value & “] “
+ “member Measures.TrendValue as ([Measures].[”
+ Parameters!MeasureName.Value
+ “] , ParallelPeriod([Date].[Calendar].[Month], “
+ str(Parameters!LagMonths.Value) + “, [Date].[Calendar].CurrentMember)) “
+ “select “
+ ” {[Measures].[Selected Measure], Measures.TrendValue} on columns “
+ ” , NON EMPTY LASTPERIODS(-30, [Date].[Calendar].[Date].+[”
+ Format(Parameters!FirstDate.Value, “yyyyMMdd”) + “] ) on rows “
+ “from “
+ ” [Adventure Works];”
Thank you for the suggestion, Jenny. Generally, it is a good idea to explicitly convert non-string types to strings when doing concatenation. You can use a few methods and functions to do this including STR(), CSTR() and .ToString(). I think most .NET purists would argue in favor of the ToString() method.
It seems like you truly understand plenty related to this particular subject
and it all exhibits throughout this excellent blog, called Windows
Blinds “Designing Reports with Custom MDX Queries
– part 3 | Paul Turley’s SQL Server BI Blog”. Thanks a lot -Fawn
Hi Paul
I use Dynamic MDX query to make a flexible environment for end user, like steps you define in this step by step tutorial
I have a new requirement that want to show dynamic measure in one plot, for example I want to show Tax , sell , and gain amount measure in date dimension. if it was static in number I could define a data-set with the require member name and then assign a parameter to each fields.
but in my case it is dynamic in count, so I look for a solution that merge this measures into one measure and make a new dimension that show main measure name of each cell.
in this thread :http://social.msdn.microsoft.com/forums/en-us/sqlreportingservices/thread/6213030c-acfe-4520-b2ad-f544496a4486
Raymond Lee at his last post say that :
“Since you are using cube, you can try to delete the two measures, and create calculated measure to combine the two measure values with prefixed name -type (‘DocEntry’, ‘Quantity’) . After that:
Category: group on cast the prefixed name
Series: group on year
Data: group on cast the value from the calculated measure.”
actually I couldn’t understand Raymond’s solution and ask you to make it clear for me.
any help will appreciated.
regards
Hi Paul,
Great post over all. I have been trying to accomplish report parameter to query parameter mapping all day and I have not had any success. I was wondering if you could shed some light or give me some pointers?
Let the query designer do as much of the work as possible. There is a bit of a trade-off here (but both options are good news.) If you build the query using the graphical designer, it will generate the parameters with the necessary lookup datasets. The first trick is to use this to create all the necessary metadata. Then you can switch to text mode, copy and paste the query into SSMS and manipulate it.
For more advanced designs, don’t bother with query parameters. Write and execute a simple MDX query that returns the members you need in the report (which it translates to dataset fields). Take that command text into the expression window and use the techniques described in this post to build a parameterized expression, referencing report parameters, not query parameters. Once I go that route, I normally don’t try to add my own query parameters. If you really need to, you can do that by copying the XML definition of an existing query parameter and using it as a template for the new one.
Hi Paul
I have a question about dataset fields for dynamic mdx queries.
When the fields for an MDX query cannot be autogenerated, what’s the best way to include them in the dataset? I’m working with queries that will not run in design mode, and manually adding fields to a dataset for a dynamic MDX query seems tricky, especially when it’s not clear from the MDX query what the returned field name will be.
Andrew, I’ve been where you are and I know what you’re asking but you shouldn’t have to do that.
Write a simple verison of the query that returns all the necessary fields. Keep two copies on hand – the simple verison without parameters and the complex verison with parameters. If you need to add a member to the query (e.g. field to the report), modify the query and execute it to let the report designer add the field.
Paul,
This really helped me out. Thanks for posting.
James Z. (frequent O -SQL-Dev attendee)
Hi Paul,
This is LVPRASAD , Now i am working with SSIS,SSRS ,
i have one doubt in SSRS i.e
How to create Calulate member for the change in two consecutive months sales amount ?
Can you please give me the suggestions for how to write MDX query for that
Thanks & Regards,
Vara Prasad
Are you looking for a member that returns the previous month’s sales amount? … or the difference between the past two months’ sales. Here’s a short sample. If I’m not on track, please provide a sample of the output you’re expecting.
with
member Measures.[Prev Month Sales] as
([Measures].[Reseller Sales Amount], [Date].[Month of Year].CurrentMember.PrevMember)
member Measures.[Prev Month Sales Diff] as
[Measures].[Reseller Sales Amount] –
([Measures].[Reseller Sales Amount], [Date].[Month of Year].CurrentMember.PrevMember)
select
{[Measures].[Reseller Sales Amount], Measures.[Prev Month Sales], Measures.[Prev Month Sales Diff]} on columns
, LASTPERIODS( 6, [Date].[Month of Year].&[12] ) on rows
from
[Adventure Works];
Outstanding series of posts Paul, great job as always:) Congrats on the change of jobs as well and best of luck!
Thanks. I’m pretty happy with the move so far. SolidQ is a great company.