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.
Open SQL Server Data Tools for Visual Studio 2013 and add a new project, then add a new report to the project.
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.
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.
Add a table to the body of the report
Drag the fields from the dataset into the Data area of the table
Apply grouping by Make to the table
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.
Format the column names, rename Fact Sales Count to Total Sales, make the font bold and centered
Click the Preview tab to see how the report looks so far
Sort the data by Total Sales so that the Regions are ordered by sales with the largest quantities on top
Set the Target Server URL to your project
Deploy the project
View the report