Creating an SSRS Report with a Dynamic Datasource

I’ve been asked this question a few times recently and so thought I’d put it on the blog as a “how to”.

This is going to be a basic report only to demonstrate the concepts and steps required in order to create a report that has a dynamic datasource. The data that this report returns isn’t going to be particularly useful but it serves as a nice demo regardless.

So… first things first start a report project and create a new report.

NewReport

Create a datasource (not a shared datasource).

DataSource

Create a dataset that uses the datasource you’ve just created.

DataSet

Create a very simple report.

SimpleReport

Preview the report to ensure that it’s working.

Preview1

Now, to make it dynamic I’m going to add a server name parameter.

ParamProp1

ParamProp2

Having done this I need to alter the data source connection string to make use of the new parameter.

DataSourceExprButton

DataSourceExpr

Click OK and then OK on the datasource properties box. Then when you preview your report again you have a dropdown list containing the two servernames. Select the server you want to run the report against and you should see that you get the server name and database count from each one.

Preview2

Preview3

As I said this is a very simple example of a report you can run using a dynamic datasource but it demonstrates the steps required. The most common example of where this sort of functionality can be useful is creating custom dashboards for server estates. You could even have a database table that contains all your server names, and alter the properties of the servername parameter to get its list of servers from that table. The possibilities are endless!

Happy reporting!

Advertisements

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!