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.
data_gerbilAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
the result of your statement:
'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


0
data_gerbilAuthor Commented:
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}

0
peter57rCommented:
In that case I would suggest that what you may have is userid values which have trailing spaces or some other subtle difference.  I do not beleieve CR gets it wrong with distinctcount so you have to look at the data here.

Use .......{transcript.status} = "c" then Trim({user.systemID}) to get rid of spaces.


Pete



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.