How to set multiple values as Default Values for a multi-valued parameter (SSRS)

In most of the cases when we deal with parameters in SSRS reports, we provide some default value to those parameter (which can be a textbox, a drop down list etc) so that when users browse the report first time, report shows results with that default value of the parameter. And after that user can choose different values from the parameter list or enter different value in case of textbox to get the desired results.

 

This feature of having default value(s) can also be implemented in case of multi-valued parameter list. As in case of multi-valued parameter, user can select multiple values from the drop down list by selecting multiple checkboxes. So if we need to provide some default selection of values from this drop down list, we can provide a list of these default values for this parameter. This can be done using a Dataset for default values & then selecting this dataset as the query input for the default value.

 

Let’s suppose we have a multi-valued parameter named “Color” for displaying products based upon the selected list of colors.

Dataset for this report parameter will give complete list of colors.

 

SELECT DISTINCT Color FROM DimProduct

 

Now we’ll create another dataset only for the default values i.e. to select some of the colors by default to show the initial results on the report. This can be done by selecting some of the desired values based upon some condition or we can just have the predefined hard coded values.

 

SELECT ‘Blue’ As D_Color

UNION All

SELECT ‘Black’ As D_Color

 

And in the parameter “Color”, we can map these as:

 

rdl_param

About these ads
This entry was posted in SQL Server, SSRS. Bookmark the permalink.

