none
SSRS Multivalue Parameter

    Question

  • There is a select all option for each multivalue parameter. If this option is selected, I would like to display in the report that the parameter selected is "ALL" instead of using the Join(parameter!parametername.value,",") to display all the values. Any idea how I check to see if the select all at the beginning is checked?

    Tuesday, March 04, 2008 4:56 PM

Answers

  • I created a solution for my problem. There may be a more legit way to do it but this is working for me:

    ----------------------------------------------------------------------------------

    CREATE TABLE ##div_choice (
     DIV_NAME_2 nvarchar(30))

     

    INSERT into ##div_choice VALUES ('All Divisions')


    INSERT into ##div_choice
     SELECT DISTINCT dof_division_name from tbl_dof_divisions ORDER BY dof_division_name

     

    SELECT * FROM ##div_choice

    ----------------------------------------------------------------------------------

     

    It puts the 'All Division' choice at the top without having to use the

    '<All Divisions>' format and the other choices are sorted in alphabetical order.

    Tuesday, March 11, 2008 6:24 PM

All replies

  • You can determine the number of values possible, then compare that to the number of values selected using =Parameters! <paramName>.Count and display "ALL" when equal.

     

    Wednesday, March 05, 2008 1:34 AM
  • How do you determine the number of values possible? To you add a field to the sql to get the count or is there an internal function to find it?

     

    Wednesday, March 05, 2008 4:03 PM
  • In my case I am selecting account managers based on the team to which they belong.

     

    I have one parameter (Sales_Team) that then feeds into a dataset that retrieves the account managers on that team.

     

    I added a new dataset that counted the account manager parameter values.  My dataset contains the same criteria for chosing the employeeid but it returns only the count:

     

    Select Count(EmployeeID) as Total_Managers

      From Team_Members T LEFT OUTER JOIN

               Employee E on T.EmployeeID = E.EmployeeID

      Where @Sales_Team = Sales_Team

     

    I then added an internal parameter that has the default value of Total_Managers. 

     

    The code I then used to display All Managers is:

     

    ="For " & IIF(Fields!Total_Managers.Value = Parameters!Account_Manager.Count, "All ", "") & Parameters!Sales_Team.Label & IIF(Parameters!Account_Manager.Count > 1, " Account Managers ", " Account Manager ") & IIF(Parameters!Account_Manager.Count = Parameters!Total_Managers.Value, "", Join(Parameters!Account_Manager.Label, ", "))

    Thursday, March 06, 2008 6:37 PM
  • Another way to find the number of values possible for the parameter could be using the CountDistinct function. Something like:

    CountDistinct(Fields!<FieldName>.Value,"<DatasetName>")

     

    Here obviously <FieldName> is the field that you use to populate your multi-valued parameter & <DatasetName> is the dataset where you're fetching this field from.

     

    Then this value can be compared to Parameters!<parameterName>.Count and so on...

     

    -Aayush

    Friday, March 07, 2008 11:37 AM
  • Hi,

     

    I have couple of queries regarding SSRS

     

    1)

    I am new to SSRS and have a very common requirement to fulfill, here the requirement,

     

    I need to generate reports based on any input parameters, i.e. reports should be generated on any combination of StoreName, District or State

    • By StoreName
    • By District
    • By State

     

    Here is the table structure,

     

    StoreID

    StoreName

    District

    State

    1

    Store1

    District1

    State1

    2

    Store2

    District2

    State1

    3

    Store3

    District2

    State2

    4

    Store4

    District3

    State2

    5

    Store5

    District1

    State2

     

     

    How can I make my parameters dynamic so that when I select State value, District value gets changed accordingly. Currently I have three different dataset for 3 parameters (StoreName, District and State) but in that case user can choose any value for anything. For example user can select Store5, District3 and Stat1, which is an invalid combination.

     

    Can anyone give me some details on how to implement this.

     

    2)

     

    There is a one more requirement, if we set that parameters can have multiple values how to handle them in query?

     

    Select * from tblStore where StoreName = @StoreName

     

    This query will work only for single storename, how to pass multiple storenames to the query?

     

    Any help in the above queries will be greatly appreciated.

     

    Thanks,
    Dhaval Patel

    Dhaval.p.patel@gmail.com

    Saturday, March 08, 2008 10:02 PM
  • Hi Dhaval,

     

    1) You will need to use cascading parameters wherein the value of one parameter is used to populate the values for the next and so on.

    For example in your case let us say you have three parameters State, District & StoreName in that order which are being populated from datasets dataset1, dataset2 & dataset3 respectively.

    You can then use the values selected for the State parameter to filter the dataset2 query so that it only returns the Districts within the states selected.

    Similarly then use the values selected for the District parameter to filter the dataset3 query so that it only returns the StoreNames within the districts selected.

     

    Obviously here the parameters will have to be entered in the order in which they're defined i.e. unless you select values for parameter State, the parameter District will not get populated, and so on..

     

     

    2) Use the IN clause as follows:

    Select * from tblStore where StoreName IN (@StoreName)

     

    Hope this is clear,

    Aayush

    Tuesday, March 11, 2008 9:44 AM
  • I have another situation related to this.

    Users for my report need to choose either all values or only one.

    If I use a multivalue parameter, it displays the 'Select All' option but it also

    allows them to select one or more values, which should not be allowed.

     

    If I use a query to display the choices where they can only select one, there is no 'Select All' option.

     

    Has anyone coded for this situation?

     

    Thanks,

    minkisi

     

    Tuesday, March 11, 2008 3:06 PM
  • I created a solution for my problem. There may be a more legit way to do it but this is working for me:

    ----------------------------------------------------------------------------------

    CREATE TABLE ##div_choice (
     DIV_NAME_2 nvarchar(30))

     

    INSERT into ##div_choice VALUES ('All Divisions')


    INSERT into ##div_choice
     SELECT DISTINCT dof_division_name from tbl_dof_divisions ORDER BY dof_division_name

     

    SELECT * FROM ##div_choice

    ----------------------------------------------------------------------------------

     

    It puts the 'All Division' choice at the top without having to use the

    '<All Divisions>' format and the other choices are sorted in alphabetical order.

    Tuesday, March 11, 2008 6:24 PM
  •  

    Thanks Ayush

    Tuesday, March 25, 2008 9:05 PM
  • Hi,

     

    I have a report which takes ID as a parameter and based on that it shows the rows. I want to have only one input parameter (comma separated) and user must type the IDs (not a list where user can select values). What should be the query to handle it?

     

    Select * from table1 where ID in (:ID) works for single value, for inputs like 1, 2, 3 the query doesn't work.

     

    Your help with this regards is much appreciated.

     

    Thanks,

    Dhaval

     

    Thursday, November 13, 2008 1:50 AM
  • Aayush,
    After searching Google for several hours, this was EXACTLY the answer I was looking for and it worked perfectly!
    Thanks.
    Tuesday, January 12, 2010 12:31 PM
  • I did it like this.... 
    Count the records in the dataset source for the drop down, compare to count of the associated parameter. If equal, you can put the string ALL.
    ="Plan Type: " + IIF(
    (Count(Fields!PlanTypeID.Value, "dsPlanTypes") = Parameters!PlanType.Count)
    ,"ALL",
    "Plan Type = " + Join(Parameters!PlanType.Label, ","))
    Tuesday, April 05, 2011 1:17 AM