Home > Reporting Services, SSRS > Update Tables with Reporting Services – T-SQL Tuesday #005

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

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.


  • 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
declare @flag as

declare @company_id as

set @company_id =
(select a.company_id

    from dbo.all_companies a

join dbo.all_projects b on a.company_id = b.company_id and b.curr_rec_flg =

    where b.id = @id)

set @exists =
> 0 then

    from dbo.user_table

    where id = @id)

if @exists =

set @flag =
(select exclude_from_comparison_flag

    from dbo.user_table

    where id = @id)

update dbo.user_table

set exclude_from_comparison_flag =
when @flag =


where id = @id

if @exists =

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.

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: