SSAS Reporting Actions

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.

QueryDesigner

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.

ParamProperties

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.

ActionTab1

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.

ActionTab2

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 ?.

ActionTab3

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.

ActionTab4

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.

ActionTab5

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:

ExcelActions

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!

SSAS 2012 and 3 SSDT Quirks I’ve Encountered

Just a quick post here about some quirks in SSDT for SSAS 2012.

I’m reasonably new to the whole BI thing and so I’m really learning as I go with SSAS at the moment. I’ve tinkered with it in the past but I’m now our company’s data analyst so I’m working with it on a daily basis.

I did play with SSAS in SQL Server 2008 and found the cube browser in BIDS to be quite good. You drop things on columns and rows, filters were easy to set and you could really analyse the data right in BIDS. Obviously this is not how my end users analyse the cubes I construct but hey I was learning back then, I had no idea really what BI was about.

So… 4 years later I take a job as a data analyst. This is some way away from my more familiar role as a DBA. So… I quickly learn that the tool most commonly used to analyse data in cubes is Excel. Brilliant! A tool I’m familiar with! So, I’m running SQL Server 2012 BI Edition on a nice shiny new server, I’ve built a data warehouse/mart, and I’ve got my first cube. Things are going well, until I get to the cube browser in SSDT.

Quirk #1 – The cube browser

The cube browser is different! I no longer have the ability to drop things on columns and rows! I can’t do any analysis straight in SSDT.

CubeBrowser

Quirk #2 – The format strings

What’s even more annoying is that I’ve set a format string on my measure and it’s not displaying in the cube browser. Oh well, that’s ok I’ll hit the Excel icon and take this cube right into Excel.

Quirk #3 – The whole reason for writing this blog entry to be honest…

I’m not sure if this happened in previous versions but the cube still wasn’t showing my measure like the format string I’d specified. A lot of googling led me to a post (which I’ve since lost, so apologies to the author for not crediting him here) that stated you need to escape the £ signs in the format string if you want them to appear in Excel.

FormatString

So having applied that I’ve gone back into Excel and refreshed my data connection! And…

Excel

Hey presto! £ signs appearing.

I hope this helps someone out because #3 had me going for hours!