Archive

Posts Tagged ‘Reporting Services’

Update Tables with Reporting Services – T-SQL Tuesday #005

April 9, 2010 Leave a comment

Here’s my first blog for #TSQL2sday.

Summary: This is in an example of using reporting services for something other than just displaying data. By creating an action on a report that calls itself you can pass values to hidden parameters and then use those parameters in stored procedures that are called from a dataset to insert/update data.

Problem: In our situation there were a couple of people who need to update data in a user table and we didn’t want the users to write SQL to update and insert records. I have since implemented this concept in other reports.

Solution:

  • I built a report with the detail records that I wanted to review and update
  • I did an outer join from the main table to my user table, where I store the keys and an exclude flag.
  • In the report I put an action on the field that has the flag. In the action I call the same report with the current parameters as well as the id for the current row that you would like to set the flag. The hidden id parameter is defaulted to 0 and is set when you click the action.

  • I created a dataset in the report that calls a stored procedure passing the id parameter.
if @id >0 exec dbo.insert_update_exclude @id

 

set @id = 0

 

  • The stored procedure looks to see if a row already exists in my user table if it does it performs an update and switches the value and if it doesn’t exist than it inserts a row into the table. Below is the insert update logic from the stored procedure:
declare @exists as
varchar(1)
declare @flag as
varchar(1)

declare @company_id as
int

set @company_id =
(select a.company_id

    from dbo.all_companies a

    inner
join dbo.all_projects b on a.company_id = b.company_id and b.curr_rec_flg =
‘Y’

    where b.id = @id)

set @exists =
(select
case
when
count(id)
> 0 then
‘Y’
else
‘N’
end

    from dbo.user_table

    where id = @id)

if @exists =
‘Y’

set @flag =
(select exclude_from_comparison_flag

    from dbo.user_table

    where id = @id)

update dbo.user_table

set exclude_from_comparison_flag =
case
when @flag =
‘Y’
then

else
‘Y’
end

where id = @id

if @exists =
‘N’

insert
into dbo.user_table
(company_id, id, exclude_from_comparison_flag)

values(@company_id, @id,‘Y’)

 

  • In the report I color code the rows based on the exclude flag so I can easily read the report.
    • BackgroundColor property: =iif(Fields!Exclude.Value = “Y”,“SandyBrown”,“Transparent”)
  • Below is an example of how the report looks in BIDS, in my production report I have more descriptions that help me identify what I want to exclude.

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”)