Developing a SSRS report using a SSAS Data Source

Problem

After designing several SSRS reports based on regular relational databases, your boss would now like several new reports to be designed and rolled out to production based on your organization's SSAS OLAP cube. How do you get started with designing a report based on a cube?

Solution

The creation of an OLAP SSAS report is similar to other SSRS report development once you get to the actual report layout. However, designing your data sources and datasets varies quite a bit from normal relational database data sources and datasets. 

The first step in creating a new OLAP based SSRS report is to create a New Project as noted in the below figure. Of course, you could also add a new data source, dataset, and report to an existing project; however in our example, we will utilize a new project with a new report.

First you will want to create a new Report. We are going to utilize data sources and datasets which reside within the report as opposed to utilizing shared datasets and data sources. To Start the New Report Wizard, Right click on Reports, and select Add New Report.

The New Report Wizard opens and the first step in the wizard allows for the creation of a data source. You will fill in the New Data Source Name and change the Type to Microsoft SQL Server Analysis Services. Next, select the Connection String Edit button. Fill in your Server Name and then select or type in the appropriate SSAS database name. Be sure to test the connection using the Test Connection.

Depending on the roles created for SSAS Cube you are connecting to, you may have to change the access details noted in the Credentials window. In our demo we will use integrated security, so on the Connection Properties dialog box, click OK.

After setting up your data source and clicking next, you will now create your dataset to be used in the report. This step requires planning and forethought as to what fields will be displayed on the report and what fields will be used to filter the data both when establishing the dataset and during report generation as parameters. These decisions will impact how the data is returned to the design grid and ultimately to the report itself. In order to define your dataset, click the Query Builder button.

The Query Builder button opens the Query Designer Window as displayed in the below figure.

The SSAS query designer has several options and methods that will ultimately affect your end report. Initially, you will notice that you have the ability to drag and drop both dimensions and measures onto the results grids. 

You will additionally notice how the results grid actually flattens your datasets. You can add entire dimensions in one motion by clicking and dragging the Dimension folder onto the results area. Also, the same process works for hierarchies, although you can also add individual attributes from the hierarchy if needed.

There are several buttons in tool bar of the Query Builder that should be noted. For full descriptions of the buttons, please see Analysis Services MDX Query Designer User Interface at http://msdn.microsoft.com/en-us/library/ms403829.aspx. 

The Show Empty Cells works this same as the MDX Non Empty clause; basically it shows / hides non empty cell values. The Auto Exec button turns on and off the auto execution of queries as you drag and drop dimensions, measures, hierarchies, and attributes onto the results pane. As the results get larger and more complex, it could take longer and longer for the query results to return. The Show Aggregates button again toggles the view of aggregates on and off. Last, the Add Calculated Members button allows for the addition of calculations at the query level. These calculated members could also potentially be added at the report level instead.

Furthermore, the query designer contains a filter grid in the upper half of the design area. This filter area serves several purposes:

1.  Acts as a method to filter the dataset at the query level.
2. Allows for the easy creation of filters which can also act as parameters. (see additional details about parameters below).

Filters can either be individual attributes or individual parts hierarchies.

The above example filters the dataset at the query level; basically, the query will be restricted to only those items who Calendar Year equals CY 2003. You will also notice that the parameter box is not checked in this first example. Last, since the Auto Exec button is toggled on, the query results will display Calendar Year 2003 data. 

At this point, we will complete the rest of the wizard setup to create a basic report. Later in our tip, we will discuss the parameter option. Going back to change this option later will also allow us to review making changes to the Data Set after completing the Wizard. Clicking Ok on the Query Builder screen returns you to the Report Wizard with the MDX query now being displayed in the Query String as noted in the next figure. Furthermore, notice that the Calendar Year filter is part of the MDX query text.

The report type is selected on the next screen; a matrix report will be used in this demo.

We will complete the report by placing Year and Quarter in the Column group, Country in the Row group, and Internet Group Profit and Internet Order Count in the Details area.

Next, accept the Matrix Style of Slate.

Now, finish out the report by completing the Report Name.

The initial SSAS Report design is now complete. A preview of the design and end report is displayed in the next two figures.

Of course to completely finish the report you may want to complete some formatting. As such, the numeric fields were immediately formatted to display commas and no decimals.

Our next step is to adjust the dataset filters and replace the hard coded filter for the Calendar Year to a parameter based filter where the report consumers can select which year to display. As noted in the next figure, expand the Datasets folder, and then right click on DataSet1, and select Query to display the Query designer..

Now, change the Filter Expression to include CY 2002, CY 2003, and CY 2004. Also, check the Parameters check box.

Adding these changes now creates a parameter for the Calendar Year which is displayed by expanding the Parameters folder, as shown in the following figure.

Checking the parameter box also adds a hidden dataset, that can be shown by right clicking Datasets and selecting Show Hidden Datasets.

After making this change to add the Calendar Year parameters and previewing the report, as shown in the next figure, you will notice two Alls, Select All and All Periods, appear in the drop down list. The first All is driven by the Report Server MultiParameter option while the second is driven from the MDX query that SSRS runs to populate the Parameters list.

To alleviate this situation, we have several options. Either we can adjust the MDX query or we can add a filter to the dataset to remove the All Member from the results. Using the first option, right click on the Date Calendar Year hidden dataset (after using the technique noted above to show the hidden data set), and then select Query. The first figure below displays the current query. Changing the highlighted word, ALLMEMBERS to CHILDREN adjusts the query to display only the Children of the Calendar Year list and not the All member option.

Alternately, we can filter the dataset using the filter dataset method; right click on the Date Calendar Year hidden dataset (after using the technique noted above to show the hidden data set), and then select Query Properties. The first figure below displays the current query with its related result set. Within the Dataset Properties Window, clicking on the Filter Option, allows us to filter on any of the fields being returned from the query. In this particular instance, I see that we can easily exclude the All Periods item, by using a filter of ParameterLevel > 0 which is displayed in the second figure below.

Now when we run the report, as displayed in the next figure, only the individual years are displayed which provides for a much cleaner option for the report consumers.

Conclusion-Creating a Cube Based Reporting Services Report

In this tutorial, we reviewed the process of creating a Report Services report based on a SSAS OLAP cube. We learned about the process of creating an initial report using the Create Report Wizard and identified the specific places where attention needs to be focused during the wizard steps. In particular we need to pay close attention to the fields we place on the results grid along with the attributes and dimensions used in the filter area. Furthermore we addressed adding a parameter to a report and specifically adjusting the parameter query or filter to properly display the parameter options.

Source collected from MSSQLTIPS.COM