About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

Wednesday, January 11, 2012

SSRS: Saving User Settings from the Report

Sometimes, different report's parameters default values are needed for each users. Here is a way, each user can save their default settings for the next time they run an SSRS report, from the report itself.

We will need to save the user's settings to a database table.
Table create query:

SETQUOTED_IDENTIFIER ON
GO

SET
ANSI_PADDING ON
GO

CREATE
TABLE [dbo].[UserSettings](
[UserSettingSQN] [int] IDENTITY(1,1) NOT NULL,
[UserID] [varchar] (50) NOT NULL,
[ReportName] [varchar] (50) NOT NULL,
[ParameterName] [varchar](50) NULL,[ParameterValue] [varchar](max) NULL,
[LastUpdateDate] [datetime] NOT NULL,
CONSTRAINT [PK_UserSettingSQN] PRIMARY KEY CLUSTERED
(   [UserSettingSQN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_UserSettings] UNIQUE NONCLUSTERED
([UserID] ASC,
[ReportName] ASC,
[ParameterName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)ON [PRIMARY]
GO
SET
ANSI_PADDING OFF
GO

The fields:
  • UserID: to store the user info that they used to access the report. I currently store both the computer and username.
  • ReportName: The name of the report
  • ParameterName: Parameter that is used in the report that is to be saved.
  • ParameterValue: Value the user set to save.

An example of the data:

 The Report:

In the Reports, I've created 3 additional parameters, to be used for saving the user's default parameter settings:

  • UserID: I use the SSRS's built-in field, User!UserID, to get the ID of the user running the report. Set the visibility to be internal.
  • ReportName: Again, I use the SSRS's built-in field, Globals!ReportName, to get the name of the report as it is stored on the report server database. Set the visibility to be internal.
  • Save: A boolean field that is visible to the user. I have the default set to "False", so when the user wants to change his/her settings they can change it to true when they run the report.



So when they run the report, I have the stored procedure check to see if the boolean was set to 1 -- if so then save with update/insert.

IF@Save = 1
BEGIN
UPDATE [ScientificArray].dbo.UserSettings WITH (SERIALIZABLE) SET ParameterValue = @StringNames
WHERE UserID = @UserID AND ReportName = @ReportName AND ParameterName = 'InverterString'
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO [ScientificArray].dbo.UserSettings (UserID, ReportName, ParameterName, ParameterValue, LastUpdateDate) VALUES (@UserID, @ReportName, 'InverterString', @StringNames, GETDATE())
END
END


12 comments:

David said...

How do you load the parameter defaults from the table into SSRS as the report is loaded?

William Andrus said...

In the parameters, default values section you have the option to specify values or get values from a query.

You select "Get values from a query", which will ask you to select the Dataset and Value field.

In this case I have the dataset setup to query the table based on the userid and set the default values.

Unknown said...


Hey i am my very first comment on your site. ,I have been reading your blog for a while and thought I would completely pop in and drop a friendly note. . It is great stuff indeed. I also wanted to ask..is there a way to subscribe to your site via email?

ASC Coding

Unknown said...

It's great. I want to ask you one thing. I want to insert one button called Save Report in SSRS parameter area (ex. below view report button) and save report parameters on that button click instead of view report. How is it possible?

Unknown said...

Thanks so much for posting this. How would you format the proc if you were needing to save a multi value parameter? For example, I need to save a parameter that has a drop down list with a select all option. Thanks

William Andrus said...

It's been awhile, but looking at the image of the values stored in the database (row #6), I was saving the values as a comma separated value.

I was probably using a table value function that converts the values into table. I have used a function such as:
http://andrusdevelopment.blogspot.com/2010/11/sending-multivalue-from-ssrs-to-stored.html

Liane said...

Hello, thanks very much for this great suggestion! I'm attempting to implement this and getting stuck with the query to determine the default values. It works well enough when I'm just selecting a row based on the ParameterName, but when I try to limit the query based on @UserID I am told that "forward dependencies are not valid" (which I suppose makes sense before the report is even run).

I know it's been a while, but do you happen to know how you worked around this issue?

Guy said...

Move the Parameter up (using arrows) on VS.

Liane said...

That did it, thanks very much!

brain said...

marvy! just what I was looking for

Anonymous said...

Hope I am not too late. How did you get a stored proc to run when you refreshed the report?

Luke said...

Anonymous said...
"Hope I am not too late. How did you get a stored proc to run when you refreshed the report?"

Just create a new dataset, set the query type to 'Stored Procedure'.
It doesn't have to return fields.
It will run when 'View Report' is clicked.

Cheers,

Luke