[SOLVED] Selected Suppressing of data values in a column
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 ***
August 02, 2013, 10:21 AM
Prarie
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
In Focus since 1993. WebFOCUS 7.7.03 Win 2003
August 02, 2013, 10:26 AM
Ian Dalton
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 ***
August 02, 2013, 10:46 AM
Dave
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
August 02, 2013, 05:13 PM
David Briars
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
Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster
August 02, 2013, 05:50 PM
David Briars
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
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 ***
August 05, 2013, 10:04 AM
David Briars
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
Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster
August 05, 2013, 10:52 AM
Doug
quote:
total is actually zero, they need this to be 0.00 and NOT blank as the format of D12.2S suppresses this
EDIT your results into alpha and make it either the value (when not missing) or "0.00" when it is missing.
August 05, 2013, 12:43 PM
Ian Dalton
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 ***
November 27, 2013, 07:41 AM
Ian Dalton
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 ***
November 27, 2013, 07:52 AM
Dave
Try adding:
SET EXCELSRVURL = ''
to the code. ( before the table request )
g'luck
_____________________ WF: 8.0.0.9 > going 8.2.0.5
November 27, 2013, 08:03 AM
Ian Dalton
quote:
SET EXCELSRVURL = ''
Tried the above but made no difference. It's this line that causes the problem... TESTER/MYFMT AS 'VARFORMAT' Ian
_______________________ *** WebFOCUS 8.1.05M ***
November 27, 2013, 10:24 AM
Tony A
Ian,
It crashes an agent in 7.7.03M for EXL07 so I would suggest giving RS a call!
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
November 27, 2013, 10:29 AM
Ian Dalton
Yes I've raised a case Tony. It works fine for FORMAT EXL2K however.
_______________________ *** WebFOCUS 8.1.05M ***
November 28, 2013, 12:44 PM
susannah
does the variable format work if you create a hold file first, and then dump to excel?
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
November 29, 2013, 09:12 AM
Ian Dalton
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 ***
December 04, 2013, 09:14 AM
Ian Dalton
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