Is it possible to have subtotals like this?

MID 1 then row data
MID 1 then row data
MID 1 then row data etc.
Totals
9999999999 4 159.80 USD DR
0 -(00.00) USD CR
5 120.00 CAD DR
(only display rows if there is data to total)
1 -(50.00) CAD CR
Grand Total 4 159.80 USD
Grand Total 6 70.00 CAD


MID 2 then row data
MID 2 then row data
MID 2 then row data etc.
Totals
9999999999 4 159.80 USD DR
0 -(00.00) USD CR
5 120.00 CAD DR
(only display rows if there is data to total)
1 -(50.00) CAD CR
Grand Total 4 159.80 USD
Grand Total 6 70.00 CAD

So basically they want subtotals by MID.
The want the debit and credit subtotals to be seperate. And also the currency rows to be seperate with the debits and credits as well. And also a count of the number of transactions per line. And then a grand total per mid with the currency rows seperate with the debits and credits totals.

This message has been edited. Last edited by: Kerry,

English is my first language, but I don't understand this!

Even me too. English is my second language though

lol ok, I will try and explain it better. I only understood after seeing a mockup of what they wanted.

The mockup like this:
(column titles)
1.Case ID 2.Account No 3.MID 4.Chargeback Amount 5.Currency 6. Debit/Credit

(rows - data)
1. 1234 2. 333 3. 9999 4. 50.00 5. USD 6. DR
1. 1236 2. 234 3. 9999 4. 50.00 5. USD 6. DR
1. 1324 2. 444 3. 9999 4. (25.00) 5. USD 6. CR
1. 2322 2. 555 3. 9999 4. 61.00 5. CAN 6. DR

Subtotals
(by MID, rec count, amount, currency, debit/credit)
9999 2 100.00 USD DR
1 (20.00) USD CR
1 61.00 CAN DR
0 (0.00) CAN CR

Grand Totals
(by MID, rec count, amount, currency)
3 80.00 USD
1 61.00 CAN

Suzy,

While WebFocus does not easily allow rolled-up subtotals/subfoots at the bottom (or top) of a sort break and report footing. it can be done using a hard coded list of sort values and subtotals, subfoots, prefix operators if you are working with a known small list of sort values.

Since you are sorting by MID and it is 4 characters long, I'm assuming you could have many sort breaks. If so, you really need a dynamic method.

I found a way to do just what you are attempting using the McGyver technique. I've since created a Tips and Techinques document that shows you how to do it. You can find it here:MacGyver Dynamic Subtotals/SubFoots


If you are not familiar with the McGyver technique, you'll want to watch Noreen Reddin's webinar from DEC 2010, other McGyver Tips and Techniques documents, and help found here in the forum.

I'm also doing a Webinar next week on March 8 on this very subject. Check it out here: MacGyver Dynamic Subtotals/SubFoots


Let us know if you make headway.

David

Is this technique only available in 7.6.11 and later?

And the MID is not always a certain length, that was just an example.

It will work in any version of WebFocus/Focus.

This particular verison of the technique will leave blanks rows above/below the Subfoots/Subtotals. Our business requirement asked that the blank rows be removed. While there are techniques of varying complexity that remove blank rows found here on Focal Point, 7.6.11 offers the new SET command DROPBLNKLINE=ON which easily does it.

In your shop, though, the blank rows may be ok.

Regarding MID , the number of break values and whether that number is dynamic and unknown, or known and relatively small will help in determining if this technique would be useful for you.

If your example MID will always have, let's say 4 break values, and you know those 4 values will always be 0001, 0002, 0003, and 0004, then it may be easier to code defines and computes and add as spot marked prefixed fields in subfoots and footings.

If the number of break values is dynamic based on any number of prior parental sorts, selection criteria, etc then this technique should be for you.