35 Responses to How to set multiple values as Default Values for a multi-valued parameter (SSRS)

  1. AlexAxe says:

    Hi there,
    Ugh, I liked! So clear and positively.

    Thank you
    AlexAxe

  2. Annie says:

    Pretty clear explanation, I was wondering if you know how to deal with multi-value parameters when the user does not wants to pick an option, we have the “Select All” option which will give as the facility to pick all, but as you should know the allow null property is not available to multi-value parameters.

    So my case is something like the following:

    I have a list of parameters let’ say:

    Id Label
    -1 blue
    -2 yellow
    -3 orange
    -4 None

    The None element was added to avoid the “Please select a value for the parameter…” error

    My problem is that when the user pick the box “Select All” all of the cells are picked even the one that has the “None” label which is very confusing for the user.

    The user might think ok if None option is selected am I going to process the rest of the values in the list or not?

    So I was wondering if you know a way to exclude the none option when the user picks the “Select all” check box.

    Thanks in advance.

  3. Hi Annie,

    Quickly looking at your problem, I can suggest you two options to accomplish it:
    1. Rather than marking it as ‘None’, make it ‘Default’ or so, so that when All is selected it does not confuse the user. OR
    2. Put a separate parameter for selecting ‘None’ / ‘Options’ from that. And based upon this selection only, you can populate the other drop down list. So if none is selected then other list would be empty…and no need to select anything. If ‘options’ is selected then you can provide the list of possible values in the other drop down list.

    I’ll try to come up with some straight forward solution for this later.

    Munish Bansal

  4. Savika says:

    Hi Munish,

    I think you provided a great explanation.

    However, I had a query that I’m not able to solve. I am using a multi-valued parameter and I display an expression on top of my table that displays all the values selected by the user.

    So lets say when the user selects black, blue and yellow, these labels are diaplyed on the screen. My problem is if the user does select all , then I want the expression to read ALL. Currently it lists all of the data contained in the multivalued parameter. Any suggestions?

  5. Hi Savika,
    You can use the following expression in your textbox(label) to display the selected colors with comma separated values. If all the colors are selected it will show as ‘ALL’ in the label.

    =IIF(Parameters!color.Count >=5, “ALL”, JOIN(Parameters!color.Value, “,”))

    Here 5 is the total number of color values available in the drop down list.

  6. Lorenzo says:

    Hi

    I have set up a report in exactly the same manner as you describe..but the Select All option is not available??

    The query for the dataset is :

    Select cost_center from user_stab where cost_center not in (‘!!user template!!’,’accounts only’, ”) order by cost_center;

    And I filter out certain entries that were there for testing does this affect the Select All

    Aplogies forgot to include the query

  7. Beverley says:

    Thanks for this! I did it and it works… until I try to change my first drop down.

    So I have something like this…..

    ParmColourFamilies
    – reds
    – blues
    – greens

    and it is single-select

    Next I have
    ParamIndividualColours (multi-select)
    – pink
    – salmon
    – rouge
    – cyan
    – etc etc etc

    When I select “reds” in the first box it selects all of the individual colours in the next box that are in the red family. Yay! But if I change my mind and select “blues” what I would want is for the values to be cleared and the individual shades of blue selected… but no, nothing happens.

    Any ideas?

  8. Hey Beverley,

    As far as your requirement is considered, it is a case of creating dependent parameters.
    So in your case, in the dataset for the second drop down list (having individual shades), pass selected value of the first parameter.
    If you use value of first parameter in the data set creation for the second drop down, it will automatically take the selected value from the first parameter to pass on it to the data set of second.

    Thanks,
    Munish Bansal

  9. Paul Brand says:

    Hi. Found your articlaes on multi-valued paramaters very helpful. Can I ask for your advice on a slightly different issue but in the same are.

    I have a multivalued paramater which pulls it’s list of values from a datset ins the SSRS report (called LocalACs) – it’s a list of all our worldwide accounting centres.

    The Parameter gives me a nice drop down list, with the Select All option at the top.

    But I want it to default to Select All – rather than the user having to pick it manually.

    I have tried putting the dataset in as a the default query, but that just returns the first individual data value from the list, or nothing at all.

    I have then tried embedding a JOIN(Parameters!LocalACs.value,”,”) expression in the Default (non queried)
    but that juist gives me a build error when I try to peview.

    Apart from manually setting up a string containing all the values (simulating the JOIN expression) – how do I do this. Must I create a special, dataset which union’s all the values? What if the number of Accountig centres changes?

    I feel sure there is a simple method. I am in SSRS 2005 – so it may b that the answer is “get SRSS2008″!

    Many thanks

    Paul
    Manchester UK

  10. Hanuman says:

    Munish
    I am working with ssrs & need urgent solution please.
    I Created two dataset with two stored procedure as it is a v. complecated report with lots of calculations.
    In one stored procedure i am using
    select “All” as emp_name , dept_code
    from table name
    where
    dept_code in (‘101′,’102′,’103′)
    and
    dept_code like
    case when @parameter=’101′ then ‘101’
    …etc.
    end
    Union
    select emp_name, dep_code from table name
    where
    dept_code in (‘101′,’102′,’103′)
    and
    dept_code like
    case when @parameter=’101′ then ‘101’
    …etc.
    end
    now in report i am using in dropdown
    department code :101
    102
    etc
    now when user will select 101 will get emp_name in another dropdown with all on top & name of the employees of that department.
    now i am using these names as a parameter in another dataset (stored procedure)
    like
    where emp_name in @reportparameter or ‘All ‘ in (@reportparameter)
    My problem is it is giving correct report for individual department employees , but ALL gives all the employees name not the individual all employee like i want all in 101 or all in 102 but it is giving me all in all the departmets . please help.

    …etc.

  11. sid says:

    i am writing ssrs report in this report i have different departments
    Say:Dept1
    Dept2
    Dept3
    Dept4
    I am using these as a parameter dropdown.
    Now when user clicks Dept1 in second dropdown employee of that department will come
    like:
    All
    Tom
    Jerry
    Herry
    similarly when user clicks on Dept2
    dropdown will look like:
    All
    Rita
    Sita
    Gita
    Now every thing works fine when i click one employee at a time it gives me correct report
    but when i click “All ” it gives me report for “All ” the employees in “All” the departments .
    I want “All” for each department .Like if i hit “All” for “Dept1″ then it should give me report
    only for “All” the employees in “Dept1″ which are
    Tom
    Jerry
    Herry
    similarly “All” in “Dept2″ should give me reports for “All” employess of “Dept2″
    which are:
    Rita
    Sita
    Gita

    please someone suggest how is this possible?
    Thanks!

  12. John M says:

    I’m responding to your response to Beverley:

    “As far as your requirement is considered, it is a case of creating dependent parameters.
    So in your case, in the dataset for the second drop down list (having individual shades), pass selected value of the first parameter. If you use value of first parameter in the data set creation for the second drop down, it will automatically take the selected value from the first parameter to pass on it to the data set of second.”

    This is what I’d expect, but I cannot get this concept to work properly. I have a report that is accessed by a SalesMgr, their SalesMgrID gets passed in via a hidden parameter. The SalesMgrID is used to create a multivalue parameter of all the SalesRepIDs under that manager. The manager can then pick and choose any or all SalesRepIDs. That multivalue parameter is in turn used to create another multivalue parameter of CustomerIDs, showing only the customers that are assigned to the sales reps chosen in the first multivalue parameter. The CustomerIDList does not refresh when the sales rep drop-down list is tabbed through after choices have been made. It always shows it’s default options. Am I missing something?

    Thanks,

    John

  13. John M says:

    Follow up to my last posting… I tested the issue that Sid reported just before my posting and it seems to hold true for me as well…

    SalesMgrID correctly populates SalesRepIDList and the CustomerIDList has all the customers for all the sales reps for that sales manager. If I select just one of the SalesRepIDs, the CustomerIDList is updated accordingly. But if I select more than one SalesRepIDs, the CustomerIDList is not refreshed (the Advanced Parameter Property can be set to Automatically Determine when to Refresh or Always Refresh, it does not matter, it does not work). Any ideas?

  14. Hi John, Sid

    I tries the same scenario, it worked absolutely fine for me.

    If I select 1 Deparment (Dept1) from the first parameter (multiselect), in the second drop down (multiselect) I get the corresponding list of employees. Also if I select ‘All’ departments, I get the whole list of employee across all the departments in the second list.
    And if I just select one department & ‘All’ of its employess, then also in the report I get only the selected department with all of its employees.

    I really could not produce the issue. Probably it’s something to do with the Reporting Services update/Service pack or so.
    If you ppl has SSRS 2008, I would suggest you all to try with that also.

    Hope issue will be resolved.

    Thanks.

  15. John M says:

    Thanks for your help, Munish. I got the parameter lists working, though the behavior is not ideal, the basic functionality is what I want. Now, I have another issue.

    Please consider this scenario:

    SalesMgrID#1 logs into the report and is presented with a multi-value drop-down list of all the SalesRepIDs under him (with his own ID in the list), so he sees SalesRepIDs 1, 2, and 6. The resulting CustomerID multi-value drop-down list contains all the customers for these three reps collectively.

    SalesRepID#1 contributes CustomerIDs=100033, 1203, 2001, 2101, 2103
    SalesRepID#2 contributes CustomerIDs=1, 2204, 2211, 2214
    SalesRepID#6 contributes CustomerIDs=1103, 2002, 3012

    So, the CustomerID drop-down list presented after any duplicates are dealt with and the list is reordered. It looks like this:

    1, 100033, 1103, 1203, 2001, 2002, 2101, 2103, 2204, 2211, 2214, 3012

    The sales manager can pick and choose from that list of customers. Let’s assume he’s selected all of them. The report should process and display the report with the correct breaks on data, like this:

    SalesMgrID#1Header

    SalesRepID#1Header
    CustomerID#100033
    InvoiceDetail…
    CustomerID#1203
    InvoiceDetail…
    CustomerID#2001
    InvoiceDetail…
    CustomerID#2101
    InvoiceDetail…
    CustomerID#2103
    InvoiceDetail…
    SalesRepID#1Total

    SalesRepID#2Header
    CustomerID#1
    InvoiceDetail…
    CustomerID#2204
    InvoiceDetail…
    CustomerID#2211
    InvoiceDetail…
    CustomerID#2214
    InvoiceDetail…
    SalesRepID#2Total

    SalesRep#6Header
    CustomerID#1103
    InvoiceDetail…
    CustomerID#2002
    InvoiceDetail…
    CustomerID#3012
    InvoiceDetail…
    SalesRepID#6Total

    Instead, though, when I run the report, I see this:

    SalesMgrID#1

    SalesRepID#1Header
    CustomerID#1
    InvoiceDetail…
    CustomerID#100033
    InvoiceDetail…
    CustomerID#1103
    InvoiceDetail…
    CustomerID#1203
    InvoiceDetail…
    CustomerID#2001
    InvoiceDetail…
    CustomerID#2002
    InvoiceDetail…
    CustomerID#2101
    InvoiceDetail…
    CustomerID#2103
    InvoiceDetail…
    CustomerID#2204
    InvoiceDetail…
    CustomerID#2211
    InvoiceDetail…
    CustomerID#2214
    InvoiceDetail…
    CustomerID#3012
    InvoiceDetail…
    SalesRepID#1Total

    SalesRepID#2Header
    CustomerID#1
    InvoiceDetail…
    CustomerID#100033
    InvoiceDetail…
    CustomerID#1103
    InvoiceDetail…
    CustomerID#1203
    InvoiceDetail…
    CustomerID#2001
    InvoiceDetail…
    CustomerID#2002
    InvoiceDetail…
    CustomerID#2101
    InvoiceDetail…
    CustomerID#2103
    InvoiceDetail…
    CustomerID#2204
    InvoiceDetail…
    CustomerID#2211
    InvoiceDetail…
    CustomerID#2214
    InvoiceDetail…
    CustomerID#3012
    InvoiceDetail…
    SalesRepID#2Total (same as SalesRepID#1)

    SalesRepID#6Header
    CustomerID#1
    InvoiceDetail…
    CustomerID#100033
    InvoiceDetail…
    CustomerID#1103
    InvoiceDetail…
    CustomerID#1203
    InvoiceDetail…
    CustomerID#2001
    InvoiceDetail…
    CustomerID#2002
    InvoiceDetail…
    CustomerID#2101
    InvoiceDetail…
    CustomerID#2103
    InvoiceDetail…
    CustomerID#2204
    InvoiceDetail…
    CustomerID#2211
    InvoiceDetail…
    CustomerID#2214
    InvoiceDetail…
    CustomerID#3012
    InvoiceDetail…
    SalesRepID#6Total (same as SalesRepID#1 and #2)

    I can’t get the data to group correctly, I’ve been staring at my dataset queries and tablix properties to see if there is a way to force breaks where I need them, but I cannot figure out where to turn… I’ve got a nagging feeling it’s somewhere in the dataset query, but I remain unsuccessful in my attempts. Do you have any ideas of what I should look at? Thanks!

    John

  16. John M says:

    Sorry, my tabs were removed when I posted, so the reporting outline is hard to read. I think you can figure it out though. =o)

  17. Hi John,

    I don’t know how you are getting the data for grouping the values based upon user’s selection in first & then the second parameters (multivalued).
    But if you are using a third Dataset for getting the values for table; passing the selected values of both the parameters, it should work absolutely fine.
    It means you are already grouping on a specific field (first parameter value), only you are filtering the dataset used for the table based upon the selected parameter values.

    Thanks,
    Munish Bansal

  18. Vidya says:

    Hi,

    I am also facing the issue John M outlined. The dependent parameters set ok the first time, but do not refresh when the parent parameter is changed.

    For eg; I have Date Presets: Current Month, Current Qtr, YTD. There are two parameters: StartDate and End Date that are supposed to populate based on the value selected in DatePresets.

    Works fine the first time. User selects Current Month, StartDate populates: 4/1 and End Date: 4/30.

    User changes Date Preset to Current Qtr… the date values do not change.

    I have been told its a SSRS bug. Is it possible its resolved in a later version? I am using VS2005.

    Thanks.

  19. Joanna says:

    Hi!

    many thanks, it was extremely useful!

  20. Thanks to you too, Joanna.

  21. Cor Slotboom says:

    Hi Munish.

    I’ve the same problem as Vidya. The first time my report is opened the defaults for the multivalue parameter (based on startdate (parm1) and enddate (parm2) show all the correct defaults (result of a stored procedure). When the startdate is changed it seems that the stored procedure for getting all the defaults is not triggerd, because all the . new possible selection are not selected.

    I’m also using the 2005-version.

    Is it realy a bug or is there a solution for this problem.

    Regards

  22. Hi Vidya, Cor Slotboom

    Yes, you are right this is a known issue/limitation in Reporting Services which does not allow automatic selection of default values in the dependent parameter on refresh.
    It’s already registered there with Microsoft Connect:

    https://connect.microsoft.com/SQLServer/feedback/details/268032/default-does-not-get-refreshed-for-cascading-parameters?wa=wsignin1.0” rel=”nofollow”

    Will update you if I find some workaround for the same.

    Thanks,
    Munish Bansal

  23. And, the only workaround I could think of to this problem is to Invalidate the second parameter list of values based upon the first parameter’s selection.
    Since in case, population of its values (2nd one) is not dependent upon the other parameter, only the default values are, it’s not able to refresh it once first parameter values changed.

    And in fact, in your case, you can make second parameter list of values dependent upon first one. e.g. if quarter is selected in first, second should retrun the list of dates for current quarter only; using some logic in query. By this way each time it (2nd) would be refreshed & then default values would also get refreshed accordingly.

    Hope this helps..!

    Thanks,
    Munish Bansal

  24. Anand Vanam says:

    I have gone through your Article about Dynamic Column Mapping in SSIS.

    I will get 10 Flat files every day. I have 10 database tables whose schema is according to those 10 flat files.

    I am loading Data from Flat File source to SQL server Table (OLEdb Destination. I created a SSIS package with one data flow task, which dynamic flat file and OLEdb Destination.

    I want to change the name of the Flatfile Name and Table Name for every run through configuration File. When I change the Names of FlatFile and Database Table (of different schema ). I got an Error stating VS_NEEDSNEWMETADATA.

    So can you please provide me any suggestions or any dtsx package file to overcome this issue.
    Can you please send the dtsx package file and config file to my mail id.

  25. Ness says:

    Hi Munish,

    What I like is opposite of this, instead of automatic selection of the “ALL” during the initial run of the report. I want to select one item, since I have more than 5 multi valued parameter in my report and most of them are using multiple values, so it take forever during the initial run of the report because it selects ALL item in all my multi-valued parameter.

    Your idea will be appreciated.

    Ness

  26. Ness says:

    Already got this, I just select the “No Default Values” instead of get values from query under Default Values Tab.

    Thanks!

  27. Dominic says:

    Hi Munish

    I am confronted with the following problem:
    We have serveral reports which we want to switch to multi-valued parameters. Serveral reports are linked together via drill-throughs.
    And now the problem:
    Say we have two reports 1 and 2 which utilize besides others a multivalued parameter Customer. In report 1 I choose the customers A,B, and C. I drill though to report 2 where the available customers contain only A and B. Then the pre selected list for report 2 will be empty instead of the intersect which would be A and B.

    How do I get this behaviour? Any idea?

    Thanks in advance

  28. Miss_Ed says:

    Hi Munish,

    I am trying to use an expression in a multi select parameter. If the user leaves the parameter blank, I want the parameter value to become “ALL”.

    The expression I tried (almost identical to the one you proposed for Savika) is:

    =IIF(Parameters!Account.Count=0, “ALL”, JOIN(Parameters!Account.Value, “,”))

    I’ve set the parameter to allow blanks.

    When I run the query outside the report and leave the Account parameter blank, I get data returned. But when I run it in the report, nothing is returned.

    Any idea why this wouldn’t work? I’m using SSRS 2005.

  29. venkat says:

    Hi,

    I would like to manipulate values dynamically.
    for example Tax of Jan 100 and Tax of Mar 200.
    I would like to calculate Jan+Mar Tax i.e 100+200=300 need to get in my report one of the field.
    could you please suggest.

    Thanks.
    Venkat

  30. viren says:

    Hi,
    I am using ssrs 2008
    How to set multivalue but Non-mandatory Parameter ?
    mean I don’t select any values or this parameter have no value even though Report can proceess further.
    suggest me for this.

    One more problem,
    Is it possoble to populate mix value of static and database values in single Parameter ?
    If yes then how?
    ex. Prameter: Course
    Values : Select ALL (Multi select : ticked)
    + Course name(from DB)
    + Not Assigned(As Static value)

  31. Helne says:

    Thanks – very helpful article!

  32. Bujji says:

    Hello,
    I am pretty New to Microsoft SQL Server 2008. I have a query about dependent parameters.
    Let’s say i have two parameters – “Cities” and “Streets”. By default i will have all the list of streets displayed. But when i select a city from the “Cities” list, automatically, the “Streets” list should be updated in the second parameter depending on the selected City. Can you please tell me the process, how i can do it in SSRS 2008. Thank you. : )

  33. Priya says:

    Hi Bansal,

    I ready your post on how to default multiple values. I have the same issue. I created linked reports where I have to default 2 values(Different combination of 6 values) and hide the param, But it does not default. Can I create another dataset for a stored procedure and 2 default 2 values. Also in the linked report would I be able to change the values I want?

  34. Bev says:

    Just what I was looking for and SO simple. Thank you for sharing.

  35. HUHU says:

    Hi need to populate a multi-valued parameter with a comma separated string parameter like (1,2,3) in SSRS … please help .. thanks in advance

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s