CR XI - Help with Running Total 1

[pndMaster]

Hello

I have below table data

SvcId SvcName ClId CntType
1 test1 100 1
2 test2 100 2
3 test3 100 3
4 test4 100 2
5 test2 101 1
6 test3 102 2
7 test4 102 3
8 test1 103 2
9 test3 104 3
10 test4 105 3
I need to know the count of ClId's (client id's) that receive only one type and not others like below
CntType ClId
1 101
2 103
3 104, 105
ClId 101 received only CntType 1, ClId 103 received only Cnt Type 2 and ClId 104 received only Cnt Type 3.
the final result should be
CntType ClId CountofClId
1 101 1
2 103 1
3 104
105 2

How can i implement this in report that a ClId needs to be counted in only one Cnt Type, if they belong to more than one Cnt Type then they will not be counted. They should have only one CntType, if they have more than one Cnt Type then they are ignored.

Any ideas on how to implement this?

Regards

 

[BettyJ]

I am not sure if the solution I am listing below is a good one. Hopefully someone else will soon post a most efficient solution which eliminates the suppress condition.

Group by ClId

Insert summary on
Field to summarize:SvcId
Calculate this summary:Count
Summary Location: Group #1:ClId-A

Copy all fields (SvcId, SvcName, ClId, CntType) to Group Footer
Hide Group Header and Details.
In Group Footer's Suppress Condition add Count {SvcId}, {{ClId})<>1

Hope this helps.

 

[pmax9999]

Assuming I understand your requirements correctly, I would do it this way:

1. Create a group on {Table.ClId}
2. Add the following Group Selection formula:

DistinctCount({Table.CntType},{Table.ClId}) = 1

Hope this helps

Cheers
Pete