Archive

Posts Tagged ‘SQL Server’

Work around for the out of box Reporting Services navigation

March 30, 2010 Leave a comment

Problem: The folder structure can be intimidating for users to remember which report they need.

Solution: For our main reporting site we had 3 levels of reporting and wanted it easy to move between the reports without users getting lost in the folder structure.  The solution we came up with was to use the SQL Server tables on the report server to assist to identify the reports the current user ran and what parameters were used for the last time the main report and project summary report.

Reporting Structure – we put all of the reports in one folder with a linked version of the Main report at the root of the reporting site.  We still use multiple folders for additional reports that are need by the business that aren’t included in the main reporting.

  • Level 1 – Main Report –this has several parameters and links to the Project Summary Report
  • Level 2 – Project Summary –high level project summary with links to multiple detailed reports under it.  There are multiple versions of this report depending on the product.
  • Level 3 – Project Details – multiple detail reports

Logos – We also had a requirement to display our client logos on the Level 2 and Level 3 report and I decided to make that interactive by taking the user back to the Main Report using the initial parameters when clicked.  To accomplish this we did the following:

  1. Store the logos with a common naming convention on the reporting server.
  2. Create a parameter in the report where get the client_name from our database.
  3. On the report we put the logo in the header of the report with the following string in the “Value” parameter: “file:///N:/FileShare/Logos/” + Parameters!client_name.Value + “.gif”

Navigation – To accomplish the navigation I use the following tables in the reporting server database:

  • reportserver.dbo.catalog
  • reportserver.dbo.executionlog

Naming Convention – We have lots of reports that aren’t part of this main reporting navigation so we came up with a naming convention for the Project Summary to assist with finding the project summary reports and the associated parameters from the last report that was run.

Datasets – the following datasets are added to the Level 2 & 3 reports

dsMainReport

Select *

from (

SELECT path, name, parameters, convert(varchar(200),’http://reportservername/ReportServer/Pages/ReportViewer.aspx?/MainReport Name&’) + convert(varchar(2000),parameters) +convert(varchar(200),’&rs:Command=Render’) report_link,

row_number () over (order by timestart desc) row

FROM reportserver.dbo.catalog a

join reportserver.dbo.executionlog b

on b.reportid = a.itemid

where username = @username

and Name in (‘Main Report Name’)

) a

where row = 1

dsPrevious Report

Select *

from (

SELECT path, name, parameters, convert(varchar(200),’http://reportservername /ReportServer/Pages/ReportViewer.aspx?’) + convert(varchar(200),path) +  ‘&’ + convert(varchar(2000),parameters) +convert(varchar(200),’&rs:Command=Render’) report_link,

row_number () over (order by timestart desc) row

FROM reportserver.dbo.catalog a

join reportserver.dbo.executionlog b

on b.reportid = a.itemid

where username = @username

and name like ‘Naming Convention for the level 2 report%’

) a

where row = 1

Links in the Reports

  1. 1. First Link goes on the logo in the header

Right click on the image and then click “Image Properties…”

Put the following in the “Select URL:” box

=First(Fields!report_link.Value, “dsMainReport”)

  1. 2. 2.Second Link for the Level 3 detail report

I created a text box below the image with an action.

Right click on the text box and then click “Text Box Properties …”

Put the following in the “Select URL:” box

=First(Fields!report_link.Value, “dsPreviousReport”)