SSRS Reports with MDX

In this article, let us start to create a Drill-down report, another common type of report, using SSRS 2012.

I will be using the same Parameterized report that I created in one of my earlier articles (Go to Creating a parameterized report in SSRS using mdx ). You can see that we have 4 columns in this report – Category, Subcategory, Product and Internet Sales Amount. When we execute the report in its current form we see all the column values by default.

But consider a scenario where the requirement is that only the Category values (with sub-totals at Category Level) should be visible when the report is initially rendered. If the user wanted to view the next level of details, like viewing the report at the Sub category or product level, then he should be able to do it easily.

The Drill-down report is what we need in this scenario.

Step 1: The first step is to add a Parent Group at the Category level. Right-click the details group (in the Row Groups section as shown in the screenshot) and select Add Group > Parent Group…

In the Tablix Group, select Group by: [Category] option.

Two things are added to the report after creating the group. Firstly, you can see an additional column also named Category (which is associated with the group we just had created). We will delete the original Category column later. The second change would be in Row Groups section. Now we can see a new group named Category above Details group indicating that the Category group is the parent group of Detail group.

Step 2: Now we need to add a second group, at the SubCategory level. Right click Details group again and select Add Group > Parent Group…

Select Group by: [Subcategory] and click OK.

Step 3: You can see the additional Subcategory column in the tablix and an additional Group in the Row groups section. Now we need to add the final group to the report. Right-click the Details group again and select Add Group > Parent Group…

Select Product in the Group by: drop down and click OK.

We can again see an additional column, Product, in the Tablix section and an additional group in the Row Groups section.

Step 4: In the tablix, select the three original columns – Category, Subcategory and Product. Right-click the header of the selected columns and select Delete Columns.

Step 5: Modify the expression of the Internet Sales Amount which has the measure value by right-clicking and selecting Expression (as shown in the below screenshot).

Replace the existing expression from =Fields!Internet_Sales_Amount.Value TO =SUM(Fields!Internet_Sales_Amount.Value)

Step 6: Now we need to add the Drill-down functionality and we do this in the groups. Since Category is the top level and we need to see this by default when the report is rendered, we do not need to make any changes to this group. However, some of the properties for the second group, Subcategory, need to be changed. Right-click the Subcategory group and select Group Properties.

Go to the Visibility tab and change the visibility option from Show to Hide. Tick mark the ‘Display can be toggled by this report item:’ check box and select the Category1 column from the drop down list and click OK.

Step 7: Similarly, right-click on the Product Group and select Group Properties.

In the Visibility tab, change the visibility setting from Show to Hide.
Tick mark the ‘Display can be toggled by this report item:’ check box and select the Subcategory1 column from the drop down list and click OK.

Step 8: Change the background of the three detail cells (not header cells) – Category, Subcategory and Product as shown in the below screenshot. Change the color to Grey (or any color you like using the Background color selector button.

Step 9: Also, for these three detail cells, change the BorderStyle property to None.

Step 10: Now let’s preview the report.

We can see that when the report is rendered, it is a summarized report, summarized at the category level.

If we want to view more details about the report, then we need to click the + button against the category and the report will expand to show the subcategory details.

Expand the Subcategory to view the report at the Product level.

Note: Since we had changed the expression for the Internet Sales Amount to SUM(Fields!Internet_Sales_Amount.Value), we are getting the correct aggregated values.

In the next report, we will see how we can create other types of report.

As you start working on SSRS reports, it is only imperative that you will eventually be asked to create a parameterized report at one point in time. This is because this is one of the most common type of reports as it gives the power of filtering the report based on end user preference.

For e.g., a business analyst might just want to focus on North America sales today but might want to view the sales of another region tomorrow. A static report without parameters and created just for North America sales would not work here and another report would be needed to be created to meet the Analyst’s requirements. On the other hand, creating a parameterized report will enable the Analyst to just select the region that he wants to focus on and view the report.

In this article, we will be looking into the detailed steps as to how to create a parameterized report.

Since we will be using Analysis Services as the source here, we will use MDX to query the cube. MDX is a powerful language and SSRS supports this wonderful language.

Step 1: Open BIDS (All Programs > Microsoft SQL Server 2012 > SQL Server Data Tools). In the start screen, click on New Project (or go to File > New > Project).Provide a name for the project and also browse to the location where we want the project to be created (tick the check box to create a directory for solution). Click OK.

Once the new project is created, in the Solution Explorer, we can see a Solution named SSRS_2012_Reports with three folders: Shared Data Sources, Shared Datasets and Reports.

Step 2: The first thing we need to do is to create a data source followed by a dataset. To create a Shared Data Source, right-click Shared Data Sources and select Add New Data Source.

The Shared Data Sources Properties window opens up. Provide a Name for the shared dataset. In the Type selection box, select Microsoft SQL Server Analysis Services (since we will be querying an SSAS Cube to build our report).

Then in the connection string, click the Edit button. The Connection Properties window box opens up. Provide the server name, in my case it is the local server and I just put a (dot) and then select the AdventureWorksDW2012Multidimensional-EE database (This is the SSAS database that contains the Adventure Works cube that we will be querying). Click OK to exit the Connection Properties window.

Click Ok again.

Step 3: Now that we have a Data Source, we can go ahead and start creating our report. (A dataset is still required which we would create a little later). Right-click on Reports folder in the Solution Explorer, Select Add option and New Item.

In the Add New Item window, select Report template, provide a name for the same and click on Add to create a new blank report.

Step 4: Now, we have created a blank report with no data source (the one we initially created was a Shared Data Source with Project scope). We still need to have a Data Source that the new report can have access to. So, let’s create a new Data Source specific to the report and point it to our Shared Data Source we initially created.

In the Report Data pane (if you are not able to locate the Report Data pane, press Ctrl + Alt + D to toggle the same), right-click on Data Sources folder and select Add Data Source… that will open up the Data Source Properties window.

In the Data Source Properties window, provide a Name for the Data Source and select the ‘Use shared data source reference’ radio button and select the shared data source that we had initially created.

Click OK to close the window and return to the report.

Step 5: The next step is to add a Dataset. Right-click on Datasets folder and select ‘Add Dataset…’ to open the Dataset properties window.

In the Dataset properties window, provide a name for the dataset and select the ‘Use a dataset embedded in my report.’ radio button. Then select the data source that we created in the previous step and click on Query Designer button.

This will open up the Query Designer window that allows us to browse the Adventure Works cube. This Query Designer supports both graphical interface (Design View) and code interface (Query View). While the Design View allows us to drag and drop the required measures and dimensions and set filters, the Query View allows us to manually modify the MDX query to perform advance operations.

But note that once we switch from Design View to Query View and make even a small change, we cannot switch back to Design view. So we need to use caution here. Eventually though, we would need to switch to Query View to take advantage of the full power of MDX.

Browse to Internet Sales Amount measure (under Internet Sales measure group) and drag it to the white space. We can also right-click a measure or dimension & select Add to Query.

Next, let us add some dimensions.

Browse to the Product dimension and in the Product.Product attribute hierarchy, drag the Product level to the query results window.

In the below screenshot, Product.Product is an attribute hierarchy and within this hierarchy, Product (highlighted in red) is a level.

Now, let us add some filters to our dataset. There are multiple ways to add a filter. We can either browse to the hierarchy, select it and click ‘Add to Filter’, drag the hierarchy to the filters pane or use the <Select dimension>’ drop down box to select the dimension and the Hierarchy.

Let us drag and drop the Category hierarchy to the filters tab. Select Equal in the Operator column and in the Filter Expression column, select ‘All products’. Similarly, drag the Product.Subcategory, Product.Product and Date.Calendar hierarchies to the filter pane and set it to the values mentioned in the screenshot.

Note: Since we need to provide a way for users to modify the filter expression such as category, sub-category or product, we will make them as parameter by marking the Parameters check box.

Once done, click the OK button to go back to Dataset Properties.

Now we can see the MDX code behind our drag and drop actions and filters in the Query section of the Dataset Properties window. Click Ok to exit the Data Properties window.

Since we have opted to parameterize the filters, parameters and datasets are automatically created in the background (the parameter datasets cannot be initially seen under datasets).

To see these hidden parameter datasets, we need to right-click Datasets folder and select Show Hidden Datasets.

17 Parameterized Report - Hidden Datasets

18 Parameterized Report - Parameters

Step 6: Now that we have the dataset, we can go ahead and add report items to the report body to display the report. In the report body, right-click and select Insert > Table.

Align the table at the top of the report and assign the columns to the dataset fields as shown below.

When we select a cell in the detail row of the table, we see a small blue and white icon. When we click this icon, a drop down list of all the dataset fields appears. We can select the required dataset field to assign for the column.

Step 7: We can apply formatting for the various report items, like background colors for the headers, font type and size, and for numeric and currency fields, we can assign the respective formatting so that they appear correctly in the final report.

We also have some pre-defined currency and number formats. E.g., For a Currency field, we can go to the Format property for the cell and set it to either C0, C1 or C2. Similarly for Number fields, we can set it to N0, N1 or N2.

21 Parameterized Report - Apply Formatting

Now, the report is ready to be previewed. When we click Preview, we (hopefully 🙂 ) see a beautifully designed report.

But, wait a minute. The report is not yet final!

If we observe closely, we can see that the Prompts for parameters are not so friendly.
E.g., we see labels like Product.Product for the Product slection box and Date.Calendar for the date parameter. We need to change them.

Step 8: Go back to the Design mode and in the Report Data toolbar, under parameters, open each parameter and modify the Prompt: to a more user friendly prompt.

Click Preview again. Now the report looks good. But is it perfect?

Just click on the Calendar Year prompt and see how the parameter is being populated.

Does not look good. Does it? Let’s fix it.

Step 9: Fixing the Date parameter to accept only years:

Go back to the design view. In the Report data, under parameters, copy the Query from any other existing parameter: E.g., from ProductCategory parameter

Step 10: Create a new dataset as below.

In the Dataset Properties, provide a Name for the dataset and select the ‘Use a dataset embedded in my report’ option. Select the Data Source from the drop down box and click on Query Designer.

Paste the copied query into the new dataset and modify the query by replacing the hierarchies/ levels with the required ones as below.

OLD Query: The below is the old query from the ParoductCategory parameter. We need to change the text highlighted in Red.

WITH 
MEMBER [Measures].[ParameterCaption] AS [Product].[Category].CURRENTMEMBER.MEMBER_CAPTION 
MEMBER [Measures].[ParameterValue] AS [Product].[Category].CURRENTMEMBER.UNIQUENAME 
MEMBER [Measures].[ParameterLevel] AS [Product].[Category].CURRENTMEMBER.LEVEL.ORDINAL 
SELECT {
[Measures].[ParameterCaption], 
[Measures].[ParameterValue], 
[Measures].[ParameterLevel]} ON COLUMNS , 
[Product].[Category].ALLMEMBERS ON ROWS 
FROM [Adventure Works]

Modified Query: The below is the modified query.

WITH 
MEMBER [Measures].[ParameterCaption] AS [Date].[Calendar Year].CURRENTMEMBER.MEMBER_CAPTION 
MEMBER [Measures].[ParameterValue] AS [Date].[Calendar Year].CURRENTMEMBER.UNIQUENAME 
MEMBER [Measures].[ParameterLevel] AS [Date].[Calendar Year].CURRENTMEMBER.LEVEL.ORDINAL 
SELECT {
[Measures].[ParameterCaption], 
[Measures].[ParameterValue], 
[Measures].[ParameterLevel]} ON COLUMNS , 
[Date].[Calendar Year].ALLMEMBERS ON ROWS 
FROM [Adventure Works]

Click the exclamation mark ! to test the query results. You should see similar results for ParameterValue containing Years.

Click Ok to come out of the Query Designer.

Click OK and the new dataset is created.

Step 11: Now that we have our dataset ready, it is time that we make use of it.
Open the existing DateCalendar parameter.

In the General tab, change the Name to Year and Prompt to Select Year.

Next, go to the Available Values tab.

Change the Dataset name to LoadYear from DateCalendar.

Next go to the Default values tab.

Change the option from Specify Values to No Default value as below. Click OK.

Step 12: Now that we have created a new dataset and mapped that to the parameter, we can delete the Original, now unused DateCalendar dataset.

Now when you click preview, you can see that the correct year values appear in the drop down and the report executes properly 🙂

—

About Microsoft Business Intelligence

Everything About MSBI…

Creating a Drill-down report in SSRS

Creating a Parameterized Report in SSRS using MDX