0845 643 64 63

Report Parameter Selection Memory

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)
      --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)
         (@UserID, @Report, @Setting, @Value)

     --Return the new setting
      SELECT @Value AS Val

  CREATE PROC spr_GetUserSetting
         @UserID varchar(100),
         @Report varchar(100),
         @Setting varchar(100),
         @Default varchar(max)=NULL
      DECLARE @Val varchar(max)
      SET @Val = ISNULL((SELECT Val FROM tblUserSetting
            WHERE UserID=@UserID
            AND Report=@Report
            AND Setting=@Setting

     SELECT @Val AS Val

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

      DECLARE @Val varchar(max)
      SET @Val = ISNULL((SELECT Val FROM tblUserSetting
            WHERE UserID=@UserID
            AND Report=@Report
            AND Setting=@Setting

     SELECT Val FROM dbo.Split(',', @Val)

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…

Hope this is of some use!


16 comments on “Report Parameter Selection Memory

  1. Anthony on said:

    Thank you so much! This worked like a charm! 🙂

  2. admin on said:

    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!

  3. Ada on said:


    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

  4. Alex on said:

    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.


  5. Christian on said:

    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?

  6. Alex on said:

    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.

  7. Brian Hayes on said:

    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?

  8. 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

  9. 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

    cast(val as datetime)

  10. Looks like I was over-complicating matters somewhat!

    Thanks for the tip…

  11. Reddy on said:

    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.

  12. Alex on said:

    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

    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

  13. sunil shrikhande on said:

    It will work……. it will check for null/empty and give result


    (ObjProdOff.BranchID IN (@BranchID)OR (select count(*) from Branches where BranchID IN (@BranchID)) = 0)


  14. Mr Mark on said:

    Why not just use SSRS Linked Reports?

  15. 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.


  16. Mr Mark on said:

    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.

Power BI Sentinel
The Frog Blog

Team Purple Frog specialise in designing and implementing Microsoft Data Analytics solutions, including Data Warehouses, Cubes, SQL Server, SSIS, ADF, SSAS, Power BI, MDX, DAX, Machine Learning and more.

This is a collection of thoughts, ramblings and ideas that we think would be useful to share.


Alex Whittles
Jeet Kainth
Jon Fletcher
Nick Edwards
Joe Billingham
Lewis Prince
Reiss McSporran
Microsoft Gold Partner

Data Platform MVP

Power BI Sentinel
Frog Blog Out