SSRS: Problems Linking To SharePoint Lists When Running in Sharepoint-Integrated Mode

This blog post documents a problem linking to List Items in SharePoint from a SSRS Report Running on a SharePoint integrated-mode server.

NOTE: There is currently no solution to this problem. However there is a quick and relatively painless work-around (see below)

Prerequisites;

• SQL Server Reporting Services 2008R2 with SP1 server running in Integrated Mode (with SharePoint 2007)
• A SharePoint website containing at least one list to use as a source for reporting
• Report Builder 3.0 for creating/ testing the report

Duplicating The Issue
Create a new report (using the "Blank report" option in the Wizard);

Report Builder 3: New Report (using Wizard)

Click on the "Click to add title" box and enter the text TEST. Right-click the text box and select "Text Box Properties" and then go down to "Action" in the list on the left;

Report Builder 3: Text Box Properties

Select "Go to URL" and then click on the Expression (fx) elipse;

Report Builder 3: Expression Dialog

Enter the URL to directly link to an item in the list adding in the current value of ID from the report as the parameter. For the example list I'm using this is;

="http://xxx/Reports%20List/DispForm.aspx?ID=4"

This will mean when you click on the link it will take you directly to the corresponding item. Click "OK" twice to get back to the main report and then click "Run" on the ribbon;

Report Builder 3: Report Preview

Click on "TEST" and it will open the URL in Internet Explorer (for example My Tasks);

Internet Explorer: Working Link

The important point to note is the URL which is;

Internet Explorer URL Bar

http://xxx/Reports%20List/DispForm.aspx?ID=4

The "4" is the ID for the "My Tasks" item (shown above).

This shows the report is working correctly when run from within Report Builder.

Save the Report to the Integrated-Mode Reporting Services server and re-run it;

Test Reporting Running from SharePoint Server

I've highlighted the same item we clicked on previously, click on it again;

Incorrect Data Displayed

Different data. Incorrect data. If you check the URL it has become;

http://xxx/Reports%20List/DispForm.aspx?ID=4&ID=4[1]

You'll notice that the ID variable on the end of the URL has been duplicated. This is no-where in the code that causes the link not to work. If you add the URL we are building as a displayed field you will find that it is displayed correctly (with a single ID) - something in SSRS seems to be causing this duplication to be introduced in the renderer/viewer rather than in the code of the report.

Workaround
If, rather than building the URL as described above, you use the expression;

="javascript:void(window.open('http://xxx/Reports%20LIVE/Forms/EditForm.aspx?ID=" & CStr(Fields!ID.Value) & "', '_self'))"

This solves the problem by "hiding" the URL in the expression so it doesn't get incorrectly updated.

(Thanks to Klaus Sobel from Microsoft Support for providing me with this work-around!)

References

1. The error seems very specific to SharePoint URL's. For example if you change the URL to "http://www.google.co.uk/?ID=" then the problem (duplication of parameters) does not appear. Equally if you were to remove the ?ID= then you don't get the problem. If you then add in the “?ID=” a bit at a time you get; Adding "?" gives http://xxx/Reports%20LIVE/Forms/EditForm.aspx?4. Adding "?ID" gives http://xxx/Reports%20LIVE/Forms/EditForm.aspx?ID4. Adding "?ID=" gives http://xxx/Reports%20LIVE/Forms/EditForm.aspx?ID=83&ID=4. This suggests some amount of parsing is going on somewhere, and the parsing is incorrect.