<img height="1" width="1" style="display:none" src="https://www.facebook.com/tr?id=931099456970389&amp;ev=PageView&amp;noscript=1">

Webinar Follow-Up: Dynamic SSRS Reports Using T-SQL & MDX

Posted Jul 24, 2014 by Liz Hamilton

Presenter: David Siska

Click here to watch the full webinar.

Click here to view David's blog.

 

SSRS Training Session Questions

We got a couple of questions during the Dynamic SSRS Reports presentation that I didn't have time to address during our typical end of sessions Q/A time. Those questions and answers to them are below.
Q: What if we wanted to capture the total amount of time it took to run the report... 3 seconds, 3 minutes, etc..

A: We can definitely get this kind of information from the SSRS Execution Log.
If we query the Microsoft supported ExecutionLog3 we can see the start and end times of the report, as well as the milliseconds used for each step of the report processing.
As we can see in this example, the most recent report executed from the cache, saving us quite a bit of time for data retrieval and processing time. We can also see that the report did not take much time to process from a live execution.

Q:  For a subscription report, what if I'd like to change parameters based on to whom the report is distributed? If to Joe, show Kansas data, but if to Tim show Nebraska data?

A: You need a data driven report (which is not available in standard edition of SQL Server)
You will set this up in Report Manager using subscriptions, and the design of the report will create the options to pass in parameters.
In step three of the subscription setup process you have the option to enter a query. You can test and run this query in SSMS first and you can certainly make a more complex query than the example used here.

Step 5 of this process is to define your parameter values. Since this report has only one parameter, the year, I selected the Year column from my previous query. Joining to a table that holds user report preferences or other data that might indicate which parameters should be used for their report (e.g. a sales territory) is a great way to make use of SSRS data driven subscriptions!
Note that while the prompt states the value is coming from the database, the available values are defined in your supplied query.

Q: Is it true that when upgrading from 2008 to 2012 that report subscriptions must be recreated manually?

A: I'm of the opinion that if it can go wrong it will, so backups and documentation are great things to have prior to a significant operation such as a version upgrade.
There are several tools and considerations for the Reporting Services portion of a version upgrade, and I'd suggest you refer to the MSDN documentation, which is far more detailed than my answer could be here.

Q: Hi, I alwasy have a problem with cascading parameters, where they are date types, so Param 1 says, Period No. and if they choose '1' then Param 2 (Date From) and Param 3 (Date To) is set (this works) but then chaning Param 1 to another period, the 2 cascading date parameters do not get updated, this is in any version, thanks

A: Great question, this is a "By Design" aspect of SSRS and cascading parameters and can happen with other data types too, depending on the nature of your data sets. There are several workarounds that have already been proposed by the community and you can find the initial issue report and those workarounds described here.
Boyan Penev has a longer explanation of the issue and a workaround on his blog.

Tagged With: SSRS, MDX, T-SQL


Blog Comments

Need help with this topic? Ask the author below.

 

Subscribe to Blog Updates