Creating A Simple Report Using A Simple MDX Query

Please Note:

• See the post "Creating A Simple MDX Query" for the preliminary tasks required to set up the MDXProgrammingDemo and to connect to the DWSimpleSales cube that we will be using to create reports. 
• The following walk-through assumes the solution, project, database, data source, data source view, dimensions, and cube have been created, deployed, and processed successfully and is currently open in SQL Server Data Tools/Visual Studio.

 MDX Query

Since we are going to create our first report using an MDX query, let's start by viewing the data in the DWSimpleSales cube in SSAS' Cube Browser.  There we can auto-generate, tweak, and compile the MDX query from which we want to create the report.  This will give us the opportunity to learn yet another way to create a MDX query not previously discussed.   

To open the DWSimpleSales cube in Solution Explorer, either:

• Double click the cube;

or

• Right click the cube and select Browse in the context menu:

The DWSimpleSales cube will open to the Cube Structure tab.

• Expand the Measures and Dimensions to view the available cube data. 
• Then click on the Browser tab (last tab to the right in the Design tab pane) where we will begin creating our MDX query for our report:

Once you have clicked on the Browser tab, if the Metadata tab and pane do not show the DWSimpleSales cube's measures and dimensions, click on the Reconnect button:

Although the DWSimpleSales cube, as its name correctly implies, contains a very simple sample of sales, we can still create a meaningful report. 

Using the drag-and-drop technique in the Cube Browser, let's first create a MDX query that returns the amount of sales dollars and sales units for each product individually and collectively.

To compose the MDX query, decide what information is needed from the cube while keeping in mind the previous "MDX Basic Concepts" post.  

To move the needed items in the DWSimpleSales cube from the Metadata tab into the Browser pane, drag and drop the following:

1. Products hierarchy from DimProducts
2. SalesUnits measure from Fact Sales
3. SalesDollars measure from Fact Sales

Since we want results for all available transactions and dates in the cube, we don't need to include any items from the DimSales or DimSalesDates dimensions, which means we won't use the filtering WHERE clause in our MDX query.  

Check the result set to make sure it includes the data we want for our report:

The results include all data we want except for sales units and sales dollars for all products.   

The drag-and-drop technique does not permit dropping the All item under Members in the DimProducts dimension because we have already used the DimProducts hierarchy. 

(Please Note:

• We could add the missing data by creating a derived measure in the data source view that could then be added to the DWSimpleSales cube as a measure, or by creating a calculated member in the cube itself.  We would then need to test which produced correct results.  This would be an optimal process to choose and use if working in a production environment. 
• However, since we are in a development/learning environment and intend to experiment with tweaking auto-generated MDX code, we will make a manual adjustment in the code itself that will cause the MDX query to produce the missing data and show how to use a dimension twice in a MDX query to add more than one attribute from a single dimension.)

Behind the scenes, SSAS has been hard at work to help us begin compiling our MDX query. 

By clicking on the Design Mode button, you can see the MDX code automatically generated based on the dragged-and-dropped hierarchy and measures we selected:

Once the MDX code appears in the upper pane, click on the hyperlink in the lower drag-and-drop pane to execute the query to see the result set again:

Here is the auto-generated query up close:

