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     [CLOSED] Grand Total with Condition - Is it possible?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Grand Total with Condition - Is it possible?
 Login/Join
 
Member
posted
Hi All,

In my report, Table select, we do have an 3 levels of records. I want to do grand total with 2nd level only. But, I wanna to display all level records in my report. At the end to we need to display grandtotal for 2nd level.
is it possible?.

please suggest and share samples codes.

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8
Windows, All Outputs
 
Posts: 27 | Registered: December 14, 2017Report This Post
Virtuoso
posted Hide Post
Please consider showing what the raw data and the final output should look like. It's hard to envision what you're trying to accomplish.


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Virtuoso
posted Hide Post
Here is a manual way to do it.
You may not need files H2 and H3 since your data already has different type of records.
The goal here is to create the detailed file (H1+H2+H3) and a total file (H2T) with only total on what you want. In your case you will probably need a WHERE clause on record level 2 to create the total file.
Finally you will merge them together.
The result may look stupid, but just to illustrate the technic.
The important thing is that you need same field's name/format and number to be able to merge (MORE) files together.
TABLE FILE CAR
SUM SALES/D8.2 AS 'SLS'
BY TOTAL COMPUTE TOTID /I1 = 0;
BY TOTAL COMPUTE ROWID /I1 = 1;
BY TOTAL COMPUTE ROWTXT/A30V = 'Sales';
BY COUNTRY
ON TABLE HOLD AS H1
END
-RUN

TABLE FILE CAR
SUM SALES NOPRINT
    COMPUTE SLS/D8.2 = C1 / 365;
BY TOTAL COMPUTE TOTID /I1 = 0;
BY TOTAL COMPUTE ROWID /I1 = 2;
BY TOTAL COMPUTE ROWTXT/A30V = 'Avg Daily Sales';
BY COUNTRY
ON TABLE HOLD AS H2
END
-RUN

TABLE FILE CAR
SUM SALES NOPRINT
    COMPUTE SLS/D8.2 = C1 / 365;
BY TOTAL COMPUTE TOTID /I1 = 1;
BY TOTAL COMPUTE ROWID /I1 = 2;
BY TOTAL COMPUTE ROWTXT/A30V = 'Avg Daily Sales';
BY TOTAL COMPUTE COUNTRY/A10 = 'Total';
ON TABLE HOLD AS H2T
END
-RUN

TABLE FILE CAR
SUM SALES NOPRINT
    COMPUTE SLS/D8.2 = C1 / 12;
BY TOTAL COMPUTE TOTID /I1 = 0;
BY TOTAL COMPUTE ROWID /I1 = 3;
BY TOTAL COMPUTE ROWTXT/A30V = 'Avg Mth Sales';
BY COUNTRY
ON TABLE HOLD AS H3
END
-RUN

TABLE FILE H1
SUM SLS/D8.2CM AS ''
BY TOTID   NOPRINT
BY COUNTRY
BY ROWID   NOPRINT
BY ROWTXT  AS ''
MORE
FILE H2
MORE
FILE H2T
MORE
FILE H3
END
-RUN


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Master
posted Hide Post
quote:
...I want to do grand total with 2nd level only...

Take a look at the WHEN phrase.

The WHEN phrase allows you to conditionally display subtotals.




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
Member
posted Hide Post
Hi David,

I tried with the below conditional ON phrase for grand total - 2nd level.

 
TABLE FILE EMPLOYEE
SUM GROSS DED_AMT AND COMPUTE
DG_RATIO/F4.2=DED_AMT/GROSS;
BY HIGHEST PAY_DATE BY DEPARTMENT
BY BANK_ACCT
WHERE BANK_ACCT NE 0
ON DEPARTMENT SUMMARIZE MULTILINES AS '' WHEN DEPARTMENT EQ 'MIS'
END

 


The above mentioned code, In subtotal value is getting applied condition as based on department EQ 'MIS' record. But Similar condition, I want to apply for GrandTotal.

Any other way to use WHEN condition for GrandTotal.

This message has been edited. Last edited by: Veeramani Karuthapandian,


WebFOCUS 8
Windows, All Outputs
 
Posts: 27 | Registered: December 14, 2017Report This Post
Virtuoso
posted Hide Post
You cannot apply a condition on a Grand Total since, per definition, a Grand Total is applied on the whole data.
What you are requesting is a Sub-Total which is the total of a sub-part of the data. A condition can be applied at this level as already stated.

As far as I can understand your need, you want the 2nd level total placed at the end of the report. And this is why you call this a Grand Total which is not.
To perform such a thing, you should use the technic that I've posted already.

Here the whole code for you
TABLE FILE EMPLOYEE
SUM GROSS DED_AMT
    COMPUTE DG_RATIO/F4.2 = DED_AMT / GROSS;
BY TOTAL COMPUTE ROWID /I2 = 1;
BY PAY_DATE
BY DEPARTMENT
BY BANK_ACCT
WHERE BANK_ACCT NE 0;
ON TABLE HOLD AS DETDATA
END
-RUN

TABLE FILE EMPLOYEE
SUM GROSS DED_AMT
    COMPUTE DG_RATIO/F4.2 = DED_AMT / GROSS;
BY TOTAL COMPUTE ROWID /I2 = 99;
BY TOTAL COMPUTE PAY_DATE /I6YMD = 0;
BY DEPARTMENT
BY TOTAL COMPUTE BANK_ACCT /I9S = 0;
WHERE BANK_ACCT  NE 0;
WHERE DEPARTMENT EQ 'MIS';
ON TABLE HOLD AS TOTDATA
END
-RUN

TABLE FILE DETDATA
SUM GROSS DED_AMT
    DG_RATIO
BY ROWID
BY PAY_DATE
BY DEPARTMENT
BY BANK_ACCT
ON TABLE HOLD AS MRGDATA
MORE
FILE TOTDATA
END
-RUN

DEFINE FILE MRGDATA
PAY_DATEX /A11 = IF PAY_DATE NE 0 THEN FPRINT(PAY_DATE, 'I6YMD', 'A11') ELSE 'Total';
END
TABLE FILE MRGDATA
SUM GROSS DED_AMT
    DG_RATIO
BY ROWID NOPRINT
BY HIGHEST PAY_DATE NOPRINT
BY PAY_DATEX
BY DEPARTMENT
BY BANK_ACCT
END
-RUN


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report 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     [CLOSED] Grand Total with Condition - Is it possible?

Copyright © 1996-2020 Information Builders