Automate field selection from SSAS Tabular Models in Power View reports


By:   |   Updated: 2016-12-19   |   Comments   |   Related: More > Microsoft Excel Integration

Problem

Identifying and incorporating required fields is a regular part of any report development process. For business users who frequently develop ad-hoc reports using tools like Power View, it becomes an integral part of their day-to-day job. Mostly there are certain mandatory or frequently used fields which are part of every report irrespective of the purpose of the report. If such field selection in the report can be automated, this can save significant time for the user as well as make the report development process more efficient by reducing the possibility of errors.

Solution

The SQL Server Analysis Services (SSAS) 2016 Tabular model provides reporting properties for any table in the model, which facilitates configuration of default field selection behavior for each table.

In this tip we will look at a particular scenario where we will automate the field selection from a particular table in a Power View report. Follow the steps below to achieve this goal:

Step 1 - We need a sample SSAS Tabular model to create a Power View report. I have the AdventureWorks 2014 Tabular Model installed on my machine. You can download the sample from here, and install it on your development machine.

Step 2 - Open this model in the Visual Studio Data Tools, and click on the Customer table. You should be able to see the data as shown below.

Customer Table

Step 3 - Navigate to the properties window by selecting properties of any column, and click on the Table Detail position property. Let's say that business users need CustomerID and Email Address as a part of any report that uses the Customer table. So select these fields as a part of the default field set for this table as shown below.

Table Detail Position

Step 4 - Process and deploy this model on your SSAS server. Once deployed, open Excel and create a connection to the SSAS database which we deployed in the last step, and connect to the model. Next, click on the Data > From Other Sources > From Analysis Services menu option. When the Import Data screen loads with the options to create a PivotTable Report / PivotChart / Power View Report / Only Create Connection, select the option to create a Power View Report.

Power View Report

Step 5 - In your new Power View Report, you should be able to see different tables and a list of fields from your model. The table of interest for us is the Customer table as shown below.

Field List

Step 6 - Our prime intention is to automate the selection of the fields for the business user, so that the user does not have to find and select the frequently used fields every time. Just click once on the Customer table, and it will add the configured fields to the list of selected fields as shown below.

Selected Field List

In this way we can configure the behavior of the table during design time such that reporting clients can use it to build their reports.

Next Steps
  • Explore the rest of the Power View reporting properties available for the table, and learn how they can be used the optimize the reporting experience for the user.
  • Check out other Power View tips


Last Updated: 2016-12-19


get scripts

next tip button



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips





Comments For This Article





download


Recommended Reading

How to Create a Burndown Chart in Excel from Scratch

Read Excel File in SQL Server with OPENROWSET or OPENDATASOURCE

Passing Dynamic Query Values from Excel to SQL Server

Export data from SQL Server to Excel

Insert, Update or Delete Data in SQL Server from Excel





get free sql tips
agree to terms


Learn more about SQL Server tools