Home Dashboard Directory Help
Search

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


Status: 

Active


1
0
Sign in
to vote
Type: Suggestion
ID: 790148
Opened: 6/13/2013 6:24:06 PM
Access Restriction: Public
0
Workaround(s)
view

Description

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.
Details
Sign in to post a comment.
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.