Create a SQL Server Reporting Services Report from an Analysis Services Tabular Database

By:   |   Comments (1)   |   Related: > Reporting Services Analysis Services


What are the steps to create a SQL Server Reporting Services (SSRS) Report from an Analysis Services Tabular Database?


In this tip, we will show how to create reports using SQL Server Reporting Services (SSRS) from a Analysis Services Tabular Databases including charts and data set filters.


  1. SQL Server 2014
  2. A Tabular Database installed
  3. SSRS installed
  4. SQL Server Data Tools Installed (SSDT)
  5. The Adventureworks DW Tabular Model

SSRS Report from a Tabular Database

  1. Open SSDT.
  2. Go to File > New > Project:

  3. New project

  4. Select the Report Server Project Wizard:

  5. Report Server Project

  6. The welcome message will be displayed:

  7. Welcome wizard

  8. The Data Source will contain the connection information. In the type, select the Microsoft SQL Server Analysis Services. This type is used for Multidimensional and Tabular models. Press the edit button:

  9. Selecting data source

  10. Specify the instance name and select the database name. In this example, we will use the local instance named Tabular and the Semantic database which is the Adventureworks database for Tabular databases:

  11. Tabular connection information

  12. Once that the connection string is configured, in the Select the Data Source, press next:

  13. Data source name

  14. In the Design the Query interface, press Query Builder to create a query (you can also create your query manually, but it requires MDX skills):

  15. Design query window

  16. Go to Measures > Internet Total Sales and drag and drop the value to the report pane:

  17. Creating report

  18. In the Geography dimension, drag and drop the country region name to the report. The result is the Total Sales per country:

  19. Dimension in the report

  20. As you can see, the query created is in MDX for the Tabular Database. If you are not familiar with MDX, we recommend you to read our article about MDX. The MDX query is showing the non empty fields of the Internet Total Sales Measure per Country Region Name. It means the Total Sales per country:

  21. MDX query

  22. The next option allows you to select the type of report. Tabular is the traditional report while in the matrix type you can select which attribute is row and which column:

  23. Tabular or Matrix report

  24. In the grouping window, keep the default values and press the Next button:

  25. Report group

  26. Choose the Table Style of your preference:

  27. Table style

  28. Select the Report version. By default, it is SQL Server 2008 R2 or later. You can specify the URL and the name of the folder:

  29. Report version

  30. Specify the report name and check the preview report option:

  31. Report name

  32. Your report can be exported to XML, CSV, TIFF, PDF, MHTML, Excel and Word:

  33. Report created

  34. To create a chart, in the Toolbox drag and drop the chart item:

  35. Chart report

  36. Select the first chart type:

  37. Chart type

  38. Drag and drop the Country Region Name and the Internet Total Sales in the graph:

  39. Chart series and axis

  40. You will have a graph similar to the following:

  41. SRSS Chart created

  42. If you want to modify the query you can do so by right clicking on the Datasets and select Dataset Properties:

  43. Dataset properties

  44. You can modify the query by pressing the fx button:

  45. SSIS option menu

  46. You can also add filters at the dataset level. The following filter excludes France from the list of countries in the report. This filter is not efficient because all the data is retrieved from the query and the filter is applied later. We will talk about filters at the query level in future tips:

  47. SRSS Filter


In this tip we demonstrated how to create reports using the SSRS wizard with a Tabular Database. We also learned how to create charts and how to filter data at the Dataset Level. As you can see, creating reports with a Tabular Database is a straightforward process and it does not require advanced skills in Tabular Databases. We used the Wizard to start, but once you are familiar with the reports it is a good practice to manually customize your reports using shared data sources, data sets, parameters and SSRS expressions.

Next Steps

For more information about SSRS, refer to these links:

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

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

Thursday, September 7, 2017 - 3:29:06 PM - stan teitelbaum Back To Top (66022)

 I cant install sql 2016 yet due to a couple of restrictions.  But I have this question:


 I would like to look ahead to see how ssrs 2016 will interact with my tabular model.  My model has survey data with various dims including interviewer (employee), form, study, case #, questions etc.  And the core measure called AcceptanceRate which is basically #YES / (#YES + #NO).


ssrs 2012 needed a hand crafted query to see my core measure's aggregated values at levels higher than question.  Other client tools (eg excel, power bi, maybe tableau etc) don't need that kind of help.  They interface seamlessly with cubes and tabular models.  And show aggregated measures appropriately in a "pivot table" presentation. 


Am I going to find that ssrs 2016 is better at interfacing with tabular models?  Will it see aggregated measure values without me needing to handcraft crossjoins in dax?  Can it present my data in more of a pivot table kind of mode?  With expand/collapse capability?

get free sql tips
agree to terms