Count Distinct Staff Ref Nos of those who have 1 or 2 or 3 records

Question

I am trying to build a column chart that would count distinct Staff Ref Nos of those who have 1 or 2 or 3 records.

As an example the included table shows 3 distinct Staff Ref Numbers next to 3 columns, one for each academic year, showing if they have a record (1 for yes) in any of the 3 years.

I would like to be able to count number of Staff who have 1 record, 2 records or 3 records, please.

I tried

=CountDistinct(IIF(Sum(Fields!NoofOTrecordsin2122.Value+Fields!NoOTrecordsin2223.Value+Fields!NoofOTrecordsin2324.Value)=1,Fields!StaffRef.Value,Nothing))

I was expecting to get a number of distinct Staff Ref Nos of those with 1 record, but got this "The Y expression for the chart ‘Chart6’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset"

Staff Ref	No of OT in 2122	No of OT in 2223	No of OT in 2324
15088	1	0	0
15088	0	1	0
15088	0	0	1
15157	0	1	0
15157	0	0	1
20622	0	0	1
20454	0	1	0
20454	0	0	1
20465	1	0	0
20465	0	1	0
20465	0	0	1

When I do a pivot of this data, it shows me that 2 Staff Refs (15088 and 20465) have 3 records (2122,2223,2324) 2 Staff Refs (15157 and 20454) have 2 records (2223,2324) 1 Staff Ref (20622) has 1 record (2324)

I would like to be able to count the number of Staff Refs who have 3 records, those with 2 records and those with 1 record.

Answer 1

This is much easier to do in the dataset query, so that's the approach taken here.

First get your sample data (this is the kind of thing its useful to add to the post as a db-fiddle so others can copy it in and have data ready to play with)

DECLARE @t TABLE (StaffRef int, OT2122 int, OT2223 int, OT2324 int)

INSERT INTO @t VALUES 
(15088, 1, 0, 0),
(15088, 0, 1, 0),
(15088, 0, 0, 1),
(15157, 0, 1, 0),
(15157, 0, 0, 1),
(20622, 0, 0, 1),
(20454, 0, 1, 0),
(20454, 0, 0, 1),
(20465, 1, 0, 0),
(20465, 0, 1, 0),
(20465, 0, 0, 1)

SELECT StaffRecCount, StaffCount  = COUNT(*)
    FROM 
        (
        SELECT DISTINCT
            StaffRef
            , StaffRecCount = SUM(OT2122+OT2223+OT2324) OVER (PARTITION BY StaffRef) FROM @t
        ) rc
GROUP BY StaffRecCount

The query at the end takes the sample data, sums the 3 columns for each StaffRef and then the outer query takes these results and groups them by the counts in the inner query.

It's a pain as it's hard coded to columns but that's all I had to go on. I suspect the data is held in a more normalised form somewhere and if it is it will make things easier... anyway...

The end result is a very simple dataset that looks like this.

StaffRecCount	StaffCount
1	1
2	2
3	2

Now we just add a simple column chart and setup as follows... Also set the sort order of the Category Group to be in reverse so the results are 3,2,1

Final output looks like this... (needs a bit of tidying but close enough)