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

 

Automate field selection from SSAS Tabular Models in Power View reports


By:   |   Last Updated: 2016-12-19   |   Comments   |   Related Tips: 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




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.



    



Learn more about SQL Server tools