Tabular Actions Editor

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 BIDS Helper 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 BIDS Helper 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, BIDS Helper backs up the action perspective assignments to an annotation. If you ever rename a perspective or create a new perspective, just open the BIDS Helper Actions Editor dialog and BIDS Helper will prompt you "No actions are currently included in any perspectives, but BIDS Helper 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 BIDS Helper to attempt restore the action perspective assignments now?". Clicking Yes will tell BIDS Helper 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 BIDS Helper 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 BIDS Helper UI for customizing drillthrough to show meaningful columns.


Property Setting
Name Internet Details
Caption Drillthrough...
Caption is MDX? Unchecked
Description  
Action Type DrillThrough
Target Type Cells
Target

MeasureGroupMeasures("Internet Sales")

Condition  
Drillthrough Columns

[Customer].[First Name]
[Customer].[Last Name]
[Date].[Date]
[Product].[Product Name]
[Sales Territory].[Sales Territory Region]
[Sales Territory].[Sales Territory Country]
[Promotion].[Promotion Name]
[Internet Sales].[Sales Order Number]
[Internet Sales].[Sales Order Line Number]
[Internet Sales].[Sales Amount]
[Internet Sales].[Extended Amount]
[Internet Sales].[Tax Amt]
[Internet Sales].[Freight]

Default

 True

Maximum Rows

 

Invocation

 Interactive


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, BIDS Helper creates one identical action per calculated measure. This means that when you add a new calculated measure, you need to open and close the BIDS Helper 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.



Property Setting
Name City Map
Caption "View Map for " + [Geography].[City].CurrentMember.Member_Caption + "..."
Caption is MDX? Checked
Description This action will display the map for a given city.
Action Type Url
Target Type AttributeMembers
Target [Geography].[City]
Condition  
Expression

// URL for linking to MSN Maps
"http://maps.msn.com/home.aspx?plce1=" +

// Retreive the name of the current city
[Geography].[City].CurrentMember.Name + "," +

// Append state-province name
{Existing [Geography].[State Province Name].[State Province Name].Members}.Item(0).Name + "," +

// Append country name
{Existing [Geography].[English Country Region Name].[English Country Region Name].Members}.Item(0).Name +

// Append region paramter
"&regn1=" +

// Determine correct region paramter value
Case
    When {Existing [Geography].[English Country Region Name].[English Country Region Name].Members}.Item(0) Is
         [Geography].[English Country Region Name].&[Australia]
    Then "3"
    When {Existing [Geography].[English Country Region Name].[English Country Region Name].Members}.Item(0) Is
         [Geography].[English Country Region Name].&[Canada]
         Or
         {Existing [Geography].[English Country Region Name].[English Country Region Name].Members}.Item(0) Is
         [Geography].[English Country Region Name].&[United States]
    Then "0"
    Else "1"
End

// The "plce1" parameter represents a named location.
// The "regn1" parameter indicates the region in which
// the named location is located.

// 0 = North America
// 1 = Europe
// 2 = World Atlas
// 3 = Australia
// 4 = Brazil

Invocation

 Interactive


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.

Property Setting
Name Sales Reason Comparisons
Caption "Sales Reason Comparisons for " + [Product].[Category].CurrentMember.Member_Caption + "..."
Caption is MDX? Checked
Description This action will launch a report comparing sales reasons for a given product category.
Action Type Report
Target Type AttributeMembers
Target [Product].[Product Category Name]
Condition  
Report Parameters
Parameter Name Parameter Value Expression
ProductCategory UrlEscapeFragment( [Product].[Category].CurrentMember.UniqueName )
rs:Format "EXCEL"
Report Server localhost
Report Path ReportServer?/AdventureWorks Sample Reports/Sales Reason Comparisons
Invocation Interactive

 

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

Property Setting
Name Sales Reason Comparisons
Caption "Sales Reason Comparisons for " + [Product].[Category].CurrentMember.Member_Caption + "..."
Caption is MDX? Checked
Description This action will launch a report comparing sales reasons for a given product category.
Action Type Report
Target Type AttributeMembers
Target [Product].[Product Category Name]
Condition  
Report Parameters
Parameter Name Parameter Value Expression
rp:ProductCategory UrlEscapeFragment( [Product].[Category].CurrentMember.UniqueName )
rv:ParamMode "Hidden"
Report Server localhost
Report Path sites/bi/Reports/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/sites/bi/Reports/Report Library/AdventureWorks Sample Reports/Sales Reason Comparisons.rdl
Invocation Interactive

  

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.

Property Setting
Name DAX Query Rowset
Caption "Reseller Sales In " + [Sales Territory].[Sales Territory Region].CurrentMember.Name + " Sales Territory..."
Caption is MDX? Checked
Description This action will display a rowset with the Reseller Sales for this Sales Territory Region
Action Type Rowset
Target Type AttributeMembers
Target [Sales Territory].[Sales Territory Region]
Condition  
Expression

"
Evaluate(
 CalculateTable(
  'Reseller Sales'
  ,'Sales Territory'[Sales Territory Id] = "
+ {Existing [Sales Territory].[Sales Territory Id].[Sales Territory Id].Members}.Item(0).Name
+ "
 )
)
"

Invocation

 Interactive


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:

Last edited Sep 8 at 9:19 PM by furmangg, version 25

Comments

furmangg Mar 5 at 11:36 AM 
Boxta, I answered your question here: https://bidshelper.codeplex.com/discussions/538017

boxta Mar 4 at 5:59 PM 
Is it possible to script the changes out as an alter xmla scirpt? Our production tabular cubes have automatic partitioning created, so everyday ETL would create a new partition in cube, which means the setup doesn't match the project anymore. So we do all of our deploy via BISM normalizer now, using XMLA scripts. But BISM normalizer doesn't seem to see the new action as new changes. Anyway BIDS helper can help script this out? Thanks!