Need help Subtracting header counts

[MrHelpMe]

Hello,

I am having a brainfreeze today. Could someone please help me figure this out. I have a simple report as follows

GH1 OpenTickets 141 RT Formula 141
GH1 Resolved Not SRF 43 RT Formula-184
GH1 SRF Older 30 days 6 RT Formula-190
GH1 SRF Within 30 Days 14 RT Forumla-204

Now I need to figure out Real Open Tickets which is computed as follows, 141-43-6-14. The answer should be 78. So what I have done is I created a running total formula as follows:
Whileprintingrecods;
numbervar x:=x - Count ({p_total_tickets_detailed;1.TicketNum}, {p_total_tickets_detailed;1.DateSelection}))

However, my numbers are as follows for my running total(RT): -141 then -184, then -190 then -240. My formula is wrong somewhere. Any ideas?
Using crystal report 9.0 advanced. Thanks very much.

 

[Madawc]

Why use variables when Crystal will make totals for you?

Right-click on a field and choose Insert to get a choice of Running Total or Summary. Or else use the Field Explorer, the icon that is a grid-like box, to add running totals.

Running totals allow you to do clever things with grouping and formulas. They also accumulate for each line, hence the name. The disadvantage is that they are working out at the same time as the Crystal report formats the line. You cannot test for their values until after the details have been printed. You can show them in the group footer but not the group header, where they will be zero if you are resetting them for each group.

Summary totals are cruder, but are based directly on the data. This means that they can be shown in the header. They can also be used to sort groups, or to suppress them. Suppress a group if it has less than three members, say. They default to 'Grand Total', but also can be for a group.

You probably need just two running-total counts - one for 'open' and one that accumulates for the other three categories.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[MrHelpMe]

Thanks for the reply Madawc,

I used your suggestion and what I did was I created 4 running total that evaluates using a formula {p_total_tickets_detailed;1.DateSelection} = "Open Tickets"...replacing the "open Tickets" the other three values. However, now in the Report Footer I created a formula and subtracted {#RTotal1} - {#RTotal2} - {#RTotal3} - {#RTotal4}. However, the value I get is -14. Any ideas why. I should get 78. Thanks again Madawc.

 

[Madawc]

I suspect it's doing things in the wrong order. Try adding {#RTotal2} + {#RTotal3} + {#RTotal4} in a formula field. Display this. Then subtract it from {#RTotal1}

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[MrHelpMe]

Well I tried this Madawc but it seem like it's buggin up somewhere. Here is my data again
GH1 Open Tickets 141
GH1 Resolved not SRF 43
GH1 SRF Older then 30 6
GH1 SRF Within 30 14
RF Real Open Tickets 14(with the formula you suggested.)

Formulas Tried = The one you suggested as well as R1-R2-R3-R4. Any other ideas? Thank you once again.

 

[Madawc]

Are you using Running Totals for the entire report? Separate totals for each category? Each Group Header gets cleared after the group has printed.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[MrHelpMe]

Hi Madawc,

No, what I have done is I created separate running totals using the forumla option. If I could paste a print screen in here to show you I would. To give you an example I created a running total called Rtotal 1 and for the field to summarize I chose ticketnum, for the evaluate I chose use a Formula. The formula reads {p_total_tickets_detailed;1.DateSelection} = "Open Tickets" and for the reset I have on change of field Date Selection(this is just a column I added to a table that was created in my stored procedure). Then I took the Rtotal1 and put this in my RF. I repeated the procedure for R2, R3 and R4 each time gathering a number for each Group header. Hope that helps.

 

[lbass]

What is your group field? Your sample makes it look like you have a group on ticket status (open,resolved, etc.), and yet you also refer to a datefield as if it is a group field.

Also, do you want to do this comparison for the entire report or at some group level?

-LB

 

[MrHelpMe]

Hi lbass,

The Group field is called dateselection(this is just the column name) but within this dateselection are descriptions of the values I am looking for. I'm not sure if your familiar with creating tables in SQL but this dateselection is just a column name in the table.
Now based on the groups and there respective totals, I want to take those totals and subtract them to get a final value called Real Open tickets. So think of it for example like I have a report that is grouped on Automobile...
Audi 5
BMW 6
Porsche 10
Subtract cars = 5-6-10(this is the formula I am after).

In the example above I want to attain a total by subtracting the total Audi's(5) - total BMW's (6) - Total Porsche's (10). Hope that helps.
Thanks Lbass

 

[lbass]

Then in each running total, remove the reset field, and instead check "Reset never". Then use a formula to subtract the running totals from the first running total.

-LB

 

[MrHelpMe]

That's it LBass,

It worked perfectly. Still trying to figure out why the reset would make a difference. I used the reset and still noticed that the number for that running total was correct. Regardless I guess it worked but just wanted to know. Thanks again lbass for your help.

 

[lbass]

The running totals would look correct in the section in which they are calculating, but if you placed the same running totals in the report footer, except for the last group, they would have already been reset to zero--which is why you got the -14--that was the value of the last running total which was not reset.

-LB