• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

Crystal IF ELSE

I need to count the number of unquie agents in each group, excluding sys.  I setup a running total field GroupCounter that resets on each group.  So when it is 1, we are in a new group.  I want to return agentcounter based on the rules below, but the if else isnt saving.  Any suggestions?
----------------------------------------------------------------------------------------------------------------
WhilePrintingRecords;
Global numberVar agentcounter;
Global stringVar added;

IF({#GroupCounter} =1) THEN
(
    IF({Command.CTCLAGENT} <> "sys") THEN
    (
    agentcounter:=1
    added := added +","+{Command.CTCLAGENT}
    )
    ELSE
    agentcounter:=0
)
ELSE IF({Command.CTCLAGENT} <> "sys" AND INSTR(added,","+{Command.CTCLAGENT}) = 0) THEN
(
agentcounter:=agentcounter+1
added := added +","+{Command.CTCLAGENT}
)
0
Rowdyone52
Asked:
Rowdyone52
  • 4
  • 3
1 Solution
 
peter57rCommented:
The code you have posted cannot be the code you are using.
There are no statement terminators in your code so you should be getting errors when you try to save the formula.
0
 
Rowdyone52Author Commented:
Exactly...it isnt working.  How do I make it work?
0
 
peter57rCommented:
There is no command which explicitly outputs a value from this formula and I don't know whether you want any of the values to be used as the result.

Interms of syntax it looks like you should have...

IF({#GroupCounter} =1) THEN
(
    IF({Command.CTCLAGENT} <> "sys") THEN
    (
        added := added +","+{Command.CTCLAGENT};
        agentcounter:=1
    )
    ELSE
    agentcounter:=0
)
ELSE IF({Command.CTCLAGENT} <> "sys" AND INSTR(added,","+{Command.CTCLAGENT}) = 0) THEN
(

added := added +","+{Command.CTCLAGENT};
agentcounter:=agentcounter+1
)
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 
Rowdyone52Author Commented:
That works perfectly.  Now I was hoping to use a MAX() Summary to show the counter on the GroupRecord header, but it isnt available to select under summary fields.  Is there a reason its excluded?
0
 
peter57rCommented:
Yes.
Group summaries are calculated before printing.
So if the formula can only be calculated at print time - as is the case here- then standard summaries are not available.

If you need to summarise values from this formula then you have to set up a variable to hold the summary - in a formula field in the group header- and then put the value you want into it in another formula field.

so for a max, you would have logic like..

numbervar mymax;
if latestvalue >mymax then mymax :=latestvalue;

I'm not sure that this is what you should be doing.  As I said before I can't deduce what result you want from the formula - if any.

0
 
Rowdyone52Author Commented:
I have tried to do this several ways and keep coming up empty.  I've probably overcomplicated the issue in my failed attempts.

Basically I have a userID in the field CTCLAGENT.  I need to count the DISTINCT userID's per Group, however I do not want to count SYS.

0
 
peter57rCommented:
I don't know enough about your report to be sure what follows is relevant, but if I were doing this i would use 3 formulas as below...

groupheader...zeroise counter

whileprintingrecords;
numbervar ySys:=0;
""

detail line...test for sys in group

whileprintingrecords;
numbervar ySys;
if {Command.CTCLAGENT} ="sys" then ySys:=1;
""

group footer...use standard distinct count summary and deduct 1 if any sys is found

whileprintingrecords;
numbervar ySys;
DistinctCount ({Command.CTCLAGENT} , {groupfieldnamehere})-ySys


0
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now