Designing Reports with Custom MDX Queries – part 2

…continued from part 1:

Building a Report

For the remainder of this article, I will demonstrate query and report design techniques by progressively building a report, adding steps and features. The examples will focus on the capabilities of the query and results, rather than the visual design of the report. This report could easily be enhanced with groups, sparklines, gauges a geographic map or other visuals. I trust that you can find the most effectively way to visualize the results on your own. We’ll start with a simple query and then layer additional features.

1. Start by creating a new report in either a Business Intelligence Development Studio (BIDS) report project or the newest version of Report Builder. Report Builder 2.0 can be used for SSRS 2008 and Report Builder 3.0 or newer may be used for newer product versions and is backward compatible with SSRS 2008.

2. Add a data source that uses SQL Server Analysis Services and connect to a database server containing the Adventure Works sample SSAS database.

3. Add a new embedded dataset to the report and open the Query Builder.

4. Click the left-most toolbar button, labeled “Edit as Text”. This will allow you to type the query rather than using the graphical query builder.

5. Enter the following query script and then execute the query using the ! button on the toolbar.

select {[Measures].[Internet Sales Amount]} on columns

, NON EMPTY [Date].[Date].members on rows

from [Adventure Works]

;

Figure 2 shows the Query Designer with the MDX query script and execution results.

Figure 2

To get more business value from the query, we’ll design it to show a specified range of dates. This is done by adding the LASTPERIODS function to return 30 days of results, up to and including a specified date.

6. Replace the expression “[Date].[Date].members” with the function name and a set of parentheses. The LASTPERIODS function takes two arguments separated by a comma; the number of periods and a member reference. The easiest way to add this date member reference is to use the metadata pane on the left.

7. Expand the “Date” dimension and then the “Date” attribute hierarchy.

8. Expand “Members” and then find “January 31, 2003”.

9. Drag and drop this member to the right of the comma so it looks like this query script:

select {[Measures].[Internet Sales Amount]} on columns

, NON EMPTY LASTPERIODS( 30, [Date].[Date].&[20030131] ) on rows

from [Adventure Works]

;

Execute the query to check the results. The results grid should include 30 dates ending with January 31, like you see in Figure 3.

Figure 3

Adding a Calculated Member

Next, we’ll add a calculated member that will return the Internet Sales Amount for a period twelve months prior to the current date period. This value will be returned as a member named “TrendValue”. In the following example, the “with” clause defines this member. The earlier date is returned by the ParallelPeriod function which takes as arguments a hierarchy level, the number of prior periods (12 months), and a reference to the current member of the Calendar hierarchy. This expression will be processed for every Date member on the rows axis, to return a corresponding trend value.

10.Add the “with member” expression shown in the following example to apply the ParallelPeriod function as described.

11.Modify the “on columns” expression to return the TrendValue measure as shown.

12.Compare your query script to the following example and use the Execute (!) button on the toolbar to test the results.

with

member Measures.TrendValue as ([Measures].[Internet Sales Amount]

, ParallelPeriod([Date].[Calendar].[Month], 12

, [Date].[Calendar].CurrentMember))

select

{[Measures].[Internet Sales Amount], Measures.TrendValue} on columns

, NON EMPTY LASTPERIODS(-30, [Date].[Calendar].[Date].&[20030131]) on rows

from

[Adventure Works]

;

Take a look at the results returned in the Query Designer. The first row shows that the Internet Sales Amount for the 31st of January, 2003 was about $17,468.54. The TrendValue is the Internet Sales Amount for the 31st of January, 2002; which was about $21,762.16.

Figure 4

The next step will make only a slight modification but will enable the query to be more flexible when we start using parameters in the next section. In the previous examples, the Internet Sales Amount was hard-coded as the first measure. In this version, we will pass the measure name as a string and return the value as a member called “Selected Measure”. Later, we’ll pass the measure name using a parameter so the report will analyze any measure the user chooses.

13.Modify the query script, adding another “member” clause for the Selected Measure, as shown in the example.

14.Execute the query and check the results.

with

member Measures.[Selected Measure]

as [Measures].[Internet Sales Amount]

member Measures.TrendValue as ([Measures].[Internet Sales Amount]

, ParallelPeriod([Date].[Calendar].[Month], 12

, [Date].[Calendar].CurrentMember))

Select

{[Measures].[Selected Measure], Measures.TrendValue} on columns

, NON EMPTY LASTPERIODS(-30, [Date].[Calendar].[Date].&[20030131])

on rows

from

[Adventure Works]

;

The results in Figure 5 show that the column name has changed.

Figure 5

At this point, the report designer has enough information to generate the field metadata and in subsequent query changes this metadata will remain unchanged. This is an important thing to understand because when the complexity of a query reaches a certain point, it can be more difficult to go back and make changes to things like field names. For this reason, it’s important to establish this baseline and lock-down the fields returned by a dataset. It’s also a good idea to save a copy of this query in a separate script file for reasons that will soon be more obvious.

Before moving on to the next step in the query design, I’d like to build a simple report interface. Add a line chart to the report body. Add both of the measures as chart series values. Setup the chart to be grouped by the Date field as a category group. In a line chart the category represents the horizontal axis and values are plotted along the vertical axis scale.

15.Add a line chart to your report

16.Drag the two measures to the Values list in the Chart Data pane

17.Drag the Date field to the Category Groups list

Your chart report should resemble Figure 6.

Figure 6

Here’s a common MDX report flaw that is often overlooked in design and often gets past unit testing. Most dimension members return string values. This isn’t always the case but it’s more common than in relational solutions. In our case, the Date attribute returns string values and not actual date values, such as “January 31, 2003” and “February 1, 2003”. These members are already sorted in the right order in the query result set but if the category group were sorted on these field values “February 1…” would appear before “January 31…”. By default, the report designer adds a sort expression when a group is created. As a rule, you should always check the groups for any data region and remove the sort expressions when designing reports with MDX datasets.

18.Preview the report and take note of the date order on the horizontal axis. We expect the dates to be in the wrong order.

19.Correct the automatic sorting issue by modify the category Date group

20.Delete the sort expression for the group.

21.Preview the report again and verify that the dates are sorted correctly.

You can adjust the design to your liking. I’ve modified the horizontal axis properties and set the Interval property to 1. This will show every date on the axis. Since there are so many, the text will rotate 90 degrees to make room for all the date members. I’ve also remove the axis labels, set the chart title and formatted the vertical axis scale to clean things up. The report looks like this in preview:

Figure 7

next: part 3 – Handling Parameters [step-by-step tutorial]…