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

SETANSI_PADDING ON
GO

CREATETABLE [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
SETANSI_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