mikesnp
Hi Bruce,
When we set another parameter to the count of the dataset, the data set is requeried. Big performance drag. I believe this has been discussed in other threads.
What we have resolved to do is this...
Some background about our report (simplified):
1. Parameter Name = StaffID
2. StaffID Dataset = lsp_GetStaff (a stored procedure)
3. Report has one table with dataset = lsp_GetStaffProductivity
4. lsp_GetStaffProductivity is passed StaffID, which we declare as @StaffID
The Problem
If the user checks (select all) Staff, we want to prevent the execution of the line
WHERE StaffID IN @StaffID
in our stored proc lsp_GetStaffProductivity
The Resolution:
1. In lsp_GetStaffProductivity, we declare two parameters:
@StaffIDCount = udf_GetCountFromList(@StaffID)
@lsp_GetStaffCount = Select Count(StaffID) From Staff
Then
if @StaffIDCount <> @lsp_GetStaffCount
BEGIN
WHERE StaffID IN @StaffID
END
Now, What We'd like from Microsoft...
Ability to set a parameter in the report to the value of (Select All), e.g. SelectAll = Parameters.StaffID.SelectAll. That way we can simply pass this value to the report's stored proc. So, in the sp we can write...
if @SelectAll = False
BEGIN
WHERE StaffID IN @StaffID
END
BTW, I am aware of the UNION alternative where the "All" choice is user defined. This is not acceptable for us. We have several cases where the user likes to check Select All in order to auto check all the selections (there can be dozens), and then unchecks just a few.
I am open to other suggestions from the community and I hope my experience helps others.
Thanks - Mike