Archive for August, 2011

One of the tricks to improve the MDX query is to use “OLE DB – Microsoft OLE DB Provider for Analysis Services 10.0” as the data source instead of “Microsoft SQL Server Analysis Services – .NET Framework Data Provider for Microsoft Analysis Services” for querying data from a cube into SSRS reports.

When a parameter is added, I get following error:

An error occurred during local report processing.
An error has occurred during report processing.
Query execution failed for dataset ‘DataSet1’.
The following system error occurred: The parameter is incorrect..
Error Code = 0x80070057, External Code = 0x00000000:.

More details:




Read Full Post »

Say the @Company parameter in my report has a list of 200 companies. When “Select All” is chosen, I want it listed on my report header as “All” not the whole 200 companies.

Answer to that is you need to compare the total number of parameters in the dataset to the count of selected parameters.

IIF(Parameters!Company.Count = Count(Fields!Level_1.Value, “Company”) + 1, “All”, Join(Parameters!Company.Label, “, “) )

Total number of parameters in the Company dataset is Count(Fields!Level_1.Value, “Company”) + 1

Notice that I added 1 to the total count. This is because there is an item named “All” in the @Company parameter.

The count of selected parameters is Parameters!Company.Count.

Join(Parameters!Company.Label, “, “) is used to list the items selected if the “Select All” is not selected.

Read Full Post »

Solution: Check your dataset for null values. If it contains a null value, but your report parameter is set to not allow nulls, RS will revert to not selecting any values by default.

The data source to the cube has been set to use OLEDB with the following dataset for a list of Level 3 for the report parameter. The AND [Class Values].[Level 3].CurrentMember.LEVEL_NUMBER <> NULL was added in the FILTER to remove NULL values.


,FILTER([Class Values].[Level 3].MEMBERS, [Class Values].[Level 3].CurrentMember.MEMBER_CAPTION <> “N/A” AND [Measures].[Events Count] > 0 AND [Class Values].[Level 3].CurrentMember.LEVEL_NUMBER <> NULL)

Read Full Post »

I never had this issue in the past with SSRS 2005 and 2008.

The following blog explains it all:


The answer from the blog:

The first thing I would check is to make sure that your normal login is mapped to a role with the correct system-level permissions. The item-level role definitions don’t make a difference for the “Report Builder” button.

From the browser-based report manager interface:

  • Click “site settings”
  • In the Security section, click “Configure system-level role definitions”
  • Click the Role that you want to have this ability (e.g. “System Administrator” and “System User” are the default roles, but I believe that you can create your own if you want to).
  • Make sure that the “Execute Report Definitions” task is checked/selected. This is the permission that controls whether or not the “Report Builder” button is displayed.
  • Click “OK” and then return to the “Site Settings” page.
  • In the Security section, click “Configure site-wide security”
  • Click “New Role Assignment” and then map your login (or an AD group to which your login belongs might be even better) to the system-level role you edited in the previous steps.

The forms are pretty straightforward, and I’d guess that your login just isn’t mapped to the proper system-level role since you can see the button with the local administrator login. If that doesn’t work, you might check your IIS security settings for the report service to make sure that they’re configured to use windows authentication (assuming that’s what you’re using in the first place).



Read Full Post »

Recently, I was tasked to resolve a Report Server URL issue that my client had. The report server was configured to use both http and https. Clicking on the http one defaulted it to use https.

My first attempt to this is to remove the https link and leave the http link as it is. This time to my surprise, I received an error – unable to connect to remote server. This error misled me to check firewall rules. So ok, I create a new inbound rule to allow sqlserver.exe on the Windows Firewall of the server. I was still unable to connect to the report server.. hmm.. strange!

So 2 attempts have failed me! I then went in to check the rsreportserver.config file located at:

C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer

Open the rsreportserver.config file located at the following in NotePad. Change the SecureConnectionLevel value to zero. It was set to 2 previously.

<Add Key=”SecureConnectionLevel” Value=”0″/>

Both http and https links for report server are now working. 🙂

There is no need to check RSWebApplication.config file as this is now obsolete from SSRS 2008.

Read Full Post »

Whenever you deploy a report manually (i.e. using “Upload Files” functionality within the Report Manager), you will always have to repoint its data source. With SSRS 2008R2, you now have shared datasets. So when you deploy any reports that use shared datasets, make sure that you repoint all the datasets used in the report. Also make sure that you hit “Apply” button on each attempt (which is annyoing when you have a few to do).


Read Full Post »

My MDX report produced the following error when navigating to page 2. I could view page 1 but not from page 2 onwareds. Generating a PDF is ok.

An internal error occurred on the report server (rsInternalError)

The error produced by the Report Server does not tell much. Checked SSRS log files, used SQL Profiler to trace, and tried a few others such as reducing the amount of the subreports. All these attempts failed.

Removing one of the subreports from the main report fixed the issue. This is because this particular subreport has main dataset which returns no data, hence no fields returned in the dataset. Is this considered a bug within SSRS?

Another point to note is you will get this error if one of the measures has zero corresponding column because exactly one should exist. So use the CASE statement in your calculated measure. For example:

WITH MEMBER [Measures].[This Year Target Value] AS

CASE WHEN [Measures].[Target Value] = NULL THEN 0 ELSE [Measures].[Target Value] END

Also if the above does not work, check the data used to bring back the report parameters. Has there been any data entry errors? For example in the org chat that has 4 levels and these levels are mandatory fields, make sure that these fields are not null.

It took me 2 days to find out what’s wrong with my report.. phew!

31/08/2011 – This error haunted me back again and this time it took me half day to resolve!  This time I discovered that by removing the page breaks from the tablix, charts and rectangulars resolves this issue, despite all of the above mentioned fixes. The SSRS log file mentioned about “pagination” error which prompted me to remove the page breaks. Is this just my lucky day after spending half day figuring out what the heck?! 😛 I admit this is a tough one!

11/09/2011 – This error came back to haunt me again.. geezz.. this time I had to remove any hidden objects from the report (by setting the visibility to “show”)  in order to get rid of this bug. I wonder if this is a bug that I should report to Microsoft?


Read Full Post »

I have this scenario where the target value for each injury type is set against a date instead of a year. For example, if the target value for Medical Treatment Injury (MTI) injury type is 15 with the effective date of 01 Jan 2010, I want this target value to appear from Jan 2010 till Dec 2010.

Answer to that is to define a custom calculated member against the target measure  for the current member of the year (see example below):

WITH MEMBER [Measures].[Yearly Target Value] AS ( [Date].[Year].CurrentMember, [Measures].[Target Value])

Read Full Post »

%d bloggers like this: