If than Counts

[TEM3]

Using Crystal Reports 8.5 and Oracle Tables.....

Using two tables, one that contains Cases (each record being a case). The other table contains assignments. Can be several per case (all nicely linked with Case Key).

I was asked to do a report for any cases that were created during the calendar year 2004 and that had assignments to either of two sections. The totals needed are:

2004 Cases with assignments to only section # 1.
2004 Cases with assignments to only section # 2.
2004 Cases with agginments to both sections.
The total of the above.

I was easily able to filter for the data I wanted and group by Case and then Assignment. But I had to do a hand count of 80 pages of Cases to get a total of the three conditions I was asked to total. What a drag!

How can I set up my Crystal Report to do that work for me? I know I need to have it go through the filtered records for the Case table and then look at the Assignment table to see if there exist assignments for Section #1 and/or assignments for Section # 2 and total the three possible conditions.

Seems simple, but I don't know how to start......

 

[Turkbear]

Hi,
Try creating 2 formulas ( ToSection1 and ToSection2, for instance)
that return 1 if assigned to the particular section and 0 if not..
Then sum these to count how many in each case ( for both, use a formula that returns 1 if both the others are 1 and 0 if not)

 

[synapsevampire]

Try posting example data and expected output rather than trying to describe data and the requirements.

I gather that the "section" is a column in the assignment table?

Can't you just group on that column as well?

-k

 

[TEM3]

Here is an example of the data as currently displayed by my report:

C04-02465 Case offense type = Aggravated Sexual Assault
DNA Assignment Date = 07/29/2004 Biological
C04-02499 Case offense type = Aggravated Sexual Assault
CRIM Assignment Date = 08/02/2004 Biological
DNA Assignment Date = 01/06/2005 Biological
C04-02630 Case offense type = Aggravated Sexual Assault
CRIM Assignment Date = 08/06/2004 Biological
DNA Assignment Date = 08/20/2004 Biological
C04-02680 Case offense type = Aggravated Sexual Assault
CRIM Assignment Date = 10/05/2004 Biological
DNA Assignment Date = 02/04/2005 Biological
C04-02681 Case offense type = Aggravated Sexual Assault
CRIM Assignment Date = 08/12/2004 Biological
DNA Assignment Date = 10/12/2004 Biological
C04-02771 Case offense type = Aggravated Sexual Assault
CRIM Assignment Date = 08/19/2004 Biological
DNA Assignment Date = 02/03/2005 Biological
C04-02811 Case offense type = Aggravated Sexual Assault
CRIM Assignment Date = 08/24/2004 Biological
DNA Assignment Date = 10/07/2004 Biological
C04-02841 Case offense type = Aggravated Sexual Assault
CRIM Assignment Date = 08/25/2004 Biological
C04-02850 Case offense type = Aggravated Sexual Assault
CRIM Assignment Date = 08/26/2004 Biological
DNA Assignment Date = 01/12/2005 Biological
C04-02910 Case offense type = Aggravated Sexual Assault
CRIM Assignment Date = 08/31/2004 Biological
C04-02930 Case offense type = Aggravated Sexual Assault
CRIM Assignment Date = 09/02/2004 Biological
DNA Assignment Date = 01/12/2005 Biological

Of course the formatting is lost in the above, but basically each case number (from the table LABCASE) is followed by any section assignments (from the Left Joined table LABREPT).

I need to total each of the possible three states: CRIM assignment only. DNA assignment only. Both CRIM and DNA assignment.

 

[lbass]

Can't tell what your fields are. Are "CRIM" and "DNA" two instances of the same field? Try the following, assuming you have a group on {table.caseID}. Create a formula:

//{@assignments}:
if {table.assignment} = "CRIM" then 1 else if {table.assignment} = "DNA" then 2 else 0

//{@sumassign}:
if sum({@assignments},{table.caseID}) = 1 then "CRIM" else
if sum({@assignments},{table.caseID}) = 2 then "DNA" else
if sum({@assignments},{table.caseID}) = 3 then "Both"

Use {@sumassign} to label the results in the group footer, and do a topN/group sort on {@assignments} to cluster the results, and use running totals to count the cases per category.

-LB

 

[TEM3]

Yes, CRIM and DNA are both data from LABREPT.Section. I will try your formula suggestion tomorrow. Thanks.

 

[TEM3]

OK, created the following formula (@SumAssign):

if sum({@SectionAssign},{LABCASE.Lab Case}) = 1 then "CRIM" else
if sum({@SectionAssign},{LABCASE.Lab Case}) = 2 then "DNA" else
if sum({@SectionAssign},{LABCASE.Lab Case}) = 3 then "Both"

and along with the other formula (@SectionAssign):

if {ALL_ASSIGNMENTS.Section} = "CRIM" then 1 else
if {ALL_ASSIGNMENTS.Section} = "DNA" then 2 else 0

it functions correctly, in that when I place @SumAssign in Group Header 1, it correctly identifies the state of that Case's section assignments. But (being the dummy I am), I do not understand your last paragraph of instructions on how to sum the three different conditions of @SumAssign??

I tried to group on @SumAssign (and if I could I could easily sum each Group), but it was not an available Group option.........

 

[lbass]

You do not have to use the topN/group sort function, unless you want the records to cluster in their respective categories. If you do want the clustering, go to report->topN/group sort and choose "Sum of {@assignments}" to sort the groups.

To get the counts per cluster, insert three running totals, each using {table.caseID}, distinctcount, evaluate based on a formula, reset never. In the evaluation using a formula section, use a formula like:

sum({@assignments}, {table.caseID}) = 1 //for "CRIM"

Change this formula in the other running totals to 2 or 3. Place the running totals in the report footer.

-LB

 

[TEM3]

I will try the above.....

