Solved

CR9 - Problems summarizing with DistinctCount

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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. …
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 …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now