Focal Point Banner


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
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

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  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Selected Suppressing of data values in a column

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Selected Suppressing of data values in a column
 Login/Join
 
Platinum Member
posted
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 ***
 
Posts: 196 | Location: London, UK | Registered: December 06, 2005Report This Post
Virtuoso
posted Hide Post
 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
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Platinum Member
posted Hide Post
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 ***
 
Posts: 196 | Location: London, UK | Registered: December 06, 2005Report This Post
Master
posted Hide Post
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
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Master
posted Hide Post
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
 
Posts: 822 | Registered: April 23, 2003Report This Post
Master
posted Hide Post
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
  
 
Posts: 822 | Registered: April 23, 2003Report This Post
Platinum Member
posted Hide Post
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 ***
 
Posts: 196 | Location: London, UK | Registered: December 06, 2005Report This Post
Master
posted Hide Post
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
 
Posts: 822 | Registered: April 23, 2003Report This Post
Expert
posted Hide Post
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.
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Platinum Member
posted Hide Post
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 ***
 
Posts: 196 | Location: London, UK | Registered: December 06, 2005Report This Post
Platinum Member
posted Hide Post
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 ***
 
Posts: 196 | Location: London, UK | Registered: December 06, 2005Report This Post
Master
posted Hide Post
Try adding:

SET EXCELSRVURL = ''

to the code. ( before the table request )

g'luck


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Platinum Member
posted Hide Post
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 ***
 
Posts: 196 | Location: London, UK | Registered: December 06, 2005Report This Post
Expert
posted Hide Post
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 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Platinum Member
posted Hide Post
Yes I've raised a case Tony. It works fine for FORMAT EXL2K however.


_______________________
*** WebFOCUS 8.1.05M ***
 
Posts: 196 | Location: London, UK | Registered: December 06, 2005Report This Post
Expert
posted Hide Post
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
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Platinum Member
posted Hide Post
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 ***
 
Posts: 196 | Location: London, UK | Registered: December 06, 2005Report This Post
Platinum Member
posted Hide Post
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 ***
 
Posts: 196 | Location: London, UK | Registered: December 06, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Selected Suppressing of data values in a column

Copyright © 1996-2020 Information Builders