?
Solved

Crystal IF ELSE

Posted on 2009-12-31
7
Medium Priority
?
367 Views
Last Modified: 2012-05-08
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
Comment
Question by:Rowdyone52
  • 4
  • 3
7 Comments
 
LVL 77

Expert Comment

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

Author Comment

by:Rowdyone52
ID: 26154318
Exactly...it isnt working.  How do I make it work?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 26154392
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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 

Author Comment

by:Rowdyone52
ID: 26154418
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
 
LVL 77

Expert Comment

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

Author Comment

by:Rowdyone52
ID: 26154539
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
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 total points
ID: 26154875
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
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 quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

862 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