Tabular Actions Editor

Note: This feature is only available with models in the 1103 compatability mode

This feature provides a UI for editing actions for Tabular models. For example, this feature allows the model designer the ability to customize the columns returned by drillthrough. This feature also allows the model designer to create report, URL, or rowset actions, for example.

Warning: While actions work in Tabular models, they are not officially supported by Microsoft. If you encounter a bug in how Tabular handles actions and open a support case, Microsoft may not provide support.

In a Tabular model (SQL Server 2012 and above only) right click the .bim file to find the menu item to launch this feature:

The BI Developer Extensions Tabular Actions Editor dialog will pop up. To create your first action, click the Add button. Create your actions and click OK. When you click OK, the actions are deployed to the workspace database (just like all other editing of the Tabular model).

Perspectives

Note that you can assign actions to perspectives using this dialog. The Perspectives dialog currently does not support actions, so BI Developer Extensions built its own support in this actions dialog. Most common clicks like adding or removing a table or column from a perspective in the Perspectives dialog will preserve the actions assignments. However, renaming a perspective usually wipes out the actions assignments to perspectives. For this reason, BI Developer Extensions backs up the action perspective assignments to an annotation. If you ever rename a perspective or create a new perspective, just open the BI Developer Extensions Actions Editor dialog and BI Developer Extensions will prompt you "No actions are currently included in any perspectives, but BI Developer Extensions did retain a backup of the perspective assignments from the last actions editing session. Changes made to perspectives may have caused action assignments to be lost. Restoring action perspective assignments may be possible except when a perspective has been renamed. Would you like BI Developer Extensions to attempt restore the action perspective assignments now?". Clicking Yes will tell BI Developer Extensions to attempt a restore. The restore of the action perspective assignments should work except when a perspective has been renamed. Also, the Tabular Pre-Build feature can prompt you to restore the perspective assignments.

Internet Sales Drillthrough Example

Modeled after the Internet Details action from the Multidimensional sample Adventure Works cube, this action demonstrates how BI Developer Extensions can allow you to customize the columns returned from the default drillthrough command which is used when you double click a numeric cell from a PivotTable in Excel. Out of the box, Tabular models just return the columns from the fact table as the default drillthrough return columns. However, most fact tables contain only meaningless surrogate keys, necessitating this BI Developer Extensions UI for customizing drillthrough to show meaningful columns.

Note: The MeasureGroupMeasures function returns an empty set in Tabular models since there are no physical measures, only calculated measures. Plus, there is no way of setting the Target property of a drillthrough action to target more than one calculated measure. So behind the scenes, *BI Developer Extensions creates one identical action per calculated measure. This means that when you add a new calculated measure, you need to open and close the BI Developer Extensions Tabular Actions Editor dialog so the drillthrough action can be targeted to this new calculated measure.*

Note: In Tabular models, you cannot choose measures to be returned. Instead, you choose columns from the fact table or columns from the dimension tables.

Note: Once you have added a drillthrough column, to remove it, left click on the row header (i.e. the space to the left of the Table Name) and press delete. Or alternately right click on the row and choose delete from the context menu.

Note: The UI makes it impossible to add a drillthrough column twice. After you have used a column, it no longer appears in any other row as an option.

City Map Example

Modeled after the City Map action from the Multidimensional sample Adventure Works cube, this action demonstrates a URL action which allows a user to right click on a city and be taken to a mapping website showing that city.

Note: The example above uses MDX for the Caption and Expression. If you want to use DAX, build a calculated measure into your model, then reference it as [Measures].[DAX Measure Name] in this actions dialog.

Sales Reason Comparisons Example

Modeled after the Sales Reason Comparisons action from the Multidimensional sample Adventure Works cube, this action demonstrates a report action which allows a user to right click on a Product Category and be taken to an SSRS report.

Important Note: In SSAS 2016 it appears Report Server needs to include http:// or https:// before the server name.

Note: The example above uses MDX for the Caption and Report Parameter Value Expression. If you want to use DAX, build a calculated measure into your model, then reference it as [Measures].[DAX Measure Name] in this actions dialog.

Note: rs:Command=Render is not shown on the screen but is included in the action’s ReportFormatParameters behind the scenes.

Note: In addition to specifying report parameters, you can also specify other URL commands like the rs:Format command. You must use an MDX expression for rs:Format (and all other report parameters), so if you want to hardcode it, put double quotes around the literal e.g. ‘EXCEL’).

Note: If SSRS is in SharePoint integrated mode you can use the above example, though the report path would need to be the URL to the report (e.g. _vti_bin/ReportServer?http://localhost/sites/bi/Reports/Report Library/AdventureWorks Sample Reports/Sales Reason Comparisons.rdl). Also note that SSRS 2012 in SharePoint Integrated Mode puts the ReportServer URL under /_vti_bin/ReportServer.

Note: If SSRS is in SharePoint integrated mode and you wish to render the report in HTML with the SharePoint site branding surrounding it like when you run a report directly from SharePoint, note the URL commands are different. So an example SSRS SharePoint integrated mode report action might look like the following.

SharePoint Integrated Mode Report Action Example with SharePoint Branding

Important Note: In SSAS 2016 it appears Report Server needs to include http:// or https:// before the server name.

DAX Query Rowset Action Example

The following action demonstrates how to create an action which returns a rowset when you right click on a Sales Territory Region and execute the action.

Note: The example above uses MDX for the Caption and Expression. If you want to use DAX, build a calculated measure into your model, then reference it as [Measures].[DAX Measure Name] in this actions dialog.

Note: The Expression is an MDX expression that’s building a string. The string is a query that can run against Analysis Services. In this case, the string is a DAX query.

Instead of a DAX query, you may want to construct a drillthrough query. This approach is especially helpful when you have already customized the default drillthrough columns for the Reseller Sales measure group (for example) by creating a drillthrough action flagged as Default. The expression in this case would be:

"drillthrough
select
from  [" + Measures].CurrentMember.Properties("CUBE_NAME") + "]
where (
[Measures].[Reseller Total Sales]," 
+ {
    Existing [Sales Territory].[Sales Territory Id].[Sales Territory Id].Members
  }.Item(0).UniqueName + ")"

Note: The example above uses [Measures].CurrentMember.Properties(‘CUBE_NAME’) to determine the name of the current cube. When developing against the workspace database, the main cube is named Sandbox, but using the project properties dialog, you can customize the name of the main cube when the solution is deployed. So be sure to test these actions against the workspace database and against the deployed database.

Measure Definitions Rowset Action

Another possible way to use actions is to provide users with a way of viewing measure definitions from within an Excel PivotTable. For example, if the Description property of measures has been filled in when developing your Tabular model, the following rowset action may be useful:

The action expression returns a string which is a DMV query:

"select
  [MEASURE_CAPTION]
, [MEASUREGROUP_NAME]
, [MEASURE_DISPLAY_FOLDER]
, [DESCRIPTION]
, [EXPRESSION]
from $SYSTEM.MDSCHEMA_MEASURES
where [MEASURE_UNIQUE_NAME] = '" + [Measures].CurrentMember.UniqueName + "'"
AND [CUBE_NAME] = '" + [Measures].CurrentMember.Properties("CUBE_NAME") + "'"

When you launch this action by right-clicking on a measure numeric value in a PivotTable and choosing this action, Excel opens a new tab and displays the following measure definition:

Other Examples

Darren Gosbell posted a complex and powerful example of determining the context and building a custom DAX query in a rowset action.