Record Selection makes Summaries disappear in RF

[Jimdeb03]

I have four summaries in the Report Footer that work fine. They're all Sums of various Report Fields. (WAGES_GROSS, WAGES_CONTR, WAGES_LOANP, WAGES_ARERS)

However, if I add a condition in the Record Selection that tests one of the above mentioned Report Fields, (if WAGES_GROSS > 0), the other Summaries fail to display.

I'm just concerned if WAGES_GROSS > 0. (We don't want to display records where WAGES_GROSS is zero.) The other Report Fields can be greater than or equal to zero.

I've tried different permutations of testing these Report Fields in the Record Selection but the results are all or nothing.

How, or why does a condition in the Record Selection have an affect on Summaries?

Am I forced to scrap the Summaries and construct Formulas in place of each?

Or do I need to setup a formula in place of the Record Selection so the report only displays WAGES_GROSS > 0

 

[lbass]

Adding the criterion:

WAGES_GROSS > 0

...to the record selection formula should not result in a blank report. I think you should go to report->selection formula->record and copy your record selection formula and paste it into the thread.

Also, are your summaries simply inserted sums?

-LB

 

[Jimdeb03]

Adding WAGES_GROSS > 0 to report selection doesn't result in a blank report. It merely prevents the other three summaries in the Report Footer from displaying.

Summaries are simple Sums

Record Selection formula...
{SVC_Retirement;1.WAGES_GROSS} > 0;

This one line makes Summaries for WAGES_CONTR, WAGES_LOANP, WAGES_ARERS empty. We aren't concerned if these three report fields are zero or not. Whatever their Sum, they should display.

If Record Selection is...
{SVC_Retirement;1.WAGES_GROSS} > 0
and
{SVC_Retirement;1.WAGES_CONTR} >= 0;
None of the summaries display

But if Record Selection is...
//{SVC_Retirement;1.WAGES_GROSS} > 0
//and
{SVC_Retirement;1.WAGES_CONTR} >= 0;
Summary for WAGES_CONTR display

So this line makes Summary for WAGES_CONTR display...
{SVC_Retirement;1.WAGES_CONTR} >= 0;
(Remember, WAGES_CONTR can be greater than or equal to zero)

AND this line makes Summary for WAGES_GROSS display...
{SVC_Retirement;1.WAGES_GROSS} > 0

...but together none of the Summaries display.

 

[lbass]

I think the problem is that the record selection criteria requires PER RECORD that both wages_gross and wages_contr be greater than 0. My guess is that these are two different sets of employees, one set that has regular wages, and one with contract wages, so that wages_gross appears for one set of employees, and wages_contr, for another set. If you want to show both kinds of wages where the wages are greater than 0, then use "or" instead of "and" in the record selection formula.

-LB

 

[Jimdeb03]

Only wages_gross needs to be greater than 0. wages_contr can be equal to or greater than zero.

Each employee, (record) has WAGES_GROSS, WAGES_CONTR, WAGES_LOANP and WAGES_ARERS. WAGES_GROSS has to be greater than zero in the report, thus the reason for the Record Selection. If WAGES_GROSS isn't greater than zero, then that employee should not be in the report. The other three can be equal to or greater than zero

If I try the OR instead of 'AND' like this...
{SVC_Retirement;1.WAGES_GROSS} > 0
or
{SVC_Retirement;1.WAGES_CONTR} >= 0;
then just the Summary for WAGES_GROSS displays.

I'm currently pondering editing the SPROC to exclude all records where WAGES_GROSS equals 0 because implementing the Record Selection as I'd like isn't working as planned.

 

[lbass]

Please show a sample of your data at the detail level, with fields labeled.

Can you also clarify whether your record selection formula contains any other clauses?

-LB

 

[Jimdeb03]

Record Selection formula contain no other clauses

5.11 Details Section
{SVC_Retirement;1.WAGES_GROSS}
{SVC_Retirement;1.WAGES_CONTR}
{SVC_Retirement;1.WAGES_LOANP}
{SVC_Retirement;1.WAGES_ARERS}
{SVC_Retirement;1.PAYROLCD}

