Home Dashboard Directory Help

Report Builder Multi Select Parameters from Dataset - Add 'Distinct' option by chrisco67



Sign in
to vote
Type: Suggestion
ID: 790148
Opened: 6/13/2013 6:24:06 PM
Access Restriction: Public


In Report Builder 3.0, when using a shared dataset as a report source, a user had asked if it was possible to get 'autofilter' functionality on the results (e.g. as in Excel's autofilter feature). I don't think that's currently possible, but thought that adding a set of multi-valued parameters to the report, where each parameter was based around a different field from the dataset might give something like that ability.

Say my report has a single dataset, Dataset1, that returns data for my report main table. It has a field called 'Region'. Say there are only 5 possible regions, but the dataset returns many thousands of rows.

I can add a multivalued parameter to my report, called Region, that is multivalued, and select 'Get values from a query' on the 'Available values' section, then select Dataset1, the value and label field. In this case, the value and label field would be the same, i.e. 'Region'.

However, there is no way to specify 'distinct' here - i.e. that I only want distinct(Region) values from Dataset1. The resulting multivalued parameter appears on the report when run, but has thousands of entries, mostly duplicate values.

I know that it would be possible to add another dataset to the report that did a distinct query, but since the results for Dataset1 have already been retrieved for the report, I would like a 'Distinct values' checkbox on this 'Available Values' section, so I can just use the existing dataset values.

This would be a way to go some way to provide the required 'auto filter' functionality, without the complexity of adding another dataset. If I wanted then to allow filtering on 10 fields, I just have to add 10 parameters to the report, rather than 10 parameters + 10 additional datasets.
Sign in to post a comment.
Posted by Microsoft on 6/2/2014 at 3:36 PM
Thank you for submitting this feedback. After carefully evaluating all of the suggestion items in our pipeline, we are closing items that we will not implement in the near future due to current higher priority items. We will re-evaluate the closed suggestions again in the future based on the product roadmap.
Thanks again for providing the product suggestion and continued support for our product.
Matt Jones
SSRS Tiger Dev Team
Posted by Microsoft on 5/14/2014 at 3:42 PM

Thank you for submitting this feedback. We are investigating the issue and will update you when we have more information.

Matt Jones
SSRS Tiger Dev Team
Posted by chrisco67 on 6/19/2013 at 7:43 PM
Hi, Please note also that this facility used to be available in Report Builder 1.0. In that version, you would create a filter on a field in the dataset, use the operator 'In a List', then set the filter to 'Prompt'. This would add a multi-valued parameter to the report, and the parameter had distinct values.
Sign in to post a workaround.