Focal Point
[SOLVED] Selected Suppressing of data values in a column

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/1737095236

August 02, 2013, 10:14 AM
Ian Dalton
[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
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 ***
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
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




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
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
  

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 ***
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   





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
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.
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 ***
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 ***
Try adding:

SET EXCELSRVURL = ''

to the code. ( before the table request )

g'luck


_____________________
WF: 8.0.0.9 > going 8.2.0.5
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 ***
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 
Yes I've raised a case Tony. It works fine for FORMAT EXL2K however.


_______________________
*** WebFOCUS 8.1.05M ***
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
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 ***
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 ***