You can give somebody a hyperlink to get to a Reporting Services report and that’s all they need if there are no parameters or if the default values are what they want. But how do you specify values for parameters? It turns out to be pretty simple but the documentation is a little vague on some of the details.
We have a Sales League report where you can look at a Sales team’s figures and see how the team members are doing. In Report Manager the user has to choose their team and run the report. I was asked to supply links on the intranet home page for each team to go straight to their figures. It turns out all I had to do was grab the URL (NB: the ReportServer URL, not the Report Manager URL) and add the parameters as name value pairs but there are a few things that the documentation doesn’t make clear:
- The name is the internal name, not the display name. From the example given it could have been either.
- The value is the actual Value value, not the Label value (ie. again, it’s the internal value not the display value.)
- You only need to supply name-value pairs for the parameter(s) you want to change. Our report has four parameters and I only needed to change the third one, so I just added the name-value for that one and the rest stuck with their default values.
I just looked into a poorly performing stored procedure and brought it down from over 4 minutes to under 4 seconds by making one simple change: converting a table variable into a temporary table. What’s interesting is that there were only about 20,000 rows of about 1,100 bytes each in the table variable, which would be nothing in a permanent table. So what happened? I got stuck in and this is what I found.
The stored proc is for a sales report where the user can choose from a list of things to group the results by. The relevant sales are selected into a table variable with an empty column Group1, the approriate column is copied into Group1, and then there’s a SELECT with a whole lot of aggregations GROUPed BY Group1. The query plan showed 99% of the cost was in the main insert into the table variable. Further reading revealed that no statistics are created on a table variable and so to the Query Optimizer it always has a cardinality of 1, whereas statistics are created on temporary tables. Was this the problem?
I changed it to use a temporary table instead, ran that, and compared the query plans and the output from the Profiler, including the events Auto Stats (under Performance) and SP:StmtStarting & SP:StmtCompleted (under Stored Procedures). The query plans for populating the temporary table and table variable were identical but the temporary table approach showed statistics being created on the temporary table before the final SELECT, and the SELECT itself was parallelized. The SP:StmtStarting and SP:StmtCompleted events in the Profiler confirmed that it was the final SELECT that took nearly all the time with a table variable.
Interestingly the query plan showed the final SELECT was 1% of the total cost with a table variable and 23% with a temporary table. It just shows, you have to be careful about looking at the steps in the plan and zooming in on the step that has the largest share of the total cost. I could have gone blind looking at the first step without ever improving it.
I thought it was all too good to be true. We upgraded our reporting server in place from 2005 Std to 2008 R2 Std and we’ve hit a major problem. We have a few reports that are about 60 pages. On 2005 the report would render page 1 of 60 pretty quickly. Now it produces page 1 of 2 that is miles long and takes minutes to render. If you have enough RAM and CPU power in your PC to cope. My dev machine can handle it but the average user’s machine just dies. There’s a brief spike on the server and then it’s all your PC that’s doing the work. Seeing as I’m the only person who can run these reports at the moment, guess what I’ve been spending half my time doing?
If you encounter the same problem, please vote this bug report up, or if you know the answer, please let me know. Thanks.
On my dev machine I installed R2 alongside my existing 2005 instance and it did a funny thing with the new instance’s collation. Our servers are all on Latin1_General_CI_AS, including my 2005 dev instance. But my R2 instance defaulted to SQL_Latin1_General_CI_AS and I’ve only just noticed, which is a damn nuisance. I thought I checked the collation during the setup but I guess I did a mental LIKE ‘%Latin1_General_CI_AS’. I think this article (http://msdn.microsoft.com/en-us/library/ms143508.aspx
) explains the reasoning:
SQL Server Collations (SQL_*)
Select this option to match settings with English-language versions SQL Server 2005 or earlier versions.
Presumably it detected the 2005 instance and was trying to be helpful. Close but no cigar. Just as well my machine is long overdue for a rebuild anyway.