SELECT NON EMPTY { [Measures].[SalesDollars], [Measures].[SalesUnits] } ON COLUMNS, NON EMPTY { ([DimProducts].[Product].[Product].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [DWSimpleSales] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

• Notice the query has all the basic and some familiar components of a MDX query, including the SELECT, ON COLUMNS, ON ROWS, FROM, NON EMPTY, and ALL MEMBERS keywords, functions, and clauses with measures and dimensions already enclosed in brackets and curly braces as required and/or good practice.  This proves that the drag-and-drop technique is not a lazy choice, but rather a smart shortcut that saves time and can prevent the dreaded syntax errors caused by spelling mistakes and/or missing brackets or curly braces. 
• The query also contains many other keywords and elements we have not used in MDX code exercises yet, but that still should be familiar and relatively self-explanatory if you have basic coding knowledge and training.  
• The query does need to be tweaked a bit, which we will discuss below, and we will switch over to SQL Server Management Studio (SSMS) to do that.

Using a New Query or MDX query window in SSMS, let’s clean up the query and test it before we move on to creating our report.

While still in the cube Browser, highlight all of the MDX code in the Design Mode window, right-click, and select Copy from the context menu:

Open SSMS and connect to Analysis Services:

            

• Click the New Query or MDX Query button to open a new MDX query window.
• Make sure the SalesCubes database is selected in the drop down menu in the toolbar so that the DWSimpleSales cube will appear on the new query tab with its Metadata available:

Right click in the new query window and select Paste from the context menu:

The MDX code is pasted into the new query window:

As is, the MDX code is not easily readable so add some hard returns to make the various query components more legible:

Since this is the only query in the query window, there is no need to highlight the query.  We can simply click the  ! Execute button in the toolbar, or press F5 on the keyboard, to execute and test the query:

The MDX query executes without errors and provides the correct results as expected, but contains quite a bit of standard Microsoft client application code not required to create SSRS or Excel reports.

As it is often said regarding code whether writing it, reading it, or executing it, "less is more" whenever possible.

Let's eliminate all of what could be called the "fluff stuff" and pare down our query to only the necessities by deleting the unnecessary auto-generated properties:

We also need to add our missing data—the sales units and sales dollars for all products. 

Although we can't include a dimension hierarchy twice in a MDX query, we can use more than one attribute from a dimension—here, that would the All Members level of DimProducts—if we enclose each reference to the DimProducts dimension (i.e., set) in parentheses and separate the sets with commas:

(Please Note: 

• Remember that this is not the best option for adding the all product data.  We just did this last step for all products to experiment with tweaking an auto-generated MDX query. 
• It is actually also being done here as a "deliberate" bad choice in data extraction to illustrate that even with the smallest amount of data, SSRS can sum and do other calculations for you.   
• A better way to add this total amount of product units and sales is by using a report feature specifically for adding totals to a report—we will cover that once we create our report.)

Execute this finalized and formatted query to see if we get the expected results, including the sales units and sales dollars for all products:

Our MDX query results check out fine, so it is time to start planning our report.

SSRS Report

To create a simple report with our tested MDX query, we have three tool options:

1. SQL Server Reporting Services
2. Report Builder 3.0
3. Excel

(Please Note: See Chapters 15 and 16 of the "Pro SQL Server 2012 BI Solutions" book listed in the "MDX Resources" post for an in depth description and discussion of the different features and intended users of these reporting tools to help decide whether one or more is right for your reporting needs.)

First, we will use our MDX query to create an SSRS report added to our MDXProgrammingDemo solution.

SELECT NON EMPTY

 { [Measures].[SalesDollars]

 , [Measures].[SalesUnits] } ON COLUMNS,

 NON EMPTY

 { ( [DimProducts].[Product].[Product].ALLMEMBERS )

 , ( [DimProducts].[Product].[All] ) } ON ROWS

FROM [DWSimpleSales]

If you have closed it while working in SSMS and you don't have a shortcut on your desktop or on your taskbar for the program, reopen SQL Server Data Tools/Visual Studio:

• Click on the Windows/Start button.
• Click on All Programs.
• Click on Microsoft SQL Server 2012.
• Click on SQL Server Data Tools (make sure to run the program as an administrator):

Open the MDX Programming Demo solution we have been using:

If it is not in the Recent Projects list on your Start Page when SQL Server Data Tools/Visual Studio opens…

Then click on the File menu from the toolbar.

• Select Open.
• Select Project/Solution.
• Find and select the MDXProgrammingDemo.sln file.
• Click the Open button.

Add a new project to the MDXProgrammingDemo solution:

Open the Solution Explorer if not already open.

• If Solution Explorer is not open, go to the View tab on the toolbar and select it.
• It may also be in Auto-Hide on the far right side of the window—if so, just hover over it and click on the push pin to dock it in the window.

• Right click on the solution name in Solution Explorer.
• Select Add in the context menu.
• Select New Project.

In the Add New Project dialog window:

• Expand the Business Intelligence Reporting Services option inside the Installed Templates in the left pane of the dialog window.
• Select Reporting Services once expanded.
• Select the Report Server Project option in the center pane of the dialog window.
• Name the project MDXQueryReport in the Name textbox.
• You should see the file path in the Location textbox—confirm the location.
• Click the OK button to add the new Report Server project to our current MDXProgrammingDemo solution:

You should now see the newly created MDXQueryReport project with its folders in Solution Explorer:

In order to retrieve the data for our report, we need to create a shared data source with a connection to the SalesCubes database:

• Right click the Shared Data Sources folder.
• Select Add New Data Source in the context menu.
• In the Shared Data Source Properties dialog window:
• Put DWSimpleSales in the Name textbox.
• Select "Microsoft SQL Server Analysis Services" in the Type dropdown box.
• Click the Edit button next to the Connection String textbox to configure the connection:

In the Connection Properties dialog window:

• The Data Source should be "Microsoft SQL Server Analysis Services".
• Put the name of your SQL Server in the Server name textbox, i.e. localhost, (local), . , or your SQL Server named instance.
• For the "Connect to a database" option, select the SalesCubes database from the "Select or enter a database name" drop-down box.
• Click the Test Connection button to verify you can connect to the SalesCubes database.
• Once you receive the Test Results "Test Connection Succeeded." confirmation message, close both dialog windows by clicking the OK buttons:

Now the Connection String is created.

Click the OK button to finish creating the shared data source:

You should now see the DWSimpleSales.rds shared data source we just created in Solution Explorer.

Notice that the file extension types are different for the SalesCubes project .ds and the MDXQueryReport project .rds:

Now we need to create our shared dataset:

• Right-click the Shared Datasets folder in Solution Explorer.
• Select Add New Dataset from the context menu.

In the Shared Dataset Properties dialog window:

In the Query section:

• Name the dataset SalesCubesProducts in the Name textbox.
• Select the DWSimpleSales.rds data source from the drop-down box.
• Cut and paste our MDX query in the Query textbox.

In the Fields section:

• Confirm that the three fields from our MDX query—Product, SalesDollars, and Sales Units—are listed.

Return to the Query section.

• Click the OK button to finish creating the shared dataset for our MDXQueryReport project:

You should now see the SalesCubesProducts.rsd shared dataset we just created in Solution Explorer.

Now with all the preliminary tasks complete, we can finally start creating our report. 

(Please Note:  Remember to regularly click the Save All button in the toolbar while creating your report. )

We will add a new report to the MDXQueryReport project and configure it to display our simple product sales data.

To add a new report to the project:

• Right-click on the Reports folder in Solution Explorer.
• Select New Item from the context menu.
• In the Add New Item dialog window:

In the Add New Item dialog window:

• Select Report in the center pane of the window.
• Name the report SimpleProductSales.rdl in the Name textbox.
• Click the Add button to add our new SimpleProductSales.rdl report to the MDXQueryReport project.

You should now see the SimpleProductSales.rdl report we just created in Solution Explorer.

The Report Designer window should open automatically after adding our report.

Before looking closer at the Report Designer window tools and options, first click on the View tab in the toolbar and make sure to display both the Report Data and Toolbox windows if they are not already accessible in the left pane:

In the Report Designer window:

• Take a look at the various tabs, panes, and items available to design our report.
• Located in the center pane, there are two tabs—Design and Preview—that you can toggle with a single click at the top of the pane.

 

• Located at the bottom of the left pane, there are two tabs—Report Data and Toolbox—which you can also toggle with a single click.
• In the Design tab window, just like we did when creating our MDX query, we will use the drag-and-drop method for adding Report Items from the Toolbox to create our report.
• You can Auto-Hide the Solution Explorer to give you more room to design and preview your report.

We now need to create a dataset specifically for our report.

In the Report Data window:

• Right-click the Datasets folder.
• Select Add Dataset from the context menu:

In the Dataset Properties window:

In the Query section:

• Name the dataset SalesCubesProducts in the Name textbox.
• Select and mark the "Use a shared dataset" radio button.
• Select SalesCubesProducts dataset, which is the only item in the list.

In the Fields section:

• Confirm that the three fields from our MDX query—Product, SalesDollars, and Sales Units—are listed.

Return to the Query section.

• Click the OK button to finish configuring the Dataset Properties for the SimpleProductSales report.

Since we have a small amount of data to include in our report, let's just use a simple table to our report.

• Switch from the Report Data window to the Toolbox window with available Report Items.
• Click the Table Item in the Toolbox.
• Drag it to the Report Designer surface on the Design tab:

In the new table:

• Drag your mouse and hover over the first column of the Data row.

The Fields List icon appears:

Click the Fields List icon to display the context menu that contains the Product, SalesDollars, and SalesUnits fields.

• Select the Product data field to add it to the table.      
• The Product header and the Product data row are automatically created:

In the Design pane, you won't see values yet—those will be visible in the Preview pane.

Let's first finish adding the rest of our data to the table before we move to the Preview pane.

Repeat the same steps that we took for adding the Product header and data row to add:

• SalesUnits data field to the second column of the data row.
• SalesDollars data field to the third column of the data row:

Now we can click the Preview tab to review our current report data values and design in the Preview pane:

The design is plain and simple, but most importantly the data values are accurate.

Now that we know our data values are correctly displaying, we can add more meaningful and user-friendly column names, a report title, and formatting to spruce up the look of our report.

• Switch from the Preview pane back to the Design pane by clicking the Design tab. 
• Change the names of the columns to Products, Units Sold, and Total Sales.
• Drag-and-drop a Text Box from the Toolbox on to the Design pane to add a title.
• Type "Simple Product Sales" in the text box and center it for our report title.
• Use the Report Formatting toolbar to format the table and textbox as you like:

Notice the ability to move the title Text box around in the Design pane and resize it.

• You have this same ability with the entire table.
• If you are formatting or editing items inside the table, you will see the gray squares across the top and down the left side.
• If you click on the gray square in the upper left hand corner of the table, this permits you to move the entire table to your chosen destination on the Design pane:       

• If you want to select more than one item in the table to apply the same format—for example, all the column headers—click on the first item, hold down the Ctrl button on the keyboard while clicking on the other items (you can also click on the gray square at the beginning of the row).   
• Once there is a single dark gray border around them, select your formatting option and it will be applied to all items selected:

You can also right-click on the title Text box and select Properties to get many more options, including but not limited to the Number section where you can format the SalesDollars column to Currency:

Click the Preview tab to review our updated report design:

Now we can see why adding the all products totals in the MDX query may not the best idea here. We can add the word "Totals" in the cell below Prod2 and our report will look just fine:

Instead, we could use a built-in SSRS feature in our report to do that.

• Switch back to the Design pane.
• Click on the gray square in front of the data row in our table.
• In the lower left section of the Design pane, click on the Details drop-down arrow.
• Select "Add Totals" from the context menu.
• Select "After" from the expanded context menu:

It will add the totals for us. Voila!

If you preview the report now, it will have incorrect sums because of the code we previously added in our MDX query.

So…lesson learned in this first attempt at creating a report with a MDX query.  Make sure to fine tune your MDX query as much as possible and let SSRS do the rest.

These are just the basics so feel free to return to the Design pane to try, and experiment with, the various formatting options to make the report design all your own.

Now you can save to PDF and/or deploy the report to the SSRS web service as well as try to export and create other report types in Excel.  

(Please Note: See Chapters 15 and 16 of the "Pro SQL Server 2012 BI Solutions" book listed in the "MDX Resources" post for an in depth description and discussion of how to create and utilize other SSRS, Report Builder, and Excel reports as well as how to accomplish these various reporting options. 

See also the "MDX Resources" post.)

Once you practice with this DWSimpleSales cube, you can venture on to bigger and better cubes with more data and thus more opportunities to spread your wings in reporting from SSAS cubes.