Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Developing a SSRS report using a SSAS Data Source


By:   |   Read Comments (36)   |   Related Tips: > Reporting Services Data Sources

Attend a SQL Server Conference for FREE >> click to learn more


Problem

After designing several SSRS reports based on regular relational databases, your boss would now like several new reports to be designed and rolled out to production based on your organization's SSAS OLAP cube. How do you get started with designing a report based on a cube?

Solution

The creation of an OLAP SSAS report is similar to other SSRS report development once you get to the actual report layout. However, designing your data sources and datasets varies quite a bit from normal relational database data sources and datasets.

The first step in creating a new OLAP based SSRS report is to create a New Project as noted in the below figure. Of course, you could also add a new data source, dataset, and report to an existing project; however in our example, we will utilize a new project with a new report.

New Project

First you will want to create a new Report. We are going to utilize data sources and datasets which reside within the report as opposed to utilizing shared datasets and data sources. To Start the New Report Wizard, Right click on Reports, and select Add New Report.

New Report

The New Report Wizard opens and the first step in the wizard allows for the creation of a data source. You will fill in the New Data Source Name and change the Type to Microsoft SQL Server Analysis Services. Next, select the Connection String Edit button. Fill in your Server Name and then select or type in the appropriate SSAS database name. Be sure to test the connection using the Test Connection.

New Data Source

Depending on the roles created for SSAS Cube you are connecting to, you may have to change the access details noted in the Credentials window. In our demo we will use integrated security, so on the Connection Properties dialog box, click OK.

After setting up your data source and clicking next, you will now create your dataset to be used in the report. This step requires planning and forethought as to what fields will be displayed on the report and what fields will be used to filter the data both when establishing the dataset and during report generation as parameters. These decisions will impact how the data is returned to the design grid and ultimately to the report itself. In order to define your dataset, click the Query Builder button.

Main Query Screen

The Query Builder button opens the Query Designer Window as displayed in the below figure.

Main Query Screen

The SSAS query designer has several options and methods that will ultimately affect your end report. Initially, you will notice that you have the ability to drag and drop both dimensions and measures onto the results grids. You will additionally notice how the results grid actually flattens your datasets. You can add entire dimensions in one motion by clicking and dragging the Dimension folder onto the results area. Also, the same process works for hierarchies, although you can also add individual attributes from the hierarchy if needed.

Query Builder Complete

There are several buttons in tool bar of the Query Builder that should be noted. For full descriptions of the buttons, please see Analysis Services MDX Query Designer User Interface at http://msdn.microsoft.com/en-us/library/ms403829.aspx. The Show Empty Cells works this same as the MDX Non Empty clause; basically it shows / hides non empty cell values. The Auto Exec button turns on and off the auto execution of queries as you drag and drop dimensions, measures, hierarchies, and attributes onto the results pane. As the results get larger and more complex, it could take longer and longer for the query results to return. The Show Aggregates button again toggles the view of aggregates on and off. Last, the Add Calculated Members button allows for the addition of calculations at the query level. These calculated members could also potentially be added at the report level instead.

Query Builder Button

Furthermore, the query designer contains a filter grid in the upper half of the design area. This filter area serves several purposes:

  1.  Acts as a method to filter the dataset at the query level.
  2. Allows for the easy creation of filters which can also act as parameters. (see additional details about parameters below).

Filters can either be individual attributes or individual parts hierarchies.

Filter Dimensions

The above example filters the dataset at the query level; basically, the query will be restricted to only those items who Calendar Year equals CY 2003. You will also notice that the parameter box is not checked in this first example. Last, since the Auto Exec button is toggled on, the query results will display Calendar Year 2003 data. At this point, we will complete the rest of the wizard setup to create a basic report. Later in our tip, we will discuss the parameter option. Going back to change this option later will also allow us to review making changes to the Data Set after completing the Wizard. Clicking Ok on the Query Builder screen returns you to the Report Wizard with the MDX query now being displayed in the Query String as noted in the next figure. Furthermore, notice that the Calendar Year filter is part of the MDX query text.

QueryString

The report type is selected on the next screen; a matrix report will be used in this demo.

ReportType

We will complete the report by placing Year and Quarter in the Column group, Country in the Row group, and Internet Group Profit and Internet Order Count in the Details area.

Matrix Layout

Next, accept the Matrix Style of Slate.

Matrix Style

Now, finish out the report by completing the Report Name.

ReportFinish

The initial SSAS Report design is now complete. A preview of the design and end report is displayed in the next two figures.

Matrix Style

Of course to completely finish the report you may want to complete some formatting. As such, the numeric fields were immediately formatted to display commas and no decimals.

Report Preview

Our next step is to adjust the dataset filters and replace the hard coded filter for the Calendar Year to a parameter based filter where the report consumers can select which year to display. As noted in the next figure, expand the Datasets folder, and then right click on DataSet1, and select Query to display the Query designer..

Query Step 2

Now, change the Filter Expression to include CY 2002, CY 2003, and CY 2004. Also, check the Parameters check box.

Parameter

Adding these changes now creates a parameter for the Calendar Year which is displayed by expanding the Parameters folder, as shown in the following figure.

Parameter2

Checking the parameter box also adds a hidden dataset, that can be shown by right clicking Datasets and selecting Show Hidden Datasets.

Hidden Data Sets Hidden Data Sets 2

After making this change to add the Calendar Year parameters and previewing the report, as shown in the next figure, you will notice two Alls, Select All and All Periods, appear in the drop down list. The first All is driven by the Report Server MultiParameter option while the second is driven from the MDX query that SSRS runs to populate the Parameters list.

Paramaters 2 Alls

To alleviate this situation, we have several options. Either we can adjust the MDX query or we can add a filter to the dataset to remove the All Member from the results. Using the first option, right click on the Date Calendar Year hidden dataset (after using the technique noted above to show the hidden data set), and then select Query. The first figure below displays the current query. Changing the highlighted word, ALLMEMBERS to CHILDREN adjusts the query to display only the Children of the Calendar Year list and not the All member option.

MDXHidden1
MDX Hidden New Query

Alternately, we can filter the dataset using the filter dataset method; right click on the Date Calendar Year hidden dataset (after using the technique noted above to show the hidden data set), and then select Query Properties. The first figure below displays the current query with its related result set. Within the Dataset Properties Window, clicking on the Filter Option, allows us to filter on any of the fields being returned from the query. In this particular instance, I see that we can easily exclude the All Periods item, by using a filter of ParameterLevel > 0 which is displayed in the second figure below.

Dataset filter Query
Dataset Filter

Now when we run the report, as displayed in the next figure, only the individual years are displayed which provides for a much cleaner option for the report consumers.

Parameter Box

Conclusion-Creating a Cube Based Reporting Services Report

In this tutorial, we reviewed the process of creating a Report Services report based on a SSAS OLAP cube. We learned about the process of creating an initial report using the Create Report Wizard and identified the specific places where attention needs to be focused during the wizard steps. In particular we need to pay close attention to the fields we place on the results grid along with the attributes and dimensions used in the filter area. Furthermore we addressed adding a parameter to a report and specifically adjusting the parameter query or filter to properly display the parameter options.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Sunday, March 26, 2017 - 11:09:59 AM - Deepak Back To Top

 Hi Scott,

 It is nice article. I would like to know how to create cascading parameter with multilevel hierarchy.

 Thanks!!


Tuesday, January 24, 2017 - 8:28:53 AM - Pilar Back To Top

 Hi Scott, thanks for posting this article, it is very helpful!.  I need to create a SSRS report reading from SSAS tabular and have a special request, the grouping in the report must be dynamic.  There are 3 different ways of grouping the report that are being passed through parameters (group1, group2, group3). 

What would be the best way to accomplish this?  If I do the conditional grouping in the report would the aggregation be done by SSRS or by SSAS?   I don't know how to implement this using MDX, do you have an example?

Thanks for your help,

Pilar

 


Tuesday, January 17, 2017 - 2:48:45 PM - Jacque Back To Top

I was just starting to look into creating a SSRS report off a cube, glad I found this article.  I really like how you explain things.

Thanks!

 


Thursday, December 18, 2014 - 8:33:14 AM - Scott Back To Top

You need to check what security you are using in the data source. The double hop is still an issue when using mulitple servers.


Thursday, December 18, 2014 - 3:30:01 AM - Effie Back To Top

 

Very helpful, thanks

I've managed to create it and all is working on local machine. However, when I'm deploying it, it's not working from SSRS report server web access. Seems like windows authentication can't pass to SSAS credentials.


Tuesday, February 11, 2014 - 3:38:43 PM - Scott Murray Back To Top

Yes you can connect to a tablular model on a Sharepoint Server.  I would recommend starting with: http://technet.microsoft.com/en-us/library/hh270317.aspx.  Once you have a data source, you should be able to pass a parameter; however the security is based on the authentication used in the data source.. Subscriptions present a dilema as you much define who is running the subscription.


Tuesday, February 11, 2014 - 12:51:37 PM - Hatty Back To Top

Hi Scott - Excellent post and really nice to see your response to other's queries.

I have a question on SSRS subscription using with SSAS tabular model with parameter mode. My questions in this context listed below.

1. Is this possible to use - i mean can i use SSRS subscription to generate SSRS reports sourced by SSAS tabular model by passing parameters? 

2. If the answer to above question is "Yes" - then in addition to that is it possible to make use of role based tabular security in Subscription by passing user-id as parameter?

3. For any of the above question considering feasibility - may you please share some reference or document on how this can be done?

 

Many thanks, 


Thursday, December 12, 2013 - 12:00:10 PM - Scott Murray Back To Top

No SAS sources are not supported.


Thursday, December 12, 2013 - 9:28:20 AM - Bharat Thakkar Back To Top

Hi Scott,

  I like the SQL report system and you have explained very well. The question I have is can use as my data source my SAS datasets which are stored on network. If so, have you had any experience or any advice on how to utilize your reporting method and using SAS datasets? Thanks Bharat.


Wednesday, November 20, 2013 - 1:34:26 PM - boughedda Back To Top

Thank you scott, it works wonderfully!


Sunday, November 17, 2013 - 12:06:42 PM - Scott Murray Back To Top

SSRS is doing its own ordering.... again you can set the order of the group (row or column) or the detailed row of the tablix using the sort order property. SSRS is using the default order which is A-Z.  The solution is to bring in both the month name and the month number and sort using the month number.


Saturday, November 16, 2013 - 5:46:46 PM - boughedda Back To Top

 

   Hello Scott

  Thank you for your answer, but my problem is when I built a cube with SSAS, I apply the rule of order for the month dimension chronologically, example: January, February, ... , but on SSRS, when i created a datasource, Effective a data set previously created on the cube when I elaborated a tablix, the months dimension is not sorted, for example: April, Agust, December, etc ... rather then January, February, ...
So would you help me to obtain the ordered dimensions on SSRS please ?

Saturday, November 09, 2013 - 7:11:18 AM - scott Back To Top

You must order in the query.


Saturday, November 09, 2013 - 4:16:30 AM - boughedda Back To Top

HI!

why although the dimension is ordered in SSAS, it appears disordered in SSRS?


Monday, August 26, 2013 - 12:31:18 PM - Scott Murray Back To Top

You should be able to pass a parameter, such as country, to the report.  Check out this link (it was quicker than me writing it out)...

http://saldeloera.wordpress.com/2013/02/07/ssrs-how-to-straightforward-method-to-pass-mdx-multi-select-parameters-to-mdx-datasets/


Monday, August 26, 2013 - 11:57:47 AM - AG Back To Top

Hello I have question regarding linked reports

I can’t seem to figure out how to pass a MDX or Cube based parameter in linked report

Example:

If in the report in the example you created a link on Internet Gross Profit where after clicking on the number grouped under say for example Australia, it will pass the related Country Value to the detailed report to generate and show the user.

Thanks for the help

 

 

 

 

 

 


Thursday, August 01, 2013 - 11:03:12 AM - Gary Back To Top

Thanks, but I wish you would have gone more into detail.  And the report refuses to run at all.  I just get the busy icon until I kill off VS2008.

I should add that this is the first SSAS report I have written and I'm not very versed in cubes.  SSAS seems to be a black hole as far as internet tutorials go.


Wednesday, June 19, 2013 - 8:23:03 AM - Avineet Back To Top

Hi, I would like to have the drag and drop feature of the dimensions that are available as a tree view in SSAS, in SSRS. How can this be achieved. I beleive there is no web interface from MS for hosting SSAS cubes directly. Obviously the above example shows how you can use SSAS cube to create a static SSRS report, but you can do dynamic filtering of the data elements add or delete dimensions. Not sure if PowerPivot can provide this.


Thursday, December 27, 2012 - 5:54:14 AM - Scott Back To Top

