Click here to Skip to main content
14,605,926 members

Create First SSRS Report with SQL Server Analysis Services OLAP Cube

Rate this:
4.88 (49 votes)
Please Sign up or sign in to vote.
4.88 (49 votes)
20 Jul 2014CPOL
This Article will give you a good start for Creating SSRS Report First time with SQL Server Analysis Services OLAP Cube

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).

Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2012

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

Image 1

  • Select "Report Server Project" from the list -> Enter a project Name and specify project Location->  click OK.

Image 2

 

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.

Image 3

  • Select Type Of Source ->Ms. Sql Server Analysis Services->Assign Connection Name -> Click on Edit.

Image 4

  • Assign Analysis Services Instance name in Server Name ->Choose Your Analysis services Database Name ->Test Connection for success -> Click OK.

Image 5

  • Click OK to Complete Creation of Shared Connection.

Image 6

 

Step 3 : Create Shared Datasets

  • In Solution Explorer -> Right Click on -> Shared Datasets -> Click On -> Add New Dataset.

Image 7

  • Enter Dataset Name -> Choose Data source from drop down ->Click Query Designer

Image 8

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.

Image 9

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.

Image 10

Here you can see MDX query prepared by designer automatically for your dataset as per your field selection.

  • Click OK to close the dialog.

Image 11

 

Step 5 : Add New Report

  • In Solution Explorer -> Right Click on Reports -> Click on Add ->Click New Item.

Image 12

  • Select Report Project-> Select Report -> Assign Name -> Click Add.

Image 13

 

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.

Image 14

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.

Image 15

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.

Image 16

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.

Image 17

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.

Image 18

  • 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.

Image 19

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.

Image 20

MDX Query :

SELECT  { } ON COLUMNS,
{ 
(
[Dim Product].[Product Key].[Product Key].ALLMEMBERS * 
[Dim Product].[Product Name].[Product Name].ALLMEMBERS 
) 
} ON ROWS 
FROM [SalesAnalyticalCube] 
  • Click OK.

Image 21

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.

Image 22

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.

Image 23

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).

Image 24

  • Modify & Set Expression Value for FromDimDateDateKey->Click OK.

Image 25

  • 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).

Image 26

  • Modify & Set Expression Value for DimProductProductName -> Click OK.

Image 27

 

="[Dim Product].[Product Name].& [" + Parameters!DimProductProductName.Value + "]"
  • Click ->OK

Image 28

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.

Image 29

  • Select ToolBox- >Drag & Drop TextBox in PageHeader ->Assign your Report Title in TextBox.
  • From ToolBox -> Drag & Drop Matrix in Report Design Area.

Image 30

  • 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.

Image 31

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.

Image 32

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.

Image 33

  • In Solution Explorer-> Right Click on SalesAnalysisReports ->Click Deploy.

Image 34

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.

Image 35

  • Click on Report ->SalesAnalyticsReport.

Image 36

  • Choose From Date -> Choose To Date -> Select Product Name -> Click on View Report.

Image 37

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.

 

 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Mubin M. Shaikh
Architect
India India
Microsoft® Certified Professional (Microsoft Certification ID: 8918672).

Microsoft Certified Technology Specialist with more than 13+ years of experience to architect effective solutions for various Analytical, Reporting & Visualization requirement using MS. BI toolkit, Data warehouse & Various heterogeneous source systems

Technology :
(MS.BI, SSIS, SSAS, SSRS, SQL-Server, MySQL, Pentaho, Talend, Data Warehouse, Tableau, Power BI, Qlikview, MS.SQL DW on Azure, Google BigQuery, Snowflake, Redshift, C#.Net, ASP.Net)
Domain : Sales, Retail, CRM, Public Transport, Media & Entertainment

Linked In Profile:

Click Here to View Linked In Profile

Change Will Not Come If We Wait for Some Other Person,or Wait for Some Other Time, We are the One We are Waiting For,We are the Change That we Seek.

Comments and Discussions

 
QuestionDisplay to mvc project Pin
CSE Rashedul31-Oct-19 1:31
MemberCSE Rashedul31-Oct-19 1:31 
QuestionMDX:STRTOMEMBER function error Pin
umair_tarik25-Aug-16 23:15
professionalumair_tarik25-Aug-16 23:15 
Praiseexcellent - my vote 0f 5 Pin
Member 1209088129-Oct-15 14:44
MemberMember 1209088129-Oct-15 14:44 
GeneralMy vote of 5 Pin
ham rez14-Sep-15 19:30
Memberham rez14-Sep-15 19:30 
GeneralMy vote of 5 Pin
Member 1186613027-Jul-15 3:07
MemberMember 1186613027-Jul-15 3:07 
QuestionCalendar date picker is not working properly Pin
Member 1131873417-Dec-14 10:57
MemberMember 1131873417-Dec-14 10:57 
AnswerRe: Calendar date picker is not working properly Pin
Mubin M. Shaikh17-Dec-14 16:55
professionalMubin M. Shaikh17-Dec-14 16:55 
GeneralRe: Calendar date picker is not working properly Pin
Member 1131873418-Dec-14 17:09
MemberMember 1131873418-Dec-14 17:09 
GeneralRe: Calendar date picker is not working properly Pin
Member 1131873429-Dec-14 5:59
MemberMember 1131873429-Dec-14 5:59 
AnswerRe: Calendar date picker is not working properly Pin
Mubin M. Shaikh29-Dec-14 15:58
professionalMubin M. Shaikh29-Dec-14 15:58 
QuestionError: Overload resolution failed because no accessible 'DatePart' can be called without a narrowing conversion Pin
Member 1120646012-Nov-14 10:19
MemberMember 1120646012-Nov-14 10:19 
GeneralMy vote of 1 Pin
HikmetT Ttuncer2-Sep-14 22:50
MemberHikmetT Ttuncer2-Sep-14 22:50 
QuestionZero result when I preview a report Pin
Member 1102596320-Aug-14 18:02
MemberMember 1102596320-Aug-14 18:02 
GeneralMy vote of 5 Pin
Ștefan-Mihai MOGA13-Aug-14 3:20
professionalȘtefan-Mihai MOGA13-Aug-14 3:20 
This is a great inspiring article. I am pretty much pleased with your good work. You put really very helpful information. Keep it up once again.
QuestionValue expression for the query parameter 'DimProductProductName' contains an error Pin
thanhtam2329-Jul-14 1:10
Memberthanhtam2329-Jul-14 1:10 
AnswerRe: Value expression for the query parameter 'DimProductProductName' contains an error Pin
Mubin M. Shaikh29-Jul-14 6:56
professionalMubin M. Shaikh29-Jul-14 6:56 
GeneralRe: Value expression for the query parameter 'DimProductProductName' contains an error Pin
thanhtam2329-Jul-14 16:20
Memberthanhtam2329-Jul-14 16:20 
GeneralRe: Value expression for the query parameter 'DimProductProductName' contains an error Pin
Mubin M. Shaikh29-Jul-14 21:15
professionalMubin M. Shaikh29-Jul-14 21:15 
GeneralRe: Value expression for the query parameter 'DimProductProductName' contains an error Pin
thanhtam2330-Jul-14 1:00
Memberthanhtam2330-Jul-14 1:00 
GeneralRe: Value expression for the query parameter 'DimProductProductName' contains an error Pin
Mubin M. Shaikh30-Jul-14 17:37
professionalMubin M. Shaikh30-Jul-14 17:37 
GeneralRe: Value expression for the query parameter 'DimProductProductName' contains an error Pin
Member 1160461215-Apr-15 22:59
MemberMember 1160461215-Apr-15 22:59 
GeneralAwesome Post Pin
bishnuagrawal21-Jul-14 22:44
Memberbishnuagrawal21-Jul-14 22:44 
GeneralVery good article! Pin
Volynsky Alex21-Jul-14 10:18
professionalVolynsky Alex21-Jul-14 10:18 
QuestionMy Vote 5 Pin
Sunasara Imdadhusen20-Jul-14 18:44
professionalSunasara Imdadhusen20-Jul-14 18:44 
GeneralRe: Very Good Article Pin
Member 1144354011-Feb-15 1:35
MemberMember 1144354011-Feb-15 1:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Article
Posted 20 Jul 2014

Stats

150.8K views
1.9K downloads
38 bookmarked