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.

NULLIF() to prevent divide by zero errors in SQL

We encountered problems trying to dividing tru zero. We tried to select a duration in day's and the amount of visits, to calculate the avarage staying periode. When a customer stays shorter than one day, 0 is registerd, resulting in a 0 divided by 0, which obvious results in error.

i.e.

Duration = 5

Amount = 0

Select (5/0) gives an error.

We could write an case statement to prevent this, but there is an easier way: NULLIF!

NULLIF() takes two arguments and returns NULL, if the two values are the same. This can be used to turn the divisor from a zero into a NULL, which will force the entire equation to become NULL.

Duration = 5

Amount = 0

SELECT

( Duration / NULLIF( 0, Amount ) ) = ( 5 / NULLIF( 0, 0 ) )

This statement returns NULL and can be very helpful when working with aggregate or calculated values.

Informatica 9 Released

Integration platform includes common metadata core, tools for business end users and IT collaboration, SOA-based data services
Information Management Online, November 10, 2009

Enterprise software provider Informatica today released the latest version of its integration software platform, Informatica 9.

Informatica 9 builds on previous releases that included a common platform and version control that allows teams to work across time zones. The 9 release layers on some browser-based tools and a common metadata layer that allows standardizing policies and data quality rules.

The vendor breaks the release into six categories: enterprise data integration, data quality, B2B data exchange, application information lifecycle management, complex event processing and cloud computing data integration.

The message however, centers around three themes intended to push data integration from the back office to the front: SOA-based data services, "pervasive" data quality and business/IT collaboration.

Ash Kulkarni, VP of product management at Informatica, says companies need to think in terms of a data layer in infrastructure that isolates underlying systems from the applications that use them. "When we went down the path of SOA, we were all thinking about applications, but at the end of the day it is fundamentally about exchanging data," he says. "It needs to be your data at the right time over any protocol, Web service, SQL, batch, whatever." With a core metadata layer, Informatica says companies can use universal data discovery services to profile and investigate information and policy-based data services to manage governance and user access to sensitive data.

The second theme, pervasive data quality, revolves around building data quality rules once and applying them across all domains. This enables all stakeholders to participate in the process of driving data quality across the organization through browser-based tools for business analysts and data stewards that can provide tighter collaboration with IT.

The use of these tools is really the premise to the third theme of the release, business-IT collaboration. "You want to get away from the old process of requests to IT and designs and modifications and instead work on exactly the same thing from different perspectives through an underlying metadata layer," says according to Chris Boorman, chief marketing officer at Informatica. In this way, he says, the specification becomes the design of an integration request.

The new platform gives Informatica more exposure to the business side during the sales cycle. Forrester Research analyst Rob Karel says this is likely to become a trend among data quality and master data management vendors that recognize that IT-only projects usually have a low return and a higher risk of failure. "Just getting the business engaged as part of the process of defining and rolling out these data quality capabilities is really the key to success," Karel says. "That's why governance is a hot topic right now."

Nonetheless, Informatica 9 is a technology solution to a business problem, and the heavy lifting of organizational culture and change management is in getting business users to assume more responsibility and accountability for data quality. Analysts agree that placing collaborative tools into the right hands is a step in the right direction, but does not resolve weak and slow adoption of governance practices.

IDC research vice president Carl Olofson says the more likely it is that the business understands exposure to poor data quality, the more likely it will embrace and become involved in projects to improve performance. "The actual operations still need to be in the hand of a data professional, but when an executive can look at and understand the present state of play and exposure, that's important."

Mark Smith, EVP at Ventana Research worked his way through the details of Informatica 9 and says what he found is a clear step in the right direction. "The capabilities are pretty simple and straightforward, and for those analysts who want to do some data modeling and go beyond data quality, it works well." Early customer involvement, going back to alpha testing in May, helped this result, Smith said.

But Smith adds that the key challenge will be to get analysts to work together, and more collaboratively to further the goals of data governance. "Bad habits don't die easily."

Given this state, Karel says it was a smart idea for Informatica to build the product before widespread demand arrives. "There is a lot of IT demand to push the business to take more accountability but our research finds data governance maturity extremely low on the business side," he says. "I think it's a good thing that they are providing tools to help internal evangelists make it happen a little better."

Boorman hopes the old back-office view of Informatica is fading and that data quality is a fundamental area where his company can engage more actively with the business. "This is all about timely, trustworthy and relevant data for the business." The company plans an introduction and demonstration of the software for today, and Boorman says that more than 11,000 people have registered for the event.

http://www.information-management.com/news/data_integration_soa_service_oriented_architecture-10016496-1.html