?
Solved

CR9 - Problems summarizing with DistinctCount

Posted on 2003-11-27
5
Medium Priority
?
297 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:data_gerbil
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 9835980
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
 

Author Comment

by:data_gerbil
ID: 9837796
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
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 9837921
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

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question