Solved

CR9 - Problems summarizing with DistinctCount

Posted on 2003-11-27
5
294 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
  • 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Suggested Solutions

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…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

840 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