Changing SharePoint Execution Timeout

In SharePoint, the default time allowed to export reports to different formats (Excel, PDF, etc) is 2 mins –and to me that’s already a lot of time for a report to render-, but there are some unfortunate cases where you need to render large amounts of information and therefore, you need to ensure that the SSRS reports are actually able to export.

To do so, you need to make sure that the execution timeout is increased to not have the process fail.

Since SharePoint is a web platform, we need to include a parameter in the web.config for IIS to increase the timeout.

  1. Open IIS Manager on servers that have Reporting Services installed.
  2. Select the Site on Port 80.
  3. Right Click the site and select Explore.
  4. Edit the web.config file in Notepad.
  5. Search for the first occurrence of “httpRuntime” located under the system.web.

    ssrs_sharepoint_execution1
  6.  Add “executionTimeout=”1200”” to the httpRuntime entry located in the above image. (Value is in seconds, 1200 is 20 minutes)

    ssrs_sharepoint_execution2
  7.  Save the file and Close the file.
  8. Close the Windows explorer window.
  9. Restart IIS.
  10. Close IIS Manager.

Passing Multi-value Parameters to a MDX Query in SSRS

Let’s say our report needed ProductCategory as a parameter.  The ProductCategoryKey in the relational database for Bikes is 1, and our cube member for Bikes is [Product].[Category].&[1].  So we can transform our parameter value to pass into our mdx query like so:

“[Product].[Category].&[”+Cstr(Parameters!ProductCategory.Value) + “]”

Simple right?

BUT, I needed to be able to pass multiple values, which meant I had to take some extra steps before I could parse my string.  If ProductCategory is a multi-value parameter, then Parameters!ProductCategory.Value is an object – an array of values.  So we need to create a single string out of that array, format that string, and then break it up again back into an array since that is what the MDX query is expecting.  Whew!

Here’s the magic:

Split(“[Product].[Category].&["+Replace(Join(Parameters!ProductCategory.Value,"],”)+”]”,”,”,”,[Product].[Category].&["),",")

Cool eh? but, what’s happening here?

Join() is a handy little function that takes our array and joins the values into a string.  We can delimit the values with a string, in this case “],”, like so:

Join(Parameters!ProductCategory.Value, “],”)

Bikes, Components, and Clothing for our parameter values, our string after using the join function will be“1],2],3″.  We can then add an ending bracket to the end via simple string concatenation so now we have “1],2],3]”.

Applying the Replace() function, we replace “,” with “,[Product].[Category].&[“ and now we have “1],[Product].[Category].&[2],[Product].[Category].&[3]“.  Finish with one final string concatenation in the front and we have “[Product].[Category].&1],[Product].[Category].&[2],[Product].[Category].&[3]“

Unfortunately we can’t just pass this into our MDX query.  It’s expecting an array object, not a string!  But luckily, the Split() function is the counterpart to Join() and breaks up our string into that array object we want via the commas as a delimiter.