 |
|||||
|
As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Join the TIBCO Community
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community. | |||||
Focal Point 
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CLOSED] Complex subtotals question
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CLOSED] Complex subtotals question Read-Only TopicGo | Search | Notify | Tools |
| [CLOSED] Complex subtotals question | Login/Join |
| Gold member |
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, WebFOCUS 8.0.08 Solaris HTML, PDF, XLS, CSV | ||
|
| Expert |
English is my first language, but I don't understand this! Francis  Give me code, or give me retirement. In FOCUS since 1991 Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server | |||
|
| Guru |
 Even me too. English is my second language though  Thanks, Ramkumar. WebFOCUS/Tableau Webfocus 8 / 7.7.02 Unix, Windows HTML/PDF/EXCEL/AHTML/XML/HTML5 | |||
|
| Gold member |
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 WebFOCUS 8.0.08 Solaris HTML, PDF, XLS, CSV | |||
|
| Platinum Member |
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 In FOCUS since 1985 - WF 8.009/8.104 Win 8 Outputs: ALL of 'em! Adapters: Sql Server Teradata Oracle | |||
|
| Gold member |
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. WebFOCUS 8.0.08 Solaris HTML, PDF, XLS, CSV | |||
|
| Platinum Member |
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. In FOCUS since 1985 - WF 8.009/8.104 Win 8 Outputs: ALL of 'em! Adapters: Sql Server Teradata Oracle | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CLOSED] Complex subtotals question
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CLOSED] Complex subtotals questionCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |