Building a report based on an Analysis Services cube using Reporting Services 2014

No Comments

Written by Luis Miguel – Originally Published on Jan. 25, 2015

I built a cube based on the data in my fictitious auto sales data mart. The cube is structured as a star schema, with a fact table called fact_sales, and 3 dimensions, dim_buyer, dim_model, and dim_location. I want to build a report that shows the total sales by make per region. Make is an attribute of dim_model and Region is an attribute of dim_location. For this report I will not use any attributes of dim_buyer. If I needed to see sales data by Gender or Income Level, I would use those attributes of dim_buyer in my SSRS project.

The automobile sales cube

Step-by-Step Instructions

Open SQL Server Data Tools for Visual Studio 2013 and add a new project, then add a new report to the project.

A new Reporting Services project with a new report

Add a Microsoft SQL Server Analysis Services data source to the report, using the Edit button of the new data source wizard select the server and cube where the source data resides.

new data source

Add a Dataset to the report; it will be based on the Data Source you added in the previous step. Click Query Designer and drag the measure and dimension attributes into the large empty area at the bottom right of the dataset designer. In this case I’m using the measure “Fact Sales Count” and the Make and Region dimension attributes.

Setting up a new dataset

The new dataset query deisgner

Add a table to the body of the report

A new table

Drag the fields from the dataset into the Data area of the table

Adding fields from the dataset to the new table

Apply grouping by Make to the table

Add group - Parent group

Group by Make

When you apply grouping to a tablix column, a new (grouped) column is added but the existing column is not removed. The report now has two Make columns, one with grouping applied to it and another one without grouping. Remove the extra column left in the table without grouping.

Deleting the extra column left after the parent group was added

Format the column names, rename Fact Sales Count to Total Sales, make the font bold and centered

Improving the apperance of the report

Click the Preview tab to see how the report looks so far

Previewing the report

Sort the data by Total Sales so that the Regions are ordered by sales with the largest quantities on top

Open the Tablix properties

Add sorting by fact_sales_count

Set the Target Server URL to your project

From the menu select: Project - Properties

Deploy the project

From the menu select: Build - Deploy

View the report

Fireup a web browser and enjoy the report

Powered by Netfirms