How do I group a result for a report?

(Sebastian Tiller) #1

I’m setting up a scheduled report template and I would like to achieve the following…

I have a table of data such as:

Sample Date    Barcode      Item             Passed
21/02/2019     PLT-00592    1006-STR-1818    Yes
21/02/2019     PLT-00593    1006-STR-1836    Yes
21/02/2019     PLT-00594    1006-STR-1835    Yes
21/02/2019     PLT-00595    1006-STR-1837    Yes
21/02/2019     PLT-00596    1434-STR-1480    No
21/02/2019     PLT-00598    1434-STR-1480    No
21/02/2019     PLT-00599    1434-STR-1480    No
20/02/2019     PLT-00600    1434-STR-1480    No
20/02/2019     PLT-01300    1044-STR-8138    No
20/02/2019     PLT-01301    1044-STR-8138    Yes
20/02/2019     PLT-01302    1044-STR-8138    Yes
20/02/2019     PLT-01303    1044-STR-8138    No
20/02/2019     PLT-01456    1044-STR-8170    No
20/02/2019     PLT-01458    1044-STR-8170    No
20/02/2019     PLT-01459    1044-STR-8170    No
20/02/2019     PLT-01460    1044-STR-8170    No

I’m trying to find the number of unique items which Passed.

For example, I need to have a total of 5 passing Items out of 7, instead of 6 (a count of Yes).

Perhaps I could create a new Virtual Column and join Item and Passed and then do a select on Distinct Values… Any help would be greatly appreciated!

(Aleksi Alkio) #2

You should be able to count it directly in your template like COUNT(SELECT(TableName[Item],[Passed]=“Yes”,TRUE)) where the TRUE filters duplicates away.

(Sebastian Tiller) #3

Great, thanks @Aleksi

(Aleksi Alkio) #4

You’re welcome