However, I did think to try the following formula:

NumberVar DNAOnly;
NumberVar CrimOnly;
NumberVar BothSections;

WhilePrintingRecords;

if {@SumAssign} = "DNA" then DNAOnly := DNAOnly + 1 else
if {@SumAssign} = "CRIM" then CrimOnly := CrimOnly + 1 else
if {@SumAssign} = "Both" then BothSections := BothSections + 1

But no matter where I placed it or how I initialized my variables, they always returned 0.......

Wonder why it would not work??

 

[TEM3]

topN/group sort and Running totals are new ground for me. The topN group cluster worked (my report is not in order of Both, Crim and lastly DNA.

I created three formulas like:

sum({@SectionAssign},{LABCASE.Lab Case}) = 3 (for "Both)

and placed them in the report footer. But all they return are True of False, not a number??!!

I guess what I don't really understand is this paragraph:

"To get the counts per cluster, insert three running totals, each using {table.caseID}, distinctcount, evaluate based on a formula, reset never."

I am missing where/how to do the above...... Sorry.

 

[lbass]

Go to Insert->(field explorer, possibly)->Running total and the expert will come up. Enter a name for the RT, e.g., "Crim" and then follow my earlier directions.

-LB

 

[TEM3]

Got it all to work. Silly me, I was looking for "Running Totals" in the wrong place.

However I discovered that there is a fly in the report logic ointment! Some of these "Cases" have (and can have) two or more assignments to the same unit. There can be more than one Crim assignment and/or more than one DNA assignment. My report (the way it is currently arranged) is picking up both (all). So cases with two CRIM assignments are generating a "2" and ending up in the DNA pile, etc.

Let me see if I can correct that..... Suggestions are welcome!

 

[TEM3]

I was going to try to incorporate a switch type formula (that would only be set to 1/true for each assignment record) like:

NumberVar CV;
NumberVar DV;

if {ALL_ASSIGNMENTS.Section} = "CRIM" then CV = 1 else
if {ALL_ASSIGNMENTS.Section} = "DNA" then DV = 1

but I'm not sure how to make it work with what we have done previously...... (and my head hurts....).

 

[lbass]

Change {@assignments} to:

if {table.assignment} = "CRIM" then 1 else if {table.assignment} = "DNA" then 10000 else 0

Change {@sumassign} to:

if sum({@assignments},{table.caseID}) < 10000 then "CRIM" else
if remainder(sum({@assignments},{table.caseID}),10000) = 0 then "DNA" else
if sum({@assignments},{table.caseID}) >= 10001 and
remainder(sum({@assignments},{table.caseID}),10000) <> 0
then "Both"

-LB

 

[TEM3]

You logic works! I would have never thought of that!!

However, my running totals and topN sort are not right. I'm sure I just need to tweak them. I'll take a look after lunch.........

 

[TEM3]

In my topN/group sort tab my only choice is "sort on {@SectionAssign} which is the formula:

if {ALL_ASSIGNMENTS.Section} = "CRIM" then 1 else
if {ALL_ASSIGNMENTS.Section} = "DNA" then 10000 else 0

It is not sorting properly.

 

[lbass]

You're right, the topN will no longer give you the results you want. You could instead create a subreport for each group. Save the report under another name, e.g., "CrimSub", and then go to edit selection formula->GROUP and enter:

sum({@assignments},{table.CaseID}) < 10000

For "DNASub", use a group selection formula of:

sum({@assignments},{table.CaseID}) >= 10000 and
remainder(sum({@assignments},{table.CaseID}),10000) = 0

For "BothSub", use a group selection formula of:
sum({@assignments},{table.caseID}) >= 10001 and
remainder(sum({@assignments},{table.caseID}),10000) <> 0

Place each subreport in its own group header section. When using group selection, your running totals could now just be the distinct count of caseID, evaluate for each record, reset never, assuming you are displaying these within each subreport.

-LB

 

[TEM3]

Wow! Neat. I now have three reports that give me exactly what I needed. I will probably suck them into one report, but maybe not.....

I know I'm pushing my luck, but not only did I want to have a working Report, but I also (now) want to try to understand the parts that, even though they are working, I do not understand.

This particular formula:

sum({@SectionAssign},{LABCASE.Lab Case}) >= 10001 and
remainder(sum({@SectionAssign},{LABCASE.Lab Case}),10000) <> 0

I sort of understand the logic, but not the syntax. Could as much as possible of it be explained? Especially what is going on with "({@SectionAssign},{LABCASE.Lab Case}" and "remainder(sum({@SectionAssign},{LABCASE.Lab Case}),10000)". What is happening when we put @SectionAssign together with the field, LABCASE.Lab Case? What does the "remainder" argument/expression do??

 

[lbass]

Whenever you see a summary like:

sum({table.field},{table.group})

The second condition is the group field, and it means that the summary should be per group. The remainder function provides the balance after the first field is divided by the second. In the case of:

remainder(sum({table.field},{table.group}),10000)

if the subtotal for the group = 20004, then the remainder will be 4, since 20000 is evenly divided by 10000. The logic for the particular formula you mention is that first if the result is >= 10001, it must include at least one "DNA" record (unless there are 10000 "CRIM" records!) and the remainder portion says that if there is a remainder, then there must be a "CRIM" record, since only "CRIM" records are incrementing by 1. Therefore both types of records must be present.

-LB

 

[TEM3]

It's starting to make sense (becoming understandable). Thanks.

Now, just for grins, how might I handle a third (and even a forth) section?? It is possible that a single case might have assignments to all four of our laboratory sections: Crim, DNA, Drugs, Toxicology.

That will probably be the next Report I am asked to create (show Tox assignments, since Date Rape Sexual Assult Cases have items assigned to Toxicology)!