 |
|||||
|
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 [SOLVED] Selected Suppressing of data values in a column
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Selected Suppressing of data values in a column Read-Only TopicGo | Search | Notify | Tools |
| [SOLVED] Selected Suppressing of data values in a column | Login/Join |
| Platinum Member |
Re-opening this one as have further issues. Stupidly I wiped my original post but in essence it was to have suppressed row-totals except for the last row of each sort. David B. below came up with a good workable solution but the technique doesn't seem to work for FORMAT EXL07.This message has been edited. Last edited by: Ian Dalton, _______________________ *** WebFOCUS 8.1.05M *** | ||
|
| Virtuoso |
DEFINE FILE CAR PROFIT = RCOST - DCOST; NEWPROFIT/D12 = IF CAR EQ 'MASERATI' OR 'TRIUMPH' THEN PROFIT ELSE 0; END TABLE FILE CAR PRINT RCOST DCOST PROFIT AND COMPUTE CUMPROF = IF COUNTRY EQ LAST COUNTRY THEN NEWPROFIT + LAST CUMPROF ELSE NEWPROFIT; BY COUNTRY BY CAR WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY'; END
| |||||
|
| Platinum Member |
Nice try Prarie but I don't know what the last row's value is going to be. I just used the CAR file as an example. _______________________ *** WebFOCUS 8.1.05M *** | |||
|
| Master |
Ian, You gave the answer yourself: "I don't know what the last row's value is going to be". That's exactly the problem. WF doesn't whether it's the last BY within a major BY. I don't think you could fix it with one pass... There are serveral solutions with more that one pass. • First query, resort reverse, mark first row, rereverse and report. • First query the BY-fields, determine the 'last', match that to other dataset and use it as an indicator where to show the ***. • First query data per row. Second query only by major BY. Match there with PRINT LST. • etc. G'luck, Dave _____________________ WF: 8.0.0.9 > going 8.2.0.5 | |||
|
| Master |
Hi Ian, The following code additions to your model, might get you closer to your requirements: APP PREPENDPATH IBISAMP SET WIDTH = 200, PANEL=200 DEFINE FILE CAR PROFIT = RCOST - DCOST; END TABLE FILE CAR SUM SUM.PROFIT NOPRINT BY COUNTRY NOPRINT SUM LST.CAR WITHIN COUNTRY NOPRINT BY COUNTRY NOPRINT BY CAR NOPRINT PRINT RCOST DCOST PROFIT AND COMPUTE CUMPROF = IF COUNTRY EQ LAST COUNTRY THEN PROFIT + LAST CUMPROF ELSE PROFIT; AND COMPUTE IANPROF/D12.2S = IF LST.CAR EQ CAR THEN C1 ELSE 0; BY COUNTRY BY CAR WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY'; ON TABLE SET ONLINE-FMT STANDARD END -EXIT 1 PAGE 1
RETAIL_COST DEALER_COST PROFIT CUMPROF IANPROF
----------- ----------- ------ ------- -------
8,878 7,427 1,451.00 1,451.00
13,491 11,194 2,297.00 3,748.00
17,850 14,940 2,910.00 6,658.00
5,100 4,292 808.00 7,466.00 7,466.00
5,925 4,915 1,010.00 1,010.00
6,820 5,660 1,160.00 2,170.00
6,820 5,660 1,160.00 3,330.00
31,500 25,000 6,500.00 9,830.00 9,830.00 Regards, Dave
| ||||
|
| Master |
Similar technique, with the same results, that might align with your datasource better: APP PREPENDPATH IBISAMP
SET WIDTH = 200, PANEL=200
DEFINE FILE CAR
PROFIT = RCOST - DCOST;
END
TABLE FILE CAR
SUM PROFIT NOPRINT
CNT.BODYTYPE NOPRINT
BY COUNTRY
PRINT RCOST DCOST PROFIT
AND COMPUTE CUMPROF = IF COUNTRY EQ LAST COUNTRY THEN PROFIT + LAST CUMPROF ELSE PROFIT;
AND COMPUTE ROWNUM = IF COUNTRY EQ LAST COUNTRY THEN ROWNUM + 1 ELSE 1; NOPRINT
AND COMPUTE IANPROF/D12.2S = IF C2 EQ ROWNUM THEN C1 ELSE 0;
BY COUNTRY
BY CAR
BY BODYTYPE NOPRINT
WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY';
ON TABLE SET ONLINE-FMT STANDARD
END
-EXIT
1 PAGE 1
COUNTRY CAR RETAIL_COST DEALER_COST PROFIT CUMPROF IANPROF
------- --- ----------- ----------- ------ ------- -------
ENGLAND JAGUAR 8,878 7,427 1,451.00 1,451.00
13,491 11,194 2,297.00 3,748.00
JENSEN 17,850 14,940 2,910.00 6,658.00
TRIUMPH 5,100 4,292 808.00 7,466.00 7,466.00
ITALY ALFA ROMEO 6,820 5,660 1,160.00 1,160.00
6,820 5,660 1,160.00 2,320.00
5,925 4,915 1,010.00 3,330.00
MASERATI 31,500 25,000 6,500.00 9,830.00 9,830.00
| |||
|
| Platinum Member |
Hi David and Dave, Many thanks - both good workable solutions. Sadly nothing is simple as when I showed my results to our users they want the cumulative figure (IANPROF) to be shown as blank except for the last row per country (which we have) but when the ***. total is actually zero, they need this to be 0.00 and NOT blank as the format of D12.2S suppresses this. Ian _______________________ *** WebFOCUS 8.1.05M *** | |||
|
| Master |
Your users have a prefectly reasonable request for an amendment: APP PREPENDPATH IBISAMP
SET WIDTH = 200, PANEL=200
DEFINE FILE CAR
TAXRATE = DECODE COUNTRY ('ENGLAND' 1
'ITALY' 0);
PROFIT = RCOST - DCOST - ((RCOST - DCOST) * TAXRATE);
END
TABLE FILE CAR
SUM PROFIT NOPRINT
CNT.BODYTYPE NOPRINT
BY COUNTRY
PRINT RCOST DCOST PROFIT
COMPUTE CUMPROF = IF COUNTRY EQ LAST COUNTRY THEN PROFIT + LAST CUMPROF ELSE PROFIT;
COMPUTE ROWNUM = IF COUNTRY EQ LAST COUNTRY THEN ROWNUM + 1 ELSE 1; NOPRINT
COMPUTE MYFORMAT/A8 = IF C2 EQ ROWNUM THEN 'D12.2' ELSE 'D12.2S'; NOPRINT
COMPUTE IANPROF/D12.2C = IF C2 EQ ROWNUM THEN C1 ELSE 0; NOPRINT
IANPROF/MYFORMAT
BY COUNTRY
BY CAR
BY BODYTYPE NOPRINT
WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY';
ON TABLE SET ONLINE-FMT STANDARD
END
-EXIT 1 PAGE 1
COUNTRY CAR RETAIL_COST DEALER_COST PROFIT CUMPROF IANPROF
------- --- ----------- ----------- ------ ------- -------
ENGLAND JAGUAR 8,878 7,427 .00 .00
13,491 11,194 .00 .00
JENSEN 17,850 14,940 .00 .00
TRIUMPH 5,100 4,292 .00 .00 .00
ITALY ALFA ROMEO 6,820 5,660 1,160.00 1,160.00
6,820 5,660 1,160.00 2,320.00
5,925 4,915 1,010.00 3,330.00
MASERATI 31,500 25,000 6,500.00 9,830.00 9,830.00
| ||||
|
| Expert |
EDIT your results into alpha and make it either the value (when not missing) or "0.00" when it is missing. | |||
|
| Platinum Member |
Thanks chaps - both good suggestions - I was hoping that a variable format was a solution and appears it is - I knew it could be done with DM but was unsure using COMPUTE. My users will no doubt be thrilled !! _______________________ *** WebFOCUS 8.1.05M *** | |||
|
| Platinum Member |
We have just upgraded to WFS 7.7.05 and now get FORMAT EXL07 working correctly with multi-tab output but we are now finding that the variable format technique discussed earlier in this post/topic no longer works for FORMAT EXL07 - causes EDASERVE to crash. However for other output FORMATs all ok. Any suggestions ? Here is the code..... TABLE FILE CAR SUM RCOST DCOST COMPUTE MYFMT/A8 = IF RCOST GT 10000 THEN 'P15.2CL' ELSE 'P15.2CS'; NOPRINT COMPUTE TESTER/P15.2C = RCOST-DCOST; TESTER/MYFMT AS 'VARFORMAT' BY COUNTRY BY CAR ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT EXL07 -*ON TABLE PCHOLD FORMAT HTML END -EXIT _______________________ *** WebFOCUS 8.1.05M *** | |||
|
| Master |
Try adding: SET EXCELSRVURL = '' to the code. ( before the table request ) g'luck _____________________ WF: 8.0.0.9 > going 8.2.0.5 | |||
|
| Platinum Member |
Tried the above but made no difference. It's this line that causes the problem... TESTER/MYFMT AS 'VARFORMAT' Ian _______________________ *** WebFOCUS 8.1.05M *** | |||
|
| Expert |
Ian, It crashes an agent in 7.7.03M for EXL07 so I would suggest giving RS a call! T
| |||||||||
|
| Platinum Member |
Yes I've raised a case Tony. It works fine for FORMAT EXL2K however. _______________________ *** WebFOCUS 8.1.05M *** | |||
|
| Expert |
does the variable format work if you create a hold file first, and then dump to excel?
| |||||
|
| Platinum Member |
Sadly no Susannah because the HOLD file can only have one format per field. What I am trying to do is have a field format as either x or y depending on the contents of the field. _______________________ *** WebFOCUS 8.1.05M *** | |||
|
| Platinum Member |
I got this working by using MISSING rather than a variable FORMAT which according to IBI is NOT supported as yet with EXL07. Here is the workaround.... SET WIDTH = 200, PANEL=200, NODATA = ' ' DEFINE FILE CAR TAXRATE = DECODE COUNTRY ('ENGLAND' 1 'ITALY' 0); PROFIT = RCOST - DCOST - ((RCOST - DCOST) * TAXRATE); END TABLE FILE CAR SUM PROFIT NOPRINT CNT.BODYTYPE NOPRINT BY COUNTRY PRINT RCOST DCOST PROFIT COMPUTE CUMPROF = IF COUNTRY EQ LAST COUNTRY THEN PROFIT + LAST CUMPROF ELSE PROFIT; COMPUTE ROWNUM = IF COUNTRY EQ LAST COUNTRY THEN ROWNUM + 1 ELSE 1; NOPRINT COMPUTE IANPROF/D12.2C MISSING ON = IF C2 EQ ROWNUM THEN C1 ELSE MISSING; BY COUNTRY BY CAR BY BODYTYPE NOPRINT WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY'; ON TABLE PCHOLD FORMAT EXL07 END _______________________ *** WebFOCUS 8.1.05M *** | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Selected Suppressing of data values in a column
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Selected Suppressing of data values in a columnCopyright © 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 |