Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Grand Total with Condition - Is it possible?
Go
New
Search
Notify
Tools
Reply
  
[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, 2017Reply With QuoteReport 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: 1716 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport 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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2233 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport 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.




Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2012/Tomcat Standalone Workstation: Windows 7/IE 11 Database: Oracle 12c/Netezza Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer, & ReportCaster

 
Posts: 780 | Registered: April 23, 2003Reply With QuoteReport 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, 2017Reply With QuoteReport 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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2233 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
  Powered by Social Strata  
 

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-2018 Information Builders, leaders in enterprise business intelligence.