When trying to create actions with SSAS I really struggled to find any information or easy “how to’s” to show me how they were done.
So… here is an attempt from me to explain how to do them, to remind myself in the future if nothing else!
So I created an SSRS report that is published to my reports server and it takes 3 parameters, TimeCalendar, BrokerGroup and the other is called TransactionTypeTransactionTypeSummary. All 3 have a data type of text.
Here is the Query Designer view of my MDX query, notice to create the parameters I just put a check in the Parametr box of the Dimension in the top of the dialog.
Here are the properties of the parameters. Notice I have selected the Internal radio button at the bottom so that the parameter is hidden from view when we ultimately review the report. When trouble shooting it may be prudent to have this set to visible.
Having setup my report layout I have not got to configure an SSAS action to call the report and pass the parameters for execution.
On the Actions tab in my cube designer I have created an action called Group GWP By Insurer. The target type is Cells, target object All cells.
The condition field specifies when an action should be availables. In my case it should only be available when the end user is looking at UK Combined GWP by TransactionTypeSummary by Group by Year.
The report server section specifies the Report Server name, the path to the report and the report format. Notice the leading / on the report path and also the ?.
After that comes the parameter specification. So the Parameter Name must be the parameter name as in the report, this is very important. If you parameter names do not match then your action will simply not appear in you cube browser later. Actually this is true for all action related problems. If you make a mistake it can be very difficult to debug these. The thing to do is build it up bit at a time. I always leave my conditions until last, because that is where I encounter most of my struggles.
Parameter value was also something I struggled with at first. It’s important to use the UrlEscapeFragment() function this allows you to specify the parameter value with have to escape character all the /’s. Then I use the MEMBERTOSTR() function to parse my member to text so that it matches the datatype expected by the report parameter.
Just to smarten things up a bit I also include some additional properties to help the user see what report they are about to run.
Then assuming all the planets are aligned and you’ve not made any mistakes then the action should appear in Excel or other cube browser as follows:
Once the user clicks that action then a web-browser should fire and aslong as the permissions are set correctly on the report server then they should be able to view their report without any problems.
SSAS reporting actions are a powerful way of extending your cubes functionality. Happy reporting!