DETAIL Section
RetireNo PAYROLCD WAGES_GROSS WAGES_LOANP
D B 1198.63
D H 178.40
D L 27.00
GF3 12500 1377.03 27.00

D B 0.00
D H 0.00
D L 0.00 5.00
GF3 12600 0.00 5.00
RF 1377.03 27.00

RetireNo 12600 has 0.00 Wages_gross and should not show in report
In RF, 1377.03 and 27.00 are Summaries per WAGES_GROSS and WAGES_LOANP respectively
GF3 is visible
RF is visible via user entered Parameter

Set Record Selection...
{SVC_Retirement;1.WAGES_GROSS} > 0
and only Summary for WAGES_GROSS appears

If I create a formula like...
WhilePrintingRecords;
Sum({SVC_Retirement;1.WAGES_LOANP});
...and place in RF or PF, the above Record Selection makes this formula not appear too.
Comment out above Record Selection and this formula works fine

 

[lbass]

I think this must be a problem with the stored procedure, and it probably would make sense to add the record selection to the SP. I can't think of why else this behavior would occur.

-LB

 

[Jimdeb03]

The problem isn't in the stored procedure and unfortunately, the solution isn't either. Originally I thought the solution would be in the sproc, but not without a major overhaul.

I discovered why the Summaries and Formulas were failing to show their result when the Record Selection was set to WAGES_GROSS>0. It's so obvious.

When WAGES_GROSS = 0, the record is filtered out of the report. But this record also held positive, legit values for WAGES_LOANP, WAGES_CONTR, WAGES_ARERS. Filter out these positive values and obviously the Summaries aren't going to Summarize because there is nothing to summarize.

So I want to Filter out WAGES_GROSS = 0 only if WAGES_LOANP, WAGES_CONTR, WAGES_ARERS are zero too.

Unfortunately, I cannot blindly filter on these fields, at least in Record Selection, and expect the correct result. Because it's the SUM of WAGES_GROSS that is the key.

So let's say I SUM WAGES_GROSS per RetireNo in a formula. Depending on the SUM, it sets a boolean flag whether it returned a zero or something positive. (If SUM WAGES_GROSS is zero, we don't want to display this record per RetireNo)

How can I write said formula so it can be used in Record Selection?

Because in the Formula Workshop under Record Selection, why do only some Formulas appear in the Field Tree and not all? What criteria does CR put upon a Formula if it's available for use in a Record Selection.

 

[lbass]

Which is the equivalent of what I was trying to say 11 Feb 08 10:49, although I had it wrong about them being different employees.

But you are saying two different things in your last post--

So I want to Filter out WAGES_GROSS = 0 only if WAGES_LOANP, WAGES_CONTR, WAGES_ARERS are zero too

and

If SUM WAGES_GROSS is zero, we don't want to display this record per RetireNo

If you want to filter based on the latter, then go to report->selection formula->GROUP and enter:

sum({SVC_Retirement;1.WAGES_GROSS},{SVC_Retirement.RetireNo})

...assuming you have a group on RetireNo. Then if you need to do calculations across groups, you need to use running totals, since non-group selected records will contribute to the more usual inserted summaries.

-LB

 

[Jimdeb03]

I get the results I want when this is put into Group Selection...
if (sum({SVC_Retirement;1.WAGES_GROSS},{SVC_Retirement;1.RetireNo})>0) then
true
else
false

The record per RetireNo that has a zero sum of WAGES_GROSS is not in the report.
However, this record is reflected in the Distinct Count Summary that is in the RF.
In other words, the Distinct Count Summary continues to count the Group Selection (RetireNo) that is hidden by the above mentioned Group Selection.

The same occurs when a Formula is created and put into RF
DistinctCount ({SVC_Retirement;1.ClockNo})

 

[lbass]

I see I forgot to finish the expression with the '> 0'. You don't need to use if/then true/false. You can just use:

sum({SVC_Retirement;1.WAGES_GROSS},{SVC_Retirement.RetireNo}) > 0

As I said in my last post, you have to use running totals instead of the more usual inserted summaries when you are using group selection. You would just set up the running totals to sum the field, evaluate for each record, reset never, and then place them in the report footer.

-LB

 

[Jimdeb03]

Added running totals, now the report is perfect! Thanks for your help