If a user selects all (266) parameter values from a mult-valued parameter list, I would like to display a table based on a version of a stored-proc that does not select records using parameter driven criteria.
The total selected count can be acquired by =Parameters!ParmName.Count syntax. Total available parameter values can be extracted by =CountDistinct(Fields!ColName.Value,"DatasetName").
Setting a filter on a table and inspecting the total selected count can limit the number of values executed for the query.
That is great, but I would also like to run an efficient query if all values were selected. Thought I could set a filter on a table and compare these two values, but I can't use an aggregate.
Any ideas?
Decided to create a one row dataset (query) containing a count of the number of values to select from. Used this value in a hidden parameter "PrimaryListCount" and used the new dataset to populate the default report parameter value. Then I determined if all values were selected using the following table filter:
=CInt(Parameters!PrimaryNames.Count) = =CInt(Parameters!PrimaryListCount.Value)
If you have a better solution, I'd like to know about it. If not, hope this can help someone else.
No comments:
Post a Comment