SSRS Blog

Exporting SSRS Report with Excel Formulas Preserved

ssrs — Tue, 15 Dec 2009 22:37:47 GMT

Ahh Microsoft Excel, the key tool of every financial analyst across the world. Where would we be without you? What application would receive all of the notoriety for analysts mistakenly leaving in sensitive data in a hidden column? How would an analyst store tons of data that is a pain to mine?

As part of our conversion of some of our legacy Excel "systems" we have had to still produce Excel Reports. These reports have the strict requirement that when they are exported to Excel, they should still have the formulas so Analysts can do some scenario analysis. Lets ignore the analysis portion and get into the good old-fashioned coding part.

First, go ahead and read this article from SSRS books:

http://msdn.microsoft.com/en-us/library/ms159220%28SQL.90%29.aspx

Ok, did you read it? Was your question already answered? Awesome! Did you skip it and just want the answer here? Fine fine....

If you want the expression to export into Excel (so the user can play with the Excel spreadsheet and see values update dynamically) - you will need to set the field value to something like this:

=ReportItems!txtBox1.Value + ReportItems!txtBoxTwo.Value

See? that was fairly easy.

Some gotchas:

1) Putting in a lot of formulas WILL make your report run slower/take much longer to render

2) Make sure the cells are properly formatted (text, numeric, etc) before exporting as Excel can get confused.

3) Always make sure your SSRS Server is patched and up-to-date!

Steps to Resolve SSRS Timeout Issues

ssrs — Fri, 30 Oct 2009 21:29:57 GMT

Been awhile since I had posted anything and what better way to restart this blog than with some Timeout Issues. Now this list is designed for SSRS 2005, but you can put a few of these tips to use in 2008. Consider these steps in the below order.

1) Set the HttpRuntime ExecutionTimeout value to a greater value (ie 3 hours) on the Report Server

How do you do this? Simple!

a.       Open the ReportServer’s Web.config file by going to Start -> Administrative Tools -> Internet Information Services.
b.      From there, expand Web Sites -> Default Web Site, and click on ReportServer. On the right-hand pane, right-click ‘Web.Config’ and select ‘Open’.
c.       Locate the HttpRuntime parameter. If it doesn’t exist, you will have to create it within the <System.Web> section.
d.      Set the executionTimeout value to ‘10800’ (3 hours) as shown below:

<system.web>
<httpRuntime executionTimeout = "10800" />
</system.web>

2) Increase the script timeout on the report server

   1. Go onto your Reporting Server and open up Internet Information Services; right-click on the ReportServer and select Properties.
   2. Go to the Options tab, and set the ASP Script timeout to 300 seconds as shown below.

3) Set the report to never time out on the server

   1. Open your web browser on the server and go to http://localhost/Reports
   2. Navigate to the Report location and click on the problem report(s).
   3. On the left-hand pane, click on Properties.
   4. In the ‘Report Execution Timeout’ click the ‘Do not timeout report execution’.

Handling Multi-Value Parameter with a Stored Procedure

ssrs — Mon, 14 Sep 2009 19:49:56 GMT

In my experience with SSRS, I have noticed that there are two types of report developers:

1) SQL Developer that naturally flowed into SSRS, uses Stored Procedures for database development and has a very solid background for development.

2) Developers from other categories that somehow fell into SSRS.

I come from the second school but I have an endless desire for learning about technology and have been fortunate enough to work with some really intelligent developers.

Which brings me to one of the more common issues in SSRS - Passing multi-value parameter into a stored procedure.
Munish Batel does an excellent job of explaining the issue here:
http://munishbansal.wordpress.com/2008/12/29/passing-multi-value-parameter-in-stored-procedure-ssrs-report/

However, I am going to go into an even crazier amount of detail and provide some tips and things to remember.

1) Know your stored procedure. When you are getting into the realm of passing of multi-value parameter into a stored procedure, make sure you know the total size of the parameter in your SP as well as the total number of choices you can have in the multi-value (MV) parameter.

For example - we had a developer setup a MV parameter that could have up to 10,000 items checked (!!!) in the list. In the stored procedure, the parameter definition was varchar(50). You can see where this would go wrong.

2) If you are using the solution posted above, it is important you understand you will need to code a UDF function to split up the parameter - Split in VB land. This URL does a good job of explaining that as well:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

I am partial to this function:

-- 7998 bytes max (but faster)
   CREATE FUNCTION dbo.udf_Split (@param varchar(7998)) RETURNS TABLE AS
   RETURN(SELECT substring(',' + @param + ',', Number + 1,
                    charindex(',', ',' + @param + ',', Number + 1) - Number - 1)
                 AS Value
          FROM   Numbers
          WHERE  Number <= len(',' + @param + ',') - 1


            AND  substring(',' + @param + ',', Number, 1) = ',')