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     [Workaround] GRANDTOTAL Drill Down (Part 2)

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[Workaround] GRANDTOTAL Drill Down (Part 2)
 Login/Join
 
Dennis Smid
Member
posted
To all,

Here is another question about drill-downs on the GRANDTOTAL row. I am trying to pass the cell value of the total row to the drill-down fex. See following, which seems like the logical approach:

TABLE FILE CAR
SUM CNT.CAR
BY MODEL
WHERE COUNTRY EQ 'ENGLAND'
ON TABLE SUMMARIZE
ON TABLE SET STYLE *
TYPE=GRANDTOTAL, COLUMN=N2,FOCEXEC=dd(cellvalue=N2),$
END

However, the value that gets passed to the dd fex is taken from the last row of the matrix instead of total row. How do I pass the cell value from the total row?

Thanks again in advance.

Dennis

This message has been edited. Last edited by: Dennis Smid,


WebFOCUS 7.6.2
AIX
Output: HTML, AHTML, Excel, and PDF
 
Posts: 18 | Location: Omaha, NE | Registered: November 04, 2007Report This Post
Tom Flynn
Expert
posted Hide Post
Dennis,

If I need to use TOTAL lines, I create the ROW for that line, and, stay away from trying to using SUB-TOTAL, SUBTOTAL, GRANDTOTAL, etc. when I have to drill on those values. I realize this is just CAR, and, your data is probably much more complex, but, if you understand this concept, it "may" be easier for you in the long run.

Here's an example using your example. Granted, it IS more coding, and, a different philosophy, but...

  
SET ASNAMES=ON
TABLE FILE CAR
SUM 
    CNT.CAR AS 'XCNT'
  BY MODEL
WHERE COUNTRY EQ 'ENGLAND';
  ON TABLE HOLD AS H1_DET
END
-RUN
DEFINE FILE H1_DET
  XMODEL/A24 = 'Total';
END
TABLE FILE H1_DET
SUM
    XCNT
  BY XMODEL AS 'MODEL'
  ON TABLE HOLD AS H1_SUM
END
-RUN
TABLE FILE H1_DET
PRINT
  XCNT 
 BY MODEL
    ON TABLE HOLD AS H1_ALL
MORE
FILE H1_SUM
END
-RUN
DEFINE FILE H1_ALL
  XSORT/I2 = DECODE MODEL('Total' 99 ELSE 1);
END
TABLE FILE H1_ALL
PRINT
    XCNT
      COMPUTE DRILL_TOT/A1 = IF MODEL EQ 'Total' THEN 'Y' ELSE 'N'; NOPRINT
  BY XSORT NOPRINT
  BY MODEL
-*ON TABLE SUMMARIZE
ON TABLE SET STYLE *
TYPE=DATA, COLUMN=N3,FOCEXEC=dd(cellvalue=N3), WHEN=DRILL_TOT EQ 'Y',$
END
-EXIT


HTH

Tom

This message has been edited. Last edited by: Tom Flynn,


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Danny-SRL
Virtuoso
posted Hide Post
Tom,
You can use the McGuyver technique to produce your H1_ALL file with just one pass on the data.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Tom Flynn
Expert
posted Hide Post
Danny,

He has 12 posts, baby-steps...


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Danny-SRL
Virtuoso
posted Hide Post
Tom,

That's why I addressed it to you... Wink


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Dennis Smid
Member
posted Hide Post
To Danny/Tom,

I understand Tom's suggestion, and I had contemplated doing something similar. However, this seems to be a workaround to a bug: after all why should a GRANDTOTAL data type reference a cell from the body of the report? Does IBI concur this is a bug, or is there another solution?

Dennis


WebFOCUS 7.6.2
AIX
Output: HTML, AHTML, Excel, and PDF
 
Posts: 18 | Location: Omaha, NE | Registered: November 04, 2007Report This Post
Tom Flynn
Expert
posted Hide Post
Dennis,

Here's Noreen's excellent article on Macguyver:

The MacGuyver Technique

These are the two processes I use most often; depends on the audience..

Good Luck!

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Dennis Smid
Member
posted Hide Post
To all,

I raised a ticket with IBI. Apparently there was an earlier ticket raised about a year ago on exactly the same topic - 40242529. IBI's resolution was a multi-verb approach. As I see it, this is another workaround to add to the list. I still think there is a bug.

Dennis


WebFOCUS 7.6.2
AIX
Output: HTML, AHTML, Excel, and PDF
 
Posts: 18 | Location: Omaha, NE | Registered: November 04, 2007Report 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     [Workaround] GRANDTOTAL Drill Down (Part 2)

Copyright © 1996-2020 Information Builders