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.


Create a datasource (not a shared datasource).


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


Create a very simple report.


Preview the report to ensure that it’s working.


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



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



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.



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!

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.


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!

SSRS: Stopping Users Running High Resource Consuming Reports

Your server has ground to halt, your monitoring software has identified a long running query, other users are reporting that things are running slowly, you determine a user report being run through SSRS has been running for a long time and is consuming far more resources than it should be. How can we stop users running reports that take a long time to complete and force a timeout??

Well, assuming you’ve done all the usual code improvements, index reviews etc. there is at least one other option….

Through SSMS connect to your Report Services instance as shown below:


Then go to the reporting services properties:


Go to the execution tab and then select “limit report execution to the following number of seconds” and specify a value:


It really is as simple as that. SQL Server BOL says:

Limit report execution to the following number of seconds

Set a time constraint on report execution. The time period starts when the report is requested. If the time period ends before the report is fully processed, the report server cancels the process and any in-process queries to external data sources.

The link to that item in BOL can be found here: . I dare say there are other ways of achieving the same effect but this seems to me be trivial enough to not worry about searching for an alternative at the moment.