Create a SSRS Report from an Analysis Services Tabular Database

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

By: Daniel Calbimonte | 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

SQL Server 2014
A Tabular Database installed
SSRS installed
SQL Server Data Tools Installed (SSDT)
The Adventureworks DW Tabular Model

SSRS Report from a Tabular Database

Open SSDT.
Go to File > New > Project:

Select the Report Server Project Wizard:

The welcome message will be displayed:

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:

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:

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

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

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

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

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:

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:

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

Choose the Table Style of your preference:

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:

Specify the report name and check the preview report option:

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

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

Select the first chart type:

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

You will have a graph similar to the following:

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

You can modify the query by pressing the fx button:

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:

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:

SQL Server Business Intelligence Tips

About the author

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

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?