Help with Record Selection

[stormtrooper]

Hello there. I have deficit report in which I have to select facilities that have deficits, but, if updated without a deficit, not show up on the report. I'm running CR9 and data is from DB2.

Here is what the data would look like from DB2:

FAC_NO AMT LST_CHG_DT
12345 5000 2006-08-10
12345 -2345 2004-12-12
88888 -900 2006-07-30
88888 -1500 2005-10-31

Currently the record selection criteria on the report is as follows:

{AMT} < 0.00

The group selection criteria is as follows:

{LST_CHG_DT} = Maximum({LST_CHG_DT},{FAC_NO})

I know that this will only yield the negative amounts. I need to know if there is a way to select the facilities that only have a deficit amount and have not had an updated positive amount. (ie. facility 12345 would not show on the report and facility 88888 would show -900)

Thanks,

 

[synapsevampire]

I think you'll need to use conditional suppression for this.

Toss your grouo selection formula, and suppress the details and group header, then use the following 3 formula method for suppressing the group footer (which is where you'll display):

Gorup header:

whileprintingrecords;
booleanvar Suppressme := false


Details formula:

whileprintingrecords;
booleanvar Suppressme;
if {AMT} >= 0.00 then
Suppressme := true

Right click the group footer and select sectgion expert->X2 next to suppress and place:

whileprintingrecords;
booleanvar Suppressme;
Suppressme

So now whenever ther has been a positive value in that facility, the group footer will be suppressed, otherwise just have the rows sorted by the date and place your fields in the group footer and the last row (most recent date) will display if there haven't been any positive values.

-k

 

[lbass]

Move your formula:

{AMT} < 0.00

to the Group selection formula (add it to the existing formula there).

-LB

 

[stormtrooper]

Still not working (both methods)...

From the above data, the only record that would show on the report would be

88888 -900 2006-07-30

I'm thinking that a view may have to be created. Something I am trying to avoid.

Thanks

 

[synapsevampire]

You stated "I need to know if there is a way to select the facilities that only have a deficit amount and have not had an updated positive amount. (ie. facility 12345 would not show on the report and facility 88888 would show -900)".

Now you complain that the solution returned the data that you asked it to.

If you can build a View that reurns the proper data, I would do that, but the problem here is that you either don't know what you want or you can't explain it properly.

-k

 

[dpatrickcollins]

Stormtrooper,

In case you are wanting to see ALL TRANSACTIONS of facility 88888 (and those like it), place the following in the Group Selection Formula:

Maximum ({AMT}, {FAC_NO})<0

This will show all transactions, but only of those facilities that had no positive entries.

If you are wanting to exclude only "positive updates," meaning that the most recent entry by date was positive, then that is trickier. It is a little unclear what you are wanting, so ask if you need more details.