Suppress Auto Update of MDX Parameter Datasets in Reporting Services 2008

With the MDX Query Designer in SSRS 2008 Report Designer and Report Builder 2.0, changing the main dataset overwrites the parameter datasets, which will kill any manual adjustments you’ve made. Bummer.
The hack to fix is to add a SuppressAutoUpdate =true to the parameter dataset. Right click the report and choose View Code. Find the dataset, and in the relevant node pertaining to your dataset, modify it. Then Save.

<Query>

<DataSourceName>DataSource1</DataSourceName>

<CommandText> …

<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>

<rd:Hidden>false</rd:Hidden>

</Query>

I found this solution here:
Suppress Auto Update of MDX Parameter Datasets in Reporting Services 2008

SSRS 2008 deployed on sharepoint BI gives a drilldown error (rsItemNotFound)

We created various reports with drilldown functionality on SSRS. This worked great and we assumed it would work the same way on Sharepoint.

So we deployed the reports, but we discovered after some testing that the drill down functionality didn't work anymore. It displayed the error: The item 'http://oursharepoint.nl/reports/reportslibrary/report1' cannot be found. (rsItemNotFound)

While researching this error, we came to the conclusion that default the underlaying report is accessed without extension. SSRS knows which report need to be called, automatically filling in the RDL extension. Sharepoint, on the other hand doesn't know . There are multiple types of reports and therefor it doesn't automatically knows which extension it needs to use.

Summarizing: SSRS just needs the name, Sarepoint expects an name and extension.

So we could build the report in such manner that it works with sharepoint, but then i wouldn't work with SSRS anymore. So we need another way. To fix this, try the following:

• Open the report in Visual Studio

• Go to the link which provides the drilldown

• Open the properties

• Go to actions

• Edit the expression, which calls the report

• Finally add the following expression: IIF(Globals!ReportServerUrl Like "*http*", "report1.rdl", "report1")

Using the Globals!ReportServerUrl we can determine if the url is a http protocol or not. If so, its called by Sharepoint and we need the extension (report1.rdl), if not, it's called by Visual Studio and we don't need the extension (report1).

Now the reports opens if its called in Sharepoint or in SSRS.

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/002ccd0b-a850-4c5f-8904-38ac5bcbe5c3/

Update:

The error is caused when the underlaying report is called using an expression. If you point straight to a report without expression, it works fine on Sharepoint or SSRS. But when you use an expression to determine which report needs to open, it needs the extension and the workaround above still solves the problem.

SSRS: Alternate colors for grouped rows issue

I came across a bit of a problem. I want to use alternate colors for rows grouped by department. I found three methods to realize this:

1. Using a piece of code determining if the row is even or not and based on that piece of code setting the background color. (http://olsv.blogspot.com/2009/08/alternating-background-colors-for-odd.html)

2. The second one is using the following expression in the background properties: =iif(RowNumber(Nothing) Mod 2, "Green", "White"). (http://msdn.microsoft.com/en-us/library/ms157328.aspx)

3. The last one is the same as the previous one, only the expression is different: =IIf( RunningValue (Fields!FieldName, CountDistinct, Nothing) MOD 2, Color1, Color2). (http://weblogs.sqlteam.com/joew/archive/2008/08/20/60691.aspx)

All methods work fine and the rows even and odd get alternate colors grouped by department....but… here is the problem: The report has an calculationfield. I sort my tablix on that field. The ordering is correct, but it colors get mixed up. They are set on the non-ordered set.

So I understand that SSRS first sets de background colors and then sorts the tablix, which leaves me with a bit of a problem.

I will post the fix when I find the solution.