Developing a SSRS report using a SSAS Data Source

By:   |   Comments (41)   |   Related: > Reporting Services Data Sources


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?


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


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


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.


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.


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.


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.

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, March 17, 2021 - 9:47:05 AM - Scott Murray Back To Top (88429)
SSRS only uses the user account used to connect in the data source.

Wednesday, March 17, 2021 - 8:57:28 AM - Ravikanth Back To Top (88426)
Hello Scott, I have an SSRS report whose source is SSAS Tabular. Security has been defined in the semantic layer, but security is not getting applied in SSRS report. What could be the probable reason.

Wednesday, February 7, 2018 - 11:19:33 PM - Aniruddha Back To Top (75133)



well thanks Scott,

but I have different req.

I already have a calender on SSRS.

I have a textbox parameter in which if I put 2 (sec), records that are there in difference of two seconds should not be displayed.

Wednesday, February 7, 2018 - 9:14:51 AM - Scott Murray Back To Top (75129)


 In SSRS you should be able to use a datatime parameter which will show minutes.

Wednesday, February 7, 2018 - 1:38:58 AM - Aniruddha Back To Top (75124)

Very Nice Article Scott.
How can I fetch records based on minute and seconds in datetime field in SSRS? Say I have dateandtime column in my sql server table as
2017-06-12 22:17:14.000
2017-06-12 22:17:16.000
2017-06-12 22:17:19.000
2017-06-12 22:17:21.000
In which data is dumped automatically after n seconds, lets say after every 2 seconds.
My req is that I need to select those records only which are not dumped after every 2 seconds (means which are dumped after every != 2 (1 or 3 or 5 or n) seonds) while putting 2 (second) in textbox parameter in SSRS report designer.
Any solution will be appreciated. Anybody?
i need to filter this because this report has very large pages, more than 100, as data is dumping after every 2 seconds or n seconds for same day/date.

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

 Hi Scott,

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


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

 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,



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

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.



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

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 (35656)


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 (29411)

Yes you can connect to a tablular model on a Sharepoint Server.  I would recommend starting with:  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 (29407)

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 (27782)

No SAS sources are not supported.

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

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 (27556)

Thank you scott, it works wonderfully!

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

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 (27523)


   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 9, 2013 - 7:11:18 AM - scott Back To Top (27455)

You must order in the query.

Saturday, November 9, 2013 - 4:16:30 AM - boughedda Back To Top (27454)


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 (26487)

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

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

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


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 1, 2013 - 11:03:12 AM - Gary Back To Top (26088)

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 (25481)

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 (21150)

Ganesh:  Please review:

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

how to connect SSAS to SSRS give detaild steps


Wednesday, November 7, 2012 - 2:10:41 AM - Raja Back To Top (20240)

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 (19495)



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 (19488)

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 (19146)


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 (19140)

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.



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

Awesome :)

Tuesday, July 3, 2012 - 2:51:49 AM - Vish Back To Top (18319)


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 2, 2012 - 10:14:48 AM - Scott Murray Back To Top (18300)

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

Monday, July 2, 2012 - 9:39:51 AM - Vish Back To Top (18296)

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 (18213)

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.


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

You could certainly use report builder.

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

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 (17961)


There are several good tutorials on creating a SSRS dataset.  I am am unsure of your exact question, so I would recommend starting at:


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

I like it!!!

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

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 (17923)

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

get free sql tips
agree to terms