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.

Wrox Professional Microsoft Sharepoint 2007 reporting with SQL sever 2008 reporting services review

I'am currently working on a Microsoft Sharepoint business intelligence project.

To prepare for the project I read the book Wrox Professional Microsoft Sharepoint 2007 reporting with SQL sever 2008 reporting services.

What a disappointment that was! Do not read or buy the book, if you are a tiny bit familiar with reporting and deploying. I really thought it would go in-depth, combining Microsoft Sharepoint and business intelligence. Talking about structure, function, enc. But that’s not the case.

The first 9 chapters is about creating reports and deploying. Chapter 10 is about some overrated and complex AJAX solution to deploy and display your reports. Chapter 11 is about gauges. Very short and obvious.

Off course everybody needs a basic understanding and every organizational needs are different, but there is an certain essential that is missed.

Deployment is very imported, but not worth 3 chapters. Especially not when your pretty new to this, and that’s the books audience. You want to know how to create a fast, efficient and good solution for the end-users.

Summarizing:
If you want to know more about deployment and care less about how the reports are shown and interact, this book is for you (although is pretty expensive for what you get). If not, please find another book. There are much better books out there.

http://www.wrox.com/WileyCDA/WroxTitle/Professional-Microsoft-SharePoint-2007-Reporting-with-SQL-Server-2008-Reporting-Services.productCd-0470481897.html



Professional Microsoft SharePoint 2007 Reporting with SQL Server 2008 Reporting Services
Coskun Cavusoglu, Jacob J. Sanford, Reza Alirezaei
ISBN: 978-0-470-48189-9
Paperback
480 pages
September 2009

Wabit: The Ultimate BI Reporting Tool for Mac

After seeing the Microsoft video earlier to day, and knowing that it Microsoft has some pretty good BI-products, I was wondering if there where any Apple based BI-products. Maybe only as reporting, query or ETL based. So i went online and found my first toy: Wabit. I looks fun, and I’am going to test it tomorrow. I will post a review later this week about

Wabit

Wabit is an elegant and easy to use Business Intelligence Reporting tool. It offers database and business users the ability to perform Ad-Hoc Queries and generate Standard Reports.

The Wabit is designed to be the most intuitive and simplest BI Reporting tool on the market. It includes creative features such as a drag-&-drop playpen, live result-set updates, global searching, and WYSIWYG report formatting.

The Wabit also makes Standard Reporting easy. With a single mouse click, query results are instantly transformed into elegant reports that can be further customized with fonts, colours, sections, breaks, headers, footers, logos, and more.

It offers technical and business users the ability to:

• Drag-&-Drop source database tables into a playpen
• Use the database definitions to automatically formulate required joins
• Intuitively refine your queries with an “evolving” result set that updates instantly and continuously
• Many more features

http://www.macupdate.com/info.php/id/30052/wabit

http://www.sqlpower.ca/page/wabit

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.