Introduction
In this Article I am going to teach you the step by step method to create SSRS Report on SSAS OLAP Cube. If you have prior knowledge on SSRS it is good otherwise not to worry, just follow the steps and you will have your first Report ready on OLAP Cube.
SQL Server Reporting Services (SSRS) Comes within SQL Server Setup,You just need to select option for this while doing setup of SQL Server. SSRS can be used to Create, Schedule, Publish and manage your reports centrally created on various data sources. You can use Microsoft BIDS or SQL Server Data Tool or Report Builder for the development of your reports.
Lets have a Quick Introduction on OLTP data source & OLAP data source
- OLTP data model is specifically designed to support your transactional data from your live systems, so Data Manuplation (DML) operations will have their optimal performance while doing Insert, Update, or Delete.
- OLAP Solution were designed by keeping in mind Reporting Requirement of the client. This system is capable of holding very huge amount of records. Query will execute quickly against billions of records hold by this type of OLAP source.
I hope now your idea is clear on above two different type of source system, you can find further detail, differences on this two systems in my previously published Articles or by searching on Google !!
If you are newbie You can learn about creation of Data warehouse , OLAP Cube using my previous articles it will ease your journey towards learning of Business Intelligence, If you already gone through my previous articles then after learning Report Development using this article you will be capable to give End to End solution to your client.
Now Let us focus on Designing of SSRS Report.
Prerequisites
- Install SQL Server 2008, 2012 ( Standard, BI or Enterprise Edition) along with options SQL Server Analysis Services (SSAS), SQL Server Reporting Services (SSRS), and Business Intelligence Development Studio (BIDS).
- You can also use SQL Server Data Tools (SSDT) for Development if you do not have BIDS environment installed on your machine.
It is free to download !! Use given link to Download SSDT (SQL Server Data Tool).
Background
Before starting designing of the SSRS report we need to do some basic setup & configuration for creation of data warehouse and OLAP cube in our system if we do not have any.
1. Download and Execute Sample Data warehouse script in your SQL Server Instance and refer the given article for more detail on creation of Sales Data warehouse.
2. Take a Look to this Article if you do not have any idea on creation of OLAP Solution and its deployment, Just follow the steps and you will have your OLAP cube ready soon with the data.
Once you have your OLAP Cube deployed and ready for query we can start designing our SSRS report. As we all know SQL is a query language to query OLTP source system similarly MDX Query language is used for querying records from OLAP Cube as per our desire.
3. If you do not know MDX !! do not worry, here i have article for you which will help you to learn MDX Query language quickly and easily.
Note : If you are using my Data warehouse script and OLAP cube creation method so i advise you to use same naming conventions used for them in the article or pick them from Images for ease of yours. Only use your machine credentials for login instead of mine wherever required, e.g. SQL Login, SSAS Login, SSRS Report Manager Login etc.
Now we are ready to start designing our first SSRS report with OLAP Cube.
Create SSRS Report with OLAP Source In Few Steps
Here I have used SQL Server Data Tool 2012 (SSDT) for designing of SSRS Report.
You can Just follow these 12 easy steps in any environment (SSDT 2010 or 2012 or BIDS ) for creation of your first report server project.
Ex. Problem Statement
Create SSRS Report to view performance of Product Sold over the specified date range.
Step 1 : Create a SSRS Project in SQL Server Data Tool 2012 (SSDT).
- From the Start Menu -> Select All Programs -> Microsoft SQL Server 2012 (or 2008) ->Click on -> SQL Server Data Tools for Visual Studio 2012.
- File -> New -> Project
- Select "Report Server Project" from the list -> Enter a project Name and specify project Location-> click OK.
Step 2 : Create Shared Data Source
Specify OLAP Data source Connection String and Credentials to which you want to use as your source system.
If Solution Explorer is not Visible then
- In Menu bar ->View > Solution Explorer.
- In Solution Explorer-> Right Click -> Shared Data Sources -> Click on Add New Data Source.
- Select Type Of Source ->Ms. Sql Server Analysis Services->Assign Connection Name -> Click on Edit.
- Assign Analysis Services Instance name in Server Name ->Choose Your Analysis services Database Name ->Test Connection for success -> Click OK.
- Click OK to Complete Creation of Shared Connection.
Step 3 : Create Shared Datasets
- In Solution Explorer -> Right Click on -> Shared Datasets -> Click On -> Add New Dataset.
- Enter Dataset Name -> Choose Data source from drop down ->Click Query Designer
MDX Query Designer which will allow you to do drag & drop of measures & fields from different dimensions for designing of your dataset.
or
You can also write Custom MDX Query for your dataset in the same query editor by changing Design Mode from Tool bar of Query Editor.
- Let us do Drag & Drop of measures and Dimensional Attributes as per shown in below figure.
Step 4 : Create Parameters In Dataset to Filter Records
We need 2 parameters for specifying Date Range one for From Date and another for To Date.
We also need one more parameter to filter records by supplying Product Name.
- Drag & Drop DateKey field from Dim Date Dimension to Parameter section -> Select Operator Range(Inclusive) -> Specify Filter Expression (range) -> Select Checkbox in Parameters.
- Drage & Drop Product Name field from Dim Product Dimension to Parameter section -> Select Operator Equal-> Select Check box in Parameters -> Click OK.
Here you can see MDX query prepared by designer automatically for your dataset as per your field selection.
- Click OK to close the dialog.
Step 5 : Add New Report
- In Solution Explorer -> Right Click on Reports -> Click on Add ->Click New Item.
- Select Report Project-> Select Report -> Assign Name -> Click Add.
Step 6 : Add Shared Datasource & Dataset in Report Data Explorer
- Click on Report Designer Surface -> View ->Report Data or Press ( Ctrl +Alt +D ) to Open Report Data pane.
Add Shared Data source in Report Data Pane.
- In Report Data Explorer-> Right Click on Data Sources ->Select Radio button Use shared data source reference ->Choose Data source from drop down ->Assign Name -> Click OK.
Add Shared Dataset in Report Data Pane.
- In Report Data Explorer->Right Click on Datasets ->Click Add Dataset ->Select radio button Use a shared dataset ->Select dataset which you created previously -> Assign Name -> Click OK.
Step 7 : Change Report Parameter Properties
Now let us change properties of Report parameters FromDimDateDateKey & ToDimDateDateKey. Let us change data type to Date/Time so it will appear as Date Picker Control while we preview or Run the Report, this will ease date select using Date Picker control and give date in dd/MM/yyyy or MM/dd/yyyy format.
- Double Click on FromDimDateDateKey to Open its Properties -> General ->Change Data type ->Change display name in Prompt.
After setting values in General section now we have to remove default date value assigned to date parameter.
- Select Tab Default Values ->Select Fully Qualified date value shown in Value Drop down ->Click Delete ->Click OK.
- Similarly Change Data type for parameter DimToDateDateKey to Date/Time and Remove its Default Value.
Step 8 : Create Look up Data set to fill values in Product Parameter
Here we will create dataset which will have only product name and its Key values in it.
- In Report Data ->Right Click on Datasets ->Click Add Dataset->Assign Name -> Select Radio button Use a dataset embedded in my report -> Click Query Designer.
Click on button Design Mode in toolbar of Query Designer to change the mode for typing your MDX Query --> Type your MDX Query -> Click ! to Execute MDX Query & Check the Query Result.
MDX Query :
SELECT { } ON COLUMNS,
{
(
[Dim Product].[Product Key].[Product Key].ALLMEMBERS *
[Dim Product].[Product Name].[Product Name].ALLMEMBERS
)
} ON ROWS
FROM [SalesAnalyticalCube]
Step 9 : Specify Available Values for Product Parameter
- In Report Data Explorer-> Double Click on DimProductProductName ->General ->Specify Display Name in Prompt ->Choose Data type as Text.
After assigning values in General tab of Report Parameter Properties
- Click Tab Available Values -> Choose radio button Get Values from a query -> Choose Lookup Dataset ->Choose Dataset field which you want to pass as value in Value field -> Choose Dataset field which you want to display in drop down as Label field->Click OK.
Step 10 : Place Expression on Dataset (DsetSalesData) Parameter to Convert Values
In the Report we converted data type of both report parameters to Date/Time and due to that when we select date from the control it will be in dd/MM/yyyy or MM/dd/yyyy format.
While In dataset DsetSalesData datekey parameters are used which is expecting value in yyyyMMdd format.
So to Convert supplied Date Parameter Value from Date to Datekey we need to modify Parameter Value Expression using fx in both date parameters ( FromDimDateDateKey & ToDimDateDateKey) of dataset DsetSalesData.
- In Report Data Explorer->Double Click on DsetSalesData ->Click Parameters -> Click fx to change Expression for FromDimDateDateKey (3).
- Modify & Set Expression Value for FromDimDateDateKey->Click OK.
- Similarly Change Parameter Value Expression using fx for DimToDateDateKey by following above steps.
- Use following Value to Set in expression fx of Report Parameter Value for DimToDateDateKey (4).
="[Dim Date].[Date Key].&[" & Format(Parameters!ToDimDateDateKey.Value,"yyyyMMdd") + "]"
- Modify Expression for Parameter Value of DimProductProductName-> Click fx (5).
- Modify & Set Expression Value for DimProductProductName -> Click OK.
="[Dim Product].[Product Name].& [" + Parameters!DimProductProductName.Value + "]"
Step 11 : Design the Report
What ever you specify in Page Header and Footer it will be repeated on all the pages of the Report.
Let us Insert PageHeader and Page Footer in the Report.
- Right Click in Report Design Area-> Select Insert ->Click PageHeader.
- Right Click in Report Design Area-> Select Insert ->Click PageFooter.
- Select ToolBox- >Drag & Drop TextBox in PageHeader ->Assign your Report Title in TextBox.
- From ToolBox -> Drag & Drop Matrix in Report Design Area.
- Drag & Drop Full_Date_UK in Columns section of Matrix. or Drag & Drop Full_Date_UK in Column Groups.
- Drag & Drop Product_Name in Rows section of Matrix. or Drag & Drop Product_Name in Row Groups.
- Drag & Drop Quantity in Data section of Matrix.
Now your report is ready let us preview it.
Preview Report in SSDT Development Environment
- Click -> Preview -> Choose From Date->Choose To Date ->Select Product Name ->Click View Report.
12. Deploy the Report, Data source & Dataset on the Report Server
Here I assume your Report Server is Configured & You have credentials to do Login in SSRS using Report Manager from Web browser.
Set Deployment Properties first in your project properties
- In Solution Explorer-> Right Click on SalesAnalysisReports-> Click Properties.
- Set Target Server URL -> Set Target Server Version-> Click OK.
- In Solution Explorer-> Right Click on SalesAnalysisReports ->Click Deploy.
View Deployment Status in Output window.
Preview Report using Report Manager
- Open Internet Explorer or Mozilla FireFox -> Type Report Manger URL ->Press Enter -> Supply Credential if needed for Login to Report manager -> Click folder SalesAnalysisReports.
- Click on Report ->SalesAnalyticsReport.
- Choose From Date -> Choose To Date -> Select Product Name -> Click on View Report.
Points of Interest
You can modify your parameter to allow Multiple selection of Values in Product Parameter. I will shortly update the Article, I hope you have enjoyed the article and gained good knowledge.
Enjoy SQL Intelligence.
Friends, if you like my article, Please do not forget to vote for me.
