Record Selection From a Sum Calculation

[arodri]

Hello,

I have a report with four groups (1.Region, 2.Site, 3.Type, 4.Date) and all of my data and calculations are in in Group #4 footer). The source of this report is a SQL stored procedure.

Below is a sample of my data (Total,Cash, and Noncash are the fields with formulas, the rest of the fields come directly from the stored procedure):

Code Site Type Date Total Cash NonCash
612 MN62 W 2/3/2011 8.36 0.35 8.01
811 MN81 L 2/3/2011 0.00 0.00 0.00
801 MN81 W 2/3/2011 (1.76) (2.03) 0.27
802 MN82 W 2/3/2011 0.00 0.00 0.00

My problem is that I need to exclude all records from this report that have Cash AND NonCash fields equal to 0.00. So for example in the data above, it would exclude the 2nd and 4th records and would just look like:

Code Site Type Date Total Cash NonCash
612 MN62 W 2/3/2011 8.36 0.35 8.01
801 MN81 W 2/3/2011 (1.76) (2.03) 0.27

I can't do this in the record selection because Cash and Noncash fields are sum formulas:

Cash formula: sum({@Cash},{transdate))
NonCash formula: sum({@NonCash, {transdate})

The formulas @Cash and @Noncash just take certain amounts from the stored procedure (ex. IF category = 712 then strdprocedure.amt else 0)

Any idea on how to remove the fields that meet the
(Cash=0 AND NonCash = 0) condition?

Thanks in advance for any suggestions!

 

[swiss2007]

create 2 formulas
@Cash

if Cash <> 0 then Cash else 0

@NonCash

if NonCash <> 0 then NonCash else 0

insert a sum on these 2 formulas in GF4 and u can suppress it.

then go to report ---->selection formulas----> Group and write a condition
sum(@Cash,transdate) = 0
and sum(@NonCash,transdate) = 0

this will remove all cash =0 and noncash = 0 rows.

 

[swiss2007]

Hi Tested again,
USE

create 2 formulas
@Cash

if Cash = 0 then Cash else 0

@NonCash

if NonCash = 0 then NonCash else 0

insert a sum on these 2 formulas in GF4 and u can suppress it.

then go to report ---->selection formulas----> Group and write a condition
sum(@Cash,transdate) = 0
and sum(@NonCash,transdate) = 0

this will remove all cash =0 and noncash = 0 rows.

 

[lbass]

Group selection is the way to go, but no need to create new formulas for cash and noncash--you can just use your current cash and noncash formulas:

sum({@Cash},{transdate}) <> 0 and
sum({@NonCash,{transdate}) <> 0

Group selection selects the rows to show. Non-group selected records are still IN the report, so use running totals if you just want to use the displayed values in calculations across records, since the more usual summaries will pick up the non-displayed values.

-LB

 

[arodri]

Hello again,

I've tried both methods (creating the new formulas and just using my current formulas in the group selection) and neither one is really working. Both still leave records where Cash and NonCash are equal to zero.

I also can't use running totals for this report - it just gives me zero for every site so that's why I used a regular formula.

Any idea why it's not working?

Thanks.

 

[lbass]

Assuming you only want records where BOTH sums are not equal to zero, mine should have worked. I wonder if your values have some decimal amounts. Try this:

round(sum({@Cash},{transdate}),2) <> 0 and
round(sum({@NonCash,{transdate}),2) <> 0

-LB

 

[arodri]

I think you were right about the decimals because the rounding worked perfectly.

Thank you so much for your help!