Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Updated: 2016-07-20   |   Comments (1)   |   Related: > Reporting Services Analysis Services

Problem

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

Solution

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.

Requirements

  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

Conclusion

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:



Last Updated: 2016-07-20


get scripts

next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

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    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, September 07, 2017 - 3:29:06 PM - stan teitelbaum Back To Top

 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?



Learn more about SQL Server tools