SQL Server Reporting Services “interal error occurred”

If you work with SQL Server Reporting Services (SSRS) 2005 for any length of time, you’ll see a number of ambiguous error messages.  I recently ran into the error below and found the solution quite easy.  Maybe you’ll encounter this one day and I’ll save you some headache.

An error occurred during local report processing.

The definition of the report ‘/Main’ is invalid.

An internal error occurred on the report server. See the error log for more details.

Non-queried multi-value parameters in SQL Server Reporting Services

As a follow-up to my previous post on Easy multi-value parameters in SQL Server Reporting Services, I wanted to point out some information on non-queried, or static multi-value parameters.

For my example, I’m using a parameter named “Time” with choices “Morning”, “Noon”, and “Night”.  The values are M, N, and I respectively.  You can see how I set up the parameter in the following screenshot.

You can accomplish having all the checkboxes selected by default in two ways.  The first way is to pass an object array of values, and the easiest way to do that is to use the Split command to split a string into a string array.  I used =Split(“M,N,I”,”,”) as my default.  I had to provide the second parameter for Split, the delimiter, because the default is space.

The second, and easier method, is to just add a line item for each item you want checked, as demonstrated in this screenshot:

Here is the resulting parameter list, showing all the items selected (same for either method):

Sometimes, you need to display the parameters of your report, perhaps so it could be regenerated later.  You’ll need to print out that object array, so use the opposite of Split, Join.  Below is a textbox with an expression of =Join(Parameters!Time.Label, “, “), which joins the parameter values together with a comma and space separator.

Here is the result of the code, with two parameters selected shown:

If you only put the parameter label in the textbox (i.e. using code =Parameters!Time.Label) then when you display the report, you’ll get the famous Reporting Services #Error message.  You have to use Join in this case.

More on SharePoint “User not found” error with Reporting Services

Following up my initial post on the SharePoint “User not found error with Reporting Services in integrated mode, I wanted to post another reason for the “User not found” error.

If, somewhere in the site author tree, you have user accounts that no longer exist, you will encounter the “User not found” error while viewing the report and browsing the list of shared data sources.

I found two good blog articles about this issue as well as a solution here:

• Wherefore Art Thou Author?
• Resetting the Author on a SharePoint site, or Wherefore Art Thou Author Redux

I modified the code a bit to just show the site author without resetting it.  I also changed the code to explicitly dispose of the SharePoint objects.

The utility takes two parameters, the first is the site URL.  If you only pass one parameter, it looks up the site author.  If you pass a second parameter (in the format of DOMAIN\USER), the site author will be reset to the value you supply.

(more…)

Sorting via parameters in SQL Server Reporting Services

In SQL Server Reporting Services (SSRS) there are two different methods for a user to sort their reports after they have been generated. Other folks have done a superb job of explaining each method, so I’ll just point you to their sites:

The user can interactively sort on a header textbox by just setting the “interactive sort” properties:
http://weblogs.sqlteam.com/joew/archive/2008/08/02/60666.aspx

You can also sort using standard report parameters:
http://www.databasejournal.com/features/mssql/article.php/10894_3492236