Browsed by
Month: December 2010

Deploying SSRS reports larger than 4 mb

Deploying SSRS reports larger than 4 mb

When you have an SSRS report with a file size larger or close to 4 megabytes that you want to upload to the report server, you might get the following error:

System.Web.Services.Protocols.SoapException: There was an exception running the extensions specified in the config file. —> System.Web.HttpException: Maximum request length exceeded.

This has to do with the limit on the report server that by default, it only allows files up to 4 megabytes. To increase the allowed file size, you have to modify the web.config of your report server.

Go to the following directory where the web.config is located:

(Reporting Services folder)Reporting ServicesReportServer

Notice that the folder “ReportManager” also has a web.config. This is not the correct file to edit though, you need to open the web.config in the ReportServer folder.

Open the file with Notepad and search for the following line:

httpRuntime executionTimeout=”9000″

 

 

Add this to this tag:

maxRequestLength=”8096″

In case your report is really big and more than 8 mb, you have to change the number to a desired level. This number is the amount in kilobytes.

Save the file and restart the SQL Reporting Service in the Windows Services.

Now you should be able to deploy the report to the report server!

Matrix with “other” column

Matrix with “other” column

In my earlier blog post “Group small values into an ‘other’ category in a Reporting Services chart“, I talked about how to group small values together into a chart under one category called “other”. Currently I am in the process of setting up a similar idea for a matrix. I have generalized the business case as much as possible to make it easy to understand.

In the case, I have several departments where I want to report costs figures. These are the following departments:
  1. Sales
  2. Consulting
  3. Support
  4. HR
  5. Development
In the matrix below, you can see that I have the departments in the columns and the countries in the rows:
The layout of this matrix is quite simple, it uses a column group for the department grouping, and a separate adjacent column group for the totals. I have added this column group for the totals myself:
To make the matrix more readable, I would like to consolidate the Consulting, Development and HR department into a group called “Other”. The matrix would then look something like this:
These are the steps that I’ve taken to accomplish this. It doesn’t look like the most dynamic way since you’re dealing with hard coded department names in an expression, but when these things are set in stone, it is a nice option to use.
1. In the group properties of the column group that groups the departments, I have set a filter to the departments that I would like to have the detailed figures (the departments displayed with the blue column labels):
2. I have added an adjacent column group in between the two existing groups, and I group these on the field “continent”. All these values have the continent Europe, so this setting should make sure that only one column is displayed. So a prerequisite of this method is that you have at least one column where every value is the same!
3. You might think that you have to set the filter in the group properties of this newly added group to the other departments that you want to show. Unfortunately this doesn’t work because you have grouped on a different field. In this case you have to modify the expression in the data part of the column to make this work. Instead of the Sum(Fields!amount.Value), the expression changes to the following:
=Sum(
Switch(Fields!department.Value = “Dev”, Fields!amount.Value,
Fields!department.Value = “Consulting”, Fields!amount.Value,
Fields!department.Value = “HR”, Fields!amount.Value))
Basically what it does here is for every record, the amount field is evaluated in the Switch function and is included in the Sum when it corresponds with the three conditions in the Switch function.
The final result looks like this: