This post explains a method of enhancing Reporting Services reports to make them more user friendly. I have been quizzed many times by end users who want reports to remember the parameter selections so that next time they run the report they haven’t got to re-select the same parameters. This is especially useful if there are a lot of long parameter lists, and each user tends to run the same selection frequently.
This process is made possible by the User!UserID global variable within SSRS. It contains the domain and username of the user running the report (DOMAIN\USER).
There are a number of stages required to get this to work :-
– New SQL table to store users’ selections
– New stored procedures to save and load selections
– Two new datasets within the report
Firstly create a database table to store the selections
CREATE TABLE tblUserSetting(
ID int IDENTITY(1,1) NOT NULL,
UserID varchar(100) NULL,
Report varchar(100) NULL,
Setting varchar(100) NULL,
Val varchar(max) NULL
)
Then add a couple of stored procedures, one to save the default and one to retrieve it.
CREATE PROC spu_SetUserSetting
(
@UserID varchar(100),
@Report varchar(100),
@Setting varchar(100),
@Value varchar(max)
)
AS
BEGIN
--Firstly delete any old settings for this parameter
DELETE FROM tblUserSetting
WHERE UserID=@UserID
AND Report=@Report
AND Setting=@Setting
--Now add the new setting
INSERT INTO tblUserSetting
(UserID, Report, Setting, Val)
VALUES
(@UserID, @Report, @Setting, @Value)
--Return the new setting
SELECT @Value AS Val
END
CREATE PROC spr_GetUserSetting
(
@UserID varchar(100),
@Report varchar(100),
@Setting varchar(100),
@Default varchar(max)=NULL
)
AS
BEGIN
DECLARE @Val varchar(max)
SET @Val = ISNULL((SELECT Val FROM tblUserSetting
WHERE UserID=@UserID
AND Report=@Report
AND Setting=@Setting
),ISNULL(@Default,''))
SELECT @Val AS Val
END
Now on to the report.
Lets say that we have a parameter called ‘TestParam’. The first thing we need to do is create a new dataset that will be used to retrieve the default value if one exists.
Name the new dataset dsTestParamDefault, the CommandType should be StoredProcedure, the query string should be spr_GetUserSetting.
Edit the Dataset and select the parameters tab. SSRS has already created report parameters for each of the proc’s parameters. We need to replace these with our own values.
@UserID =User!UserID
@Report =Globals!ReportName
@Setting ="TestParam"
@Default =Nothing
(if you want to you can specify a default value here for when the user runs the report for the first time)
Set the default value of your TestParam parameter to be the Val field of the dsTestParamDefault dataset.
The parameter will now load the default from the appropriate database record for that user.
Now we need to add the ability to save the users selection for next time.
Create a new dataset named dsTestParamSave, the CommandType should be StoredProcedure, the query string should be spu_SetUserSetting.
Edit the Dataset and select the parameters tab. SSRS has already created report parameters for each of the proc’s parameters. Again, we need to replace these with our own values.
@UserID =User!UserID
@Report =Globals!ReportName
@Setting ="TestParam"
@Value =Parameters!TestParam.Value
You will then need to delete the four report parameters SSRS created for you (UserID, Report, Setting and Default).
And that’s it! When the user first executes the report, it will ask them for the parameter value (or use the default if you specified one). However the next time they run it it will automatically remember what value they last used. As it is stored by username, it doesn’t matter whether they use the same PC or not.
The example above is for a simple single-valued parameter, however this technique can also be used for more complex parameters. The parameter could have a list of available values, and can even be multi-valued. If you want to use a multi-valued parameter, then you only need to make a couple of minor changes…
The spr_GetUserSetting will need changing to
BEGIN
DECLARE @Val varchar(max)
SET @Val = ISNULL((SELECT Val FROM tblUserSetting
WHERE UserID=@UserID
AND Report=@Report
AND Setting=@Setting
),ISNULL(@Default,''))
SELECT Val FROM dbo.Split(',', @Val)
END
For this to work you will need to find yourself a suitable Split function. (Search Google for ‘SQL Server Split’ to find hundreds that are suitable, or I’ve put one in the linked sample file)Then change the @Value parameter of the dsTestParamSave dataset to
=Join(Parameters!TestParam.Value, ",")
This will store a comma delimited value containing all values selected. If you use commas in your data then choose a suitable alternative as a delimiter.
I have put some working examples of all of this in the link here along with copies of all relevant SQL script…
https://www.purplefrogsystems.com/Download/blog/ParameterMemory.zip
Hope this is of some use!
Alex
Thank you so much! This worked like a charm! 🙂
Glad it’s of some use… Since I first figured it out I’ve used it for every one of my customers and all users love it – it makes so much difference to the usability of a system.
Thanks for the feedback!
Hi,
I have a question : when the stored procedure that saves the user selection get called? I have problems that sometimes the latest selection is not saved.
thank you
Hi Ada
The stored proc that saves the settings is executed when the report data is generated, after the ‘view report’ button is pressed but before the report is visible.
The stored proc that loads the setting is executed as the list of available parameters is being loaded, when the page is first loaded.
The two procs should have to process in order due to the way they use the report parameters. The loading proc is needed in order to populate the parameters, then the saving proc uses that parameter value. This means that SSRS should always execute them in the correct order.
I’d advise simplifying your report by going back to basics and getting that to work, then gradually build up the complexity again so you can see at wich point it starts failing.
Regards
Alex
I am having trouble with one part of this, where you write:
“Set the default value of your TestParam parameter to be the Val field of the dsTestParamDefault dataset.”
I went into TestParam and set the default value to the following:
=First(Fields!Val.Value, “dsTestParamDefault”)
When I view the report, I get hte following error:
A Value expression used for the report parameter ‘TestParam’ refers to a field. Fields cannot be used in report parameter expressions.
Any idea what’s going on here?
Instead of using an expression as the default value there’s an option to use a dataset. Select dsTestParamDefault. Then in the next box it should give you a list of available fields. Select Val. The first value in the dataset will be used.
Let me know if you still have trouble.
Alex
Just wanted to say that this works very well; however I am having a hard time with Date Params, it’s getting stored correctly but the report fails on reading of the stored date.
“The Property ‘DefaultValue’ of report parameter ‘start_date’ doesn’t have the expected type.”
If I past the value however stored in the table in the report it works just fine.
Any thing I need to know for date parameters?
Hi Brian.
The chances are that your problem is to do with UK/US date format incompatabilities. Even if you set all options on the clients and server to the correct format, SSRS still randomly switches between US and your own locale as it sees fit.
By far your easiest option is to treat the parameter as a string not a date, however this effectively will just bypass the problem, not actually solving it. The downside of this is that you lose the date picker.
The next easiest solution is to just give in and use US settings, although most users won’t appreciate this one!
Aside from that, you need to get a little more complicated. I haven’t tried this but it should work… Create a hidden string parameter and use this as your loading parameter (using an unambiguous format such as dd mmm yyyy). Then set the default value of the DateTime parameter to be retrieved from this string parameter. It’s likely to get a little messy, and may involve some playing around to get to work, but it should be feasible.
Welcome to SSRS date hell!…
Hope ths helps, if not please let me know
Alex
Alex, thanks for the reply but I actually solved it with a relatively simple solution.
When I have a date picker object, I simply cast as datetime for that particular dataset.
For example:
In my report where my date set is getting the user param that is a date type. I do the follwing.
declare @tmp table(val varchar(30) )
insert into @tmp exec spr_GetUserSetting @UserID,@Report,’SelectedDate’,null
select
cast(val as datetime)
from
@tmp
Looks like I was over-complicating matters somewhat!
Thanks for the tip…
Alex,
How do I use this if I have more than 1 parameter. I have a report which has 3 or 4 parameters and one parameter is multi select. Can I do it using one stored procedure. Please advise.
Thx.
Hi Reddy
You can just repeat the process once for each parameter. I.e. have two datasets (a default, and a save) for each parameter you want to use. Just make sure you choose unique setting name for each parameter so they don’t get confused.
If you want two parameters (Param1 and Param2), you would end up with
Datasets:
dsParam1Default (call spr_GetUserSetting with “Param1” as the setting)
dsParam1Save (call spu_SetUserSetting passing “Param1” as the setting)
dsParam2Default (call spr_GetUserSetting with “Param2” as the setting)
dsParam2Save (call spu_SetUserSetting passing “Param2” as the setting)
Then set the default value of each paramter to the appropriate dataset.
I explain how to adapt this to cope with multi-valued parameters at the end of the post (after the red “and that’s it!”). Give that a go, if you get any problems with it please shout.
Hope this helps
Alex
It will work……. it will check for null/empty and give result
SELECT * FROM BRANCHES AS ObjProdOff
WHERE
(ObjProdOff.BranchID IN (@BranchID)OR (select count(*) from Branches where BranchID IN (@BranchID)) = 0)
thanx…
Why not just use SSRS Linked Reports?
Hi Mr Mark
Linked reports allow you to wrap an existing report in a new customised report, which could indeed be used by a user to fix the parameters to their desired selection, given one linked report per user.
However, what the code in this post does is take it a stage further and automates the parameter memory functionality. This code will make any report remember every parameter that every user selects, without them having to do anything whatsoever. The next time that user loads the report it will automatically retrieve their last selection of parameters.
Hope this explains the benefits of this approach more clearly.
Regards
Alex
I now see the difference and the benefit. It however seems like a lot of complexity to add to every report for something that be done with a single software change and a couple of clicks by each user. If you were to add something to the interface where users can select some params, click “Save to my Linked Reports” and give the linked params their own name, each user could then create as many “pre-programmed” reports as they want. Users could also share some linked reports amongst themselves.
Our intent for using SSRS is to have an easy way for almost anyone to develop reports (inside our business and for our customers). As such, we’re trying to keep reports as simple as possible to develop and the UI as automated as practical.