Tag Archives: drillthrough

Reporting Actions – Its All About the Nutmeg !

Actions in Analysis Services are a fantastic way to bring the bling to your cube without overcomplicating the basic user interface.

Analysis Services supports several different types of actions however the scope of the following discussion will be on Reporting Actions and to a lesser extent Drillthrough Actions.

The classic case for a Reporting Action is where your cube rolls up to the day level, however you want to query at more detailed level to the cube’s granularity, going down to the line level.   The Reporting Action allows the user to investigate a lower level of detail by clicking the Reporting Action which will provide the additional data via a Reporting Services Report.   The Report will accept 1 or more parameters from the cube Action, query a relational (or multi-dimensional) dataset and return the results in the Reporting Services report.

There are several references available for creating a New Reporting Action.   The key point to get across here is if any of the fields used to define the Reporting Action are not correct, the action will not appear at the OLAP client.  Also make sure you give the Action a Caption by setting the Additional Properties section of the Action definition.  For the server name and path using similar naming to that of the Adventureworks solution will work i.e.

Server Name – needs only to be set to localhost (if SSRS and SSAS are on the same server) or the server’s actual name

Report Path – should look something like this –

ReportServer?/Folder Name/Report Name

Note the full path to the report is preceded by a / (following the ? character) and does not require the .rdl extension.

Caption – Before you get too excited with parameters, makes sure you go to the Additional Properties section and give the Action a caption.  This is the wording the user will see when they right click on the cube member and select Additional Actions.

Parameters – Make sure the Action parameters are correct otherwise this can cause the Action to effectively “break” which means it won’t appear on the OLAP client.  Using an expression such as

 UrlEscapeFragment( [Dimension].[Attribute].CurrentMember.Name ) 

is an ideal choice for passing a parameter to a relational dataset, whereas

 UrlEscapeFragment( [Dimension].[Attribute].CurrentMember.UniqueName ) 

is an ideal parameter expression for passing to a multidimensional dataset.

Some other examples here …

Bret Stateham Reporting Actions

Benny Austin Reporting Actions

The above examples work well for native mode installation of Reporting Services – hence be sure to read this integrated mode example if relevant.

Integrated Mode Samples

Test your Reporting Services action by deploying the Actions you have made using the Deployment wizard.

Benny Deploys Reporting Action

What to do when Actions don’t work ?  As outlined above, make sure you have a caption, the parameter expression is correctly formed and the Report Server and Report Path have been correctly defined.   This is reiterated here on MSDN.

IMPORTANT: It is important to note that the MDX expressions used in Conditional Expressions, Parameter Values and Caption must evaluate to valid values. If either one of them does not return a valid result, the Report Action will disappear when the cube is browsed. No errors are thrown, but the Report action just does not show up.”

Good luck and happy Action!