Exclude certain records from a total

[Juice05]

I have a list of months and quarters with sales data for each month and quarter. I need a total at the end...how do I sum but exclude records that are quarters?

My Data looks like this:

Company Month Sales
1 Jan 120
1 Feb 150
1 Mar 100
1 Q1 370
1 Apr 100
1 May 120
1 Jun 160
1 Q2 380

How can I sum up the month field for records that do not = Q1, Q2, Q3, Q4?

 

[pmk16]

please try the following:

whileprintingrecords;
numbervar x = 0;
if month in ["Jan","Feb","Mar","Apr","May",....,"Dec"] then
x:= x + sales
else
x:= x+ 0

 

[Juice05]

I have since had to change the sales value to a formula. Does the same formula that you provided still work?

 

[Madawc]

I normally use running totals rather than formulas. Worth getting to know them, because they get the same results with less work.

Running totals will add any formula field that is based on just a single 'row' of data, the stuff you could display in a detail line.

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

 

[Juice05]

Madawc,

So I could have a Running Total but exclude certain records?

 

[Juice05]

pmk16,

I fave two groups in my report for displaying purposes.

Your formula works if I put the formula within the second group footer, which I can't do because it repeats after each month(which I can't have). How can I tweak the formula to allow me to place it within the company group footer?

 

[BarbaraFuhrmann]

Hi Juice,

In case like yours I usualy use the running total. Yours could look like (all labeling can be exactly or similar in your CR because I'm running a german version):

Name: YourRunningTotal
Field for result: Table.SALES
Kind of result: SUM
Evaluate Section: Mark "Use Formula", then click at the formula button and enter: left({TABLE.MONTH}) <> 'Q'
restore section: mark something if the running total has to be restored.

You can use the running total directly in the report or make it more perfect and create a formula like:
{@YourRunningTotal_edited}
if isNull({#YourRunningTotal})
then
0
else
{#YourRunningTotal}

You can place the running total itself or the formula in the place you need to see the result:
If you place it in the detail section you will see it growing for every record beside the quarters. If you place it in a group or report footer you will see the final result.

HTH
Barbara

Barbara Fuhrmann (Cologne, Germany) - Using Windows 2000/XP, Oracle 9i and Crystal Reports X

 

[Juice05]

BarbaraFuhrmann,

That solution sounds really promising. However, I have taken a different approach and calculated everythin on the SQL end.

I will try your solution in future reports (They are coming) where I run into a situation like this.

Thanks

 

[pmk16]

I would have suggested group in 'specified order' and then first show only records from Jan-Dec and a second group showing only records from Q1...Q4.

In your first group from Jan- Dec, go to 'Others' tab which gets enabled when you select 'specified order' and select the option:
Put all others together with a name : 'Quarter'

Now, you would be able to sum all records from Jan-Dec using Summary operation

 

[Madawc]

To answer the earlier question. A running total includes a 'formula' option in the middle 'evaluate' section.

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