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

Start at part 1

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

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!

This article is the first in a three-part series

On the Microsoft Developer Network (MSDN) forum for Reporting Services and other online discussion resources, I see many report developers asking how to build analytical reports using relational data sources. Some of the most fundamental capabilities often require complex and convoluted coding techniques. Sometimes these features require the report developer to populate temporary tables, inefficient and complicated subqueries and work-around techniques to derive calculated values and simple report features.

You probably know that OLAP cubes make this kind of reporting work much easier to manage and the data much easier to query and report on. To take that step – the transition from relational storage to SQL Server Analysis Services – also means taking a big step from the familiar T-SQL query language to the Multi-dimensional Expression query language. MDX is not a difficult language to use once you get the basic concepts down but it’s quite a bit different than SQL and it takes some re-learning to get to that point. For many, at first MDX seems strange and foreign until arriving at a moment of clarity when the mechanics of the language begin to make practical sense. Understanding concepts like “sets”, “tuples”, “members”, “hierarchies” and “levels” will help you appreciate the elegance of this simple language and the power of the OLAP query engine.

This article will show you many of the essential report and MDX query design techniques, beyond the fundamentals, related to OLAP reporting. I’m not going to teach you all about MDX in this short article so I expect that you already know some of the basics. However, I will show some examples that may help newcomers get started if you’re willing to venture out of your comfort zone. You’ll see how to use techniques in Reporting Services that will let you take advantage of the power of MDX and Analysis Services cubes. I assume that you’ve had some experience with, and understand the fundamentals of report design. I won’t demonstrate every step but I will provide enough direction to build a report if you already know the basics. You should know what to do when I provide directions like “create a new dataset with the provided query script” or “create a new parameter and use the dataset for the available values.” If you don’t have prior report design experience, there are several good books available on Reporting Services that teach essential report design. I encourage you to pick one up and learn report design basics before you read this article.

Just to make sure we’re on the same page; let’s briefly discuss the kinds of things that SSAS and MDX reports are typically used for. Analytical reporting is not about showing transactional details like you would on an invoice or ledger. It’s about grouping and aggregating data to analyze status, progress and trends so business leaders and analysts know what’s going on in their business at a relatively high level.

Consider the following common challenges and business scenarios:

• When analyzing sales progress over periods of time, business users want to see totals consistently for each period in the time series – even those that don’t have any data. For example, if there were no sales on a particular day, they still need to have a placeholder for that day to show that there were no sales.
• For a given period, business users need to see the aggregate sales value for that period alongside the aggregate value for a prior period for comparison. For example, a report should show the total sales for each day for the past 30 days; and for each of those days’ total sales, it should also show and compare the total sales for the same day of the previous year.

An OLAP database, consisting of cubes and dimensions, makes it much easier to efficiently resolve these challenges without the need for writing complex queries that scan and aggregate large volumes of transactions.

Figure 1

Using the Adventure Works Sample Data

To use these examples, you’ll need to have the Adventure Works Analysis Services database which you can find at codeplex.com; Microsoft’s community for sample projects and code sharing. Search for “Adventure Works SQL Server Sample Databases” and then download and install the correct version. I have tested these examples with the data for SQL Server 2008 and SQL Server 2008 R2. After installing the relational data warehouse database and the Analysis Services project, open the Enterprise Analysis services project in Business Intelligence Development Studio and then deploy and process the database on your server.

MDX Query Builder

When a dataset is created and SQL Server Analysis Services is selected as the data source, the query design experience changes. The Build Query button on the dataset dialog now opens the MDX query builder. The graphical query builder is a useful tool for designing basic queries by dragging-and-dropping objects into the grid and various landing zones. Beyond this, the graphical designer doesn’t provide the flexibility to make manual modifications to the query script. Using the graphical designer, you can easily build an entire query, complete with multiple hierarchies on the rows axis and multiple measures and complete parameter filtering logic. I often use the graphical query builder to prototype an MDX query and sometimes use the query as a starting point. For highly-parameterized queries, I usually start over and write the query from scratch as you will see in the following examples.

The query builder uses a layering pattern to build MDX queries using sub select statements for each filtering slicer or parameter. This technique is an effective way to handle different filtering logic but queries can easily become overly complex, difficult to decipher and maintain. We won’t be using the graphical query builder. Use the left-most toolbar button “Edit as text” to switch to generic text mode.

MDX Reporting Essentials

Reporting Services can be used quite effectively for reporting on SSAS cubes and in most cases reports may be designed in the same manner as relational queries. However, you should be aware of the following common considerations:

Reporting Services must flatten MDX results before the data can be consumed. As a rule, place all measures on the columns axis and the initial set of dimension members on the rows axis.

Include empty members

One essential characteristic of trend analysis is that all periods should be represented, even if no values exist. For example, all time periods should appear on a trend line chart even if no activity is reported for a day or other time period. Include all dimension members by using the NON EMPTY directive before dimension references on an axis (usually on the rows axis.)

Allow the cube to aggregate and roll-up measures

By default the report designer applies the SUM function to numeric fields referenced in a report group. Some measures defined in the cube may not additive. For example, a measure that performs an average, inventory count or other calculated value cannot be summed across levels of a dimension hierarchy. For these measures, use the AGGREGATE function in place of the SUM function for the report items used for group totals. Consult Books Online to understand the restrictions and properties used to control the behavior of the AGGREGATE function in Reporting Services.

next:  part 2 – Building a Report [step-by-step tutorial]…