Tuesday, March 04, 2008 4:56 PM
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?
Wednesday, March 05, 2008 1:34 AMYou 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 4:03 PMHow 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?
Thursday, March 06, 2008 6:37 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, ", "))
Friday, March 07, 2008 11:37 AM
Another way to find the number of values possible for the parameter could be using the CountDistinct function. Something like:
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...
Saturday, March 08, 2008 10:02 PM
I have couple of queries regarding SSRS
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,
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.
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.
Tuesday, March 11, 2008 9:44 AM
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,
Tuesday, March 11, 2008 3:06 PM
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?
Tuesday, March 11, 2008 6:24 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 (
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 25, 2008 9:05 PM
Thursday, November 13, 2008 1:50 AM
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.
Tuesday, January 12, 2010 12:31 PMAayush,
After searching Google for several hours, this was EXACTLY the answer I was looking for and it worked perfectly!
Tuesday, April 05, 2011 1:17 AMI 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, ","))