|
| Hi all
Using PB 9.x and have the following issue with a simple report datawindow
described below.
Product Price Credit Limit Other...
------------------------------------------------
Widget 10.00 0 25.00
Foobah 20.00 200 10.00
Goober 15.00 200 20.00
Order total 45.00 *200* 55.00 << credit limit
here is MAX(credit_limit), which is correct
Widget 15.00 100 25.00
Foobah 25.00 0 10.00
Goober 11.00 100 20.00
Order total 51.00 *100* 55.00
Report total 96.00 900 110.00
(Wanted) 96.00 300 110.00
The data is kind of odd with this report, in that the "credit limit" is
actually an "order" level value. Due to the rather complex SQL behind this
report, the credit_limit will show up associated with n product detail rows
on the order (at least one of them). It will always be the same value
within an order if it exists. To ensure I use it only once, the credit
limit on the "Order total" line is MAX(credit limit) for group 2 (inner
group). I call this calculated column calc_order_level_credit.
The funky part happens when I try to roll up calc_order_level_credit to
group 1 (outer group). There doesn't seem to be a way to force Powerbuilder
to perform a SUM on the calculated field value itself. Instead the
calc_order_level_credit column just seems to be a pointer to the original
credit_limit column, and the SUM is performed on all detail row values for
that column. In fact, the final sum includes not only each detail row value,
but also adds on the group 2 aggregate value (from the
calc_order_level_credit column) as well.
What I'd like to find is a way to force PB to SUM a calced field value,
rather than detail rows underlying the field. I am pretty sure I've done
this type of thing before in older versions of PB. I have tried a variety
of workarounds, but none seem to work. Suggestions appreciated.
Cheers
Gary
|
|