data_gerbil
asked on
CR9 - Problems summarizing with DistinctCount
Hi,
I'd be very grateful for any help with a problem I've been wrestling with all afternoon.
I am reporting on training activity from our Learning Management System's database, using CR9. I need to know how many managers, from each location have completed one or more of seven activities. I'm using three tables for this;
user - basic user info, transcript - their training records, jobrole - all users job role info.
There are seven activities, but I only need to know how many people have done one or more, so I am using using a DistinctCount of their systemID as this is a unique number. In the details section I have a formula "@mgrs_complete"
if {jobrole.role} = "Manager" and {transcript.code} startswith "SAP" and {transcript.status} = "c" then {user.systemID}
Which returns their system id for every occurence of a manager having completed ("c") one or more activity. This seems to work fine.
I have three groups; Store, Region, Division and I need to count the number of completed Managers in each group. For this I have used a DistinctCount of @mgrs_complete. And this is where the problem starts. In Region1, there are six stores. It correctly summarises for Store1, but for Stores2 to 6 the summary counts 1 higher than the actual number of distinct systemID's.
I have also tried using a running total, reset on chnage of group, and also on reset on {user.store}
Thanks in advance for any advice.
I'd be very grateful for any help with a problem I've been wrestling with all afternoon.
I am reporting on training activity from our Learning Management System's database, using CR9. I need to know how many managers, from each location have completed one or more of seven activities. I'm using three tables for this;
user - basic user info, transcript - their training records, jobrole - all users job role info.
There are seven activities, but I only need to know how many people have done one or more, so I am using using a DistinctCount of their systemID as this is a unique number. In the details section I have a formula "@mgrs_complete"
if {jobrole.role} = "Manager" and {transcript.code} startswith "SAP" and {transcript.status} = "c" then {user.systemID}
Which returns their system id for every occurence of a manager having completed ("c") one or more activity. This seems to work fine.
I have three groups; Store, Region, Division and I need to count the number of completed Managers in each group. For this I have used a DistinctCount of @mgrs_complete. And this is where the problem starts. In Region1, there are six stores. It correctly summarises for Store1, but for Stores2 to 6 the summary counts 1 higher than the actual number of distinct systemID's.
I have also tried using a running total, reset on chnage of group, and also on reset on {user.store}
Thanks in advance for any advice.
ASKER
Thanks Pete
If the criteria are not met, I wanted a blank outcome. The field {user.systemID} is a string field, and it seems to be doing just that.
The reason that I have not used these criteria for ecord selection is because I need to show a similar result for people who aren't managers (so therefore cannot restrict to {jobrole.role} = "Manager"), and I also need to show both results as a % of total headcount so I can restrict on {jobrole.role} or {transcript.xx}
If the criteria are not met, I wanted a blank outcome. The field {user.systemID} is a string field, and it seems to be doing just that.
The reason that I have not used these criteria for ecord selection is because I need to show a similar result for people who aren't managers (so therefore cannot restrict to {jobrole.role} = "Manager"), and I also need to show both results as a % of total headcount so I can restrict on {jobrole.role} or {transcript.xx}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
'if {jobrole.role} = "Manager" and {transcript.code} startswith "SAP" and {transcript.status} = "c" then {user.systemID}'
is incomplete.
What value do you expect to result if all these conditions aren't satisfied?
If systemid is a number then the 'else' result is 0, which is also counted as a value.
It sounds to me as if you should be using these rules as record selection criteria. This would eliminate from the report anyone not satisfying all the rules.
Pete