Archive

Archive for April, 2010

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.