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?

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.

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?

In my case Iam 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, ", "))

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

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

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 caselet us sayyou 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 herethe 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 clauseas follows: Select * from tblStore where StoreNameIN (@StoreName) Hope this is clear, Aayush

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

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

Thanks Ayush

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

Aayush, After searching Google for several hours, this was EXACTLY the answer I was looking for and it worked perfectly! Thanks.

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, ","))