Solved

CR9 - Problems summarizing with DistinctCount

Posted on 2003-11-27
5
295 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 125 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

749 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