Ganesh:  Please review:  http://picnicerror.net/development/sql-server/connect-ssrs-report-builder-ssas-cube-2012-05-28/


Wednesday, December 26, 2012 - 7:31:44 AM - Ganesh Back To Top

how to connect SSAS to SSRS give detaild steps

 


Wednesday, November 07, 2012 - 2:10:41 AM - Raja Back To Top

Thank you so much for this post. Gretaly appreciated. Saved me heaps of time on a verycritical project. :)

Would be great to see more such stuff :)


Wednesday, September 12, 2012 - 4:05:18 PM - Scott Murray Back To Top

Suraj,

 

You can filter your list using either criteria in the query or on the parameter screen, use the fitler window.


Wednesday, September 12, 2012 - 12:50:38 PM - Suraj Back To Top

Hi Scott. I like the way you explained. I have a question similar to this. In your above example, can you show me how to filter parameter list for eg. display cy 2003 and cy 2004 as the only options available for parameter values.


Tuesday, August 21, 2012 - 10:23:24 AM - Scott Murray Back To Top

Vish,


You can just go to the tablix properties and select filter.  Then insert the items you want to filter by.


Tuesday, August 21, 2012 - 9:19:24 AM - Srini Back To Top

Hi Scott,

 

Excellent post, today without wasting my time to search here and there for the solution, I done using this article. it helped me a lot. Thanks again for the post.

Excellent blog, I like it.

 

Srini


Wednesday, July 11, 2012 - 5:27:33 AM - raghav Back To Top

Awesome :)


Tuesday, July 03, 2012 - 2:51:49 AM - Vish Back To Top

 

Hi Scott,

thanks for the response. But can you elaborate your point that how can i provid that functionality to a report user that he will be able to choose the fields which he want to see in the report.

thanks in advance.


Monday, July 02, 2012 - 10:14:48 AM - Scott Murray Back To Top

You could definitely use report filters to limit the list of fields which appear on the 


Monday, July 02, 2012 - 9:39:51 AM - Vish Back To Top

Hi Scott,

thanks for this really simple and step by step process for developing reports. 

i want to ask one thing that can we choose our members also which should be shown on the report .  Like suppose i have 4 dimensions containing 6-10 fields each and 1 Fact table containing 4 measures. than if i(as a user of the report) want to select only 1-2 fields from the dimensions and all measures to create the report ( I am not talking about the filters in which we select the field value i am talking about the fields ).

for ex.   I have fields like Customer_ID , Cust_name, Project_Id, Project_name  etc.  in Project Dimension and Measures ae like Time_2_Complete, Billed Hours etc.  In My report I want only Cust_name And Project_name against both measure so user should be able to select only those 2 fields at the time of accessing the report.  ( Just Like the Pivot table)

Can we do that by SSRS and if yes then How???

Thanks In advance...

 

 


Wednesday, June 27, 2012 - 2:46:02 AM - Prasadi Back To Top

Excellent Post. Really good and understandable to the beginners.

Keep doing good work.Really appriciate If u can give a post regarding the develop cube and data warehouse.

Thanks


Wednesday, June 13, 2012 - 7:47:21 AM - Scott Murray Back To Top

You could certainly use report builder.


Wednesday, June 13, 2012 - 7:28:42 AM - Rohit Acharya Back To Top

What about authoring the report in Report Builder and also connecting to a Tabular Mode cube.


Wednesday, June 13, 2012 - 6:55:54 AM - Scott Back To Top

Sutha....

There are several good tutorials on creating a SSRS dataset.  I am am unsure of your exact question, so I would recommend starting at:  http://msdn.microsoft.com/en-us/library/ff714047%28v=sql.105%29.aspx

 


Wednesday, June 13, 2012 - 2:45:33 AM - Axel Back To Top

I like it!!!


Wednesday, June 13, 2012 - 2:29:42 AM - Sutha.K Back To Top

SSRS is very nicely explained. But i need to know with dataset concept, I 've used dataset also though if i delete existing dataset and create new dataset, THat time dataset name is taking with existing dataset name which is deleted. Waht is the solution for this. 


Monday, June 11, 2012 - 5:15:55 AM - Devi Prasad Back To Top

Hey Scott, appreciate the way you have explained this lession. keep posting like this..


Learn more about SQL Server tools