…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!