Help creating aggregate report - group by count

Help Studio
, , , ,
1

Continuing the discussion from Help creating aggregate report:

Hello,

Thank you for your previous help. Can I ask for further help on the above topic @Yoichi @ppr @vrdabberu ?

If source data looks like this with a student that didn’t have a subject, how do I show their count in the aggregate report show as 0? (see John Smith below):

Grade First Name Surname DOB Subject Class Finals Due Resits
Sophomore Alfie Bradley 01/01/2011 English True False
Sophomore Alfie Bradley 01/01/2011 Music True False
Sophomore Alfie Bradley 01/01/2011 French True False
Sophomore Alfie Bradley 01/01/2011 Maths True False
Sophomore Bob Doe 02/02/2011 Chem True False
Sophomore Bob Doe 02/02/2011 French True True
Junior Charlie Blogg 03/03/2013 Shop False True
Junior Charlie Blogg 03/03/2013 English False True
Junior John Smith 04/04/2014 False True

I’d like the output to look like this:

Grade First Name Surname DOB Completed Finals Count of Entries Resits
Sophomore Alfie Bradley 01/01/2011 True 4 False
Sophomore Bob Doe 02/02/2012 True 2 True
Junior Charlie Blogg 03/03/2013 False 2 True
Junior John Smith 04/04/2014 False 0 True
2

One of many options

  • Group the data e.g. with a group by
  • instead of grp.Count use the following summing up
grp.Sum(Function (g) Convert.ToInt32(Not String.IsNullOrEmpty(g("Subject").ToString.Trim)))
1 Like
3

HI,

Can you try the following sample?

dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(r(0).ToString,r(1).ToString,r(2).ToString,r(3).ToString)).Select(Function(g) dtResult.LoadDataRow({g.Key.Item1,g.Key.Item2,g.Key.Item3,g.Key.Item4,g.Any(Function(r) r("Class Finals").ToString.ToLower="true" ),g.Count(Function(r) not String.IsNullOrWhiteSpace( r("Subject").ToString)),g.Any(Function(r) r("Due Resits").ToString.ToLower="true")},False)).CopyToDataTable

Sample20240127-1aV2.zip (15.7 KB)

Regards,

3 Likes
4

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.