Distinct Count in a Subeport

[crogers111]

CR XI
SQL

I'm trying to get a total distinct count of the Employee IDs that reside in the subreport.

The Main report is grouped on MemberID. The subreport resides in the Member ID Grouping as it displays needed data per member. It is also linked on MemberID. Not all Member's in the main report have data in the subreport

Using the sample data below I want a count of 5...for the 5 distinct Employee IDs


Main Report SubReport
MemberID MemberID Employee ID
1111 1111 AAAA
2222 2222 BBBB
3333 3333 BBBB
4444 4444
5555 5555 CCCC
6666 6666 DDDD
7777 7777 EEEE

 

[lbass]

In the subreport, create a formula like this and add it to the detail section:

whileprintingrecords;
shared stringvar empl;
if not isnull({table.employeeID}) and
not({table.employeeID} in empl) then
empl := empl + {table.employeeID}+", ";

In the report footer of the main report, add a formula like this to do the count:

whileprintingrecords;
shared stringvar empl;
stringvar array y := split(empl,", ");
ubound-1

If you want the result at some higher order group level, you would need to add a reset formula for empl at the group header level, and put the display formula in the group footer instead of the report footer.

//{@reset}:
whileprintingrecords;
shared stringvar empl := 0;

-LB

 

[crogers111]

Thank you! I had to tweak a couple things to account for my employeeID field being a number but I got the count I was expecting.

@Increment
whileprintingrecords;
shared stringvar empl;
if not isnull({table.employeeID}) and
not(totext({table.employeeID}) in empl) then
empl := empl + totext({table.employeeID})+", ";

@Reset
whileprintingrecords;
shared stringvar empl := "";

Thanks again !!

 

[lbass]

I assumed it was already a string because of your sample above where you were using alpha characters.

-LB

 

[crogers111]

Sorry LB, you are right.

Note to self: use the same data type in your sample as the actual data.