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] Custom Row Total on Table in AppStudio
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Custom Row Total on Table in AppStudio
 Login/Join
 
Platinum Member
posted
Hello everyone.

I've probably overlooked the solution somewhere on this forum. For that, I apologize. However, I'm trying to create a custom row total.

I have seven columns in my actual report, and I only want to total six of them. I found some documentation about CNOTATION. I thought it might be helpful; however, it seems to create a running total for each column. Below is some sample code I worked with in one of the demo files. When it's run, you'll see what I mean.

SET CNOTATION = ALL


TABLE FILE SALES
SUM
     SALES.PRODUCT.RETAIL_PRICE
AND COMPUTE SSPRICE=C1+C2;
BY  SALES.STOR_SEG.STORE_CODE
ACROSS LOWEST SALES.PRODUCT.PROD_CODE
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,
$
TYPE=DATA,
     ACROSSCOLUMN=N1,
$
TYPE=TITLE,
     ACROSSCOLUMN=N1,
$
ENDSTYLE
END  


What I really need is for each prod code EXCEPT product code E3 to be computed into a total.

Can someone give me a gentle hint or point me in the right direction as to what I might be missing?

By way of example, here's another take on what I want to do:

If I have seven categories running across the top of table, I only want to total the first six columns.

Thank you in advance as always for your help or guidance.

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


WebFocus 8.2.04
WebFocus 8.2.04

 
Posts: 182 | Registered: September 18, 2015Reply With QuoteReport This Post
Expert
posted Hide Post
Search the forum for the "SEQUENCE" keyword.

It might not be straight forward with TITLE positioning though Frowner

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: 5686 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Thank you, Tony, for that.

Upon further reflection,I am not sure the CNOTATION is my preferred method after all.

Basically, in plain language what I'm trying to do is the following:

I have seven columns of data.

Perhaps, the columns are: Apples, Bananas, Oranges, Strawberries, Blueberries, Strawberries and Kiwi.

My BY field is the year.

My verb request is SUM with RPCT (row percentage) on it.

I want the total percentage to calculate the percentage for each column.

However, when I display the table, I don't want to display KIWI, for example.

If I eliminate it through a WHERE statement, of course, the percentages of the other fruits will get recalculated.

Any other advice?


WebFocus 8.2.04
WebFocus 8.2.04

 
Posts: 182 | Registered: September 18, 2015Reply With QuoteReport This Post
Master
posted Hide Post
If I understand your scenario correctly:
* Matrix report
* Row % Calculations
* Hide one set of columns in the matrix at display time.

Sounds like a combination of the HIDENULLACRS setting and WHERE TOTAL might be able to help.
SET HIDENULLACRS=ON
-*
DEFINE FILE GGSALES
 YEAR/YY = IF SEQ_NO LT 2500 THEN 2019 ELSE 2020;
 MYDOLLARS/D12.2C = DOLLARS;
END
-*
TABLE FILE GGSALES
"Total/Row % Sales Across Region By Year"
"Hiding Midwest Region"
SUM MYDOLLARS
    RPCT.MYDOLLARS
ACROSS REGION
BY YEAR
WHERE TOTAL REGION NE 'Midwest'
ON TABLE SET STYLE *
  INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/jellybean_combo.sty, $
ENDSTYLE
END  





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: 808 | Registered: April 23, 2003Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Step 1
Extract and perform your total value for all fruits (including Kiwi) by Year.
Should end up with one record per Year with two columns : Year & TotalValue

Step 2
Extract detailed data per Year & Fruits EXCEPT Kiwi (WHERE Fruit NE 'Kiwi')
Should end up with one record per Year/Fruit with three columns : Year, Fruit & DetTotValue

Step 3
Join Total file (step 1) with detailed one (step 2)
In other words you should end up with a files having as many rows as you have in detailed file but where TotalValue will be added as a new column for each Year/Fruits rows
Should end up with one record per Year/Fruit with FOUR columns : Year, Fruit, DetTotValue, TotalValue

Step 4
Use Step 3 file to perform your percentage of Total

SUM COMPUTE Pct/P8.2C% = DetTolValue / TotalValue * 100;
BY Year
ACROSS Fruit

Or perform something similar to this Smiler


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: 2348 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Thank you, David, for that answer.

Perhaps I am not communicating clearly enough. (Sometimes communication can be the main challenge here on the forum. LOL.)

Your solution is very close, and I have gotten that part. Here's my true question though:

If I use a ROW-TOTAL at this point, will I still get 100% for a total? I don't want 100%.

I want 76.7%, the value of the three regions you've displayed in the final report as my total rate for the regions. Does that make sense?

Other than that, I think the solution is great, and it's pretty much what I had. So, it's wonderful knowing I understood more than I thought. Smiler


WebFocus 8.2.04
WebFocus 8.2.04

 
Posts: 182 | Registered: September 18, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
MartinY,

Your method makes sense, and I was trying something similar. Evidently, I might have missed a step though. Before I start down this path again, my question is will step 3 result in the true percentage? That is, if three fruits accounted for 76.2% of the total and the final fruit (Kiwi) accounted for 23.8%, will step 3 result in 76.2% instead of 100%. I want the 76.2%. If I understand your solution correctly, I think it should. Am I correct in that assessment?


WebFocus 8.2.04
WebFocus 8.2.04

 
Posts: 182 | Registered: September 18, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
No percentage calculation at Step 3, only summarize the value by Year/Fruit to then be able to perform the percentage

In other word, you may have several sales rows per Year and Fruit but what you are interested by it's the total sales per Year/Fruit

So in Step 1 you will end up with
Yr 2017 = 1000 $ of Total sales
Yr 2018 = 2000 $ of Total sales
Yr 2019 = 1500 $ of Total sales

Step 2
Yr 2017 Apples = 300 $ of sales
Yr 2017 Bananas = 500 $ of sales
Yr 2017 Kiwi = 200 $ of sales -- but this record is omitted by the where clause
Yr 2018 Apples = 1300 $ of sales
Yr 2018 Bananas = 200 $ of sales
Yr 2018 Kiwi = 500 $ of sales -- but this record is omitted by the where clause
Yr 2019 Apples = 800 $ of sales
Yr 2019 Bananas = 600 $ of sales
Yr 2019 Kiwi = 100 $ of sales -- but this record is omitted by the where clause

Step 3
Yr 2017 Apples = 300 $ of sales, TotalSales 1000 $
Yr 2017 Bananas = 500 $ of sales, TotalSales 1000 $
Yr 2018 Apples = 1300 $ of sales, TotalSales 2000 $
Yr 2018 Bananas = 200 $ of sales, TotalSales 2000 $
Yr 2019 Apples = 800 $ of sales, TotalSales 1500 $
Yr 2019 Bananas = 600 $ of sales, TotalSales 1500 $

Step 4
Yr 2017 Apples Pct = 300 / 1000 * 100
Yr 2017 Bananas Pct = 500 / 1000 * 100
Yr 2018 Apples Pct = 1300 / 2000 * 100
Yr 2018 Bananas Pct = 200 / 2000 * 100
Yr 2019 Apples Pct = 800 / 1500 * 100
Yr 2019 Bananas Pct = 600 / 1500 * 100


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: 2348 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Master
posted Hide Post
quote:
...If I use a ROW-TOTAL at this point, will I still get 100% for a total? I don't want 100%.

I want 76.7%, the value of the three regions you've displayed in the final report as my total rate for the regions...


Oh, didn't see the need for a row-total of the 'non-hidden' percentages.

Here is a variation of my previous model..adding a check report to support data checking during development..


DEFINE FILE GGSALES
 YEAR/YY = IF SEQ_NO LT 2500 THEN 2019 ELSE 2020;
 MYDOLLARS/D12.2C = DOLLARS;
END
-*
TABLE FILE GGSALES
SUM    RPCT.MYDOLLARS/D12.2% AS 'RPCT'
ACROSS REGION 
BY     YEAR
WHERE TOTAL REGION NE 'Midwest';
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD FORMAT FOCUS
END
-RUN
-*
TABLE FILE HOLD
"Total/Row % Sales Across Region By Year"
"Hiding Midwest Region"
PRINT YEAR           AS 'Year'
      RPCTNortheast  AS 'Northeast'
	  RPCTSoutheast  AS 'Southeast'
	  RPCTWest       AS 'West'	                       
COMPUTE ROW_TOTAL/D12.2% = RPCTNortheast + RPCTSoutheast + RPCTWest; AS 'Row,Total'
ON TABLE SET STYLE *
  INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/jellybean_combo.sty, $
ENDSTYLE
END
-*
TABLE FILE GGSALES
"CHECK"
SUM    RPCT.MYDOLLARS AS 'RPCT'
ACROSS REGION ACROSS-TOTAL 
BY     YEAR              
ON TABLE SET STYLE *
  INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/jellybean_combo.sty, $
ENDSTYLE
END  

For sure, with the power and flexibility of the language, there are probably several approaches. :-) Have fun!
 
Posts: 808 | Registered: April 23, 2003Reply With QuoteReport This Post
Expert
posted Hide Post
Forgive me if I'm taking this to the Top/Initial Post. But, is the real question (based on: "I have seven columns in my actual report, and I only want to total six of them"): How do I only show totals for 6 of the 7 columns? If so, Answer: ON {something} SUBTOTAL COL1 COL2 COL3 COL4 COL5 COL7
 
Posts: 3106 | Location: Tennessee, Nashville area | Registered: February 23, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
I think this is what Julie requires, but there's an issue with column titles.

An alternative might be to create a separate bucket for each across value.

DEFINE FILE GGSALES
  PARTIAL_ROW/D12c = IF PRODUCT EQ 'Capuccino' THEN 0 ELSE DOLLARS;
END

TABLE FILE GGSALES
-* Firstly aggregate values at STCD level
SUM PARTIAL_ROW
-* Uncomment the next line to enable comparison between partial and full row-totals
-*	DOLLARS AS 'ROW-TOTAL'
BY STCD
-* Secondly aggregate your required content
SUM DOLLARS
BY STCD
ACROSS PRODUCT AS ''
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,
$
-* Thirdly, place the first "non-BY" report column in the final column position
TYPE=REPORT, COLUMN=C1, SEQUENCE=14, $
ENDSTYLE
END
-RUN


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: 5686 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Virtuoso
posted Hide Post
JulieA said:
quote:
I have seven columns in my actual report, and I only want to total six of them.

I understand that Julie wants to show all the columns but sum only 6.
Using David's example and the column-notation:
  
-SET &ECHO=ALL;
-* File juliea01.fex
DEFINE FILE GGSALES
 YEAR/YY = IF SEQ_NO LT 2500 THEN 2019 ELSE 2020;
 MYDOLLARS/D6.2% = DOLLARS;
 DOLLARS/D8 = DOLLARS;
END
-*
TABLE FILE GGSALES
"Total/Row % Sales Across Region By Year"
"Excluding Midwest Region from the Total"
SUM DOLLARS
    RPCT.MYDOLLARS
ACROSS REGION COMPUTE PCTROWTOTAL/D6.2%=C4 + C6 + C8; AS 'PCT TOTAL'
BY YEAR
ON TABLE SET STYLE *
  INCLUDE = endeflt, $
ENDSTYLE
END  


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

 
Posts: 1961 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Everyone,

Oh my goodness! There are so many wonderful ideas and solutions, and I am extremely grateful to all of you who posted replies to my question.

In the end, I selected David Briars' approach. My supervisor liked it very much.

As always, thank you for sharing your expertise with me. I've kept notes on all these techniques. You never know when they might come in handy on another project.


WebFocus 8.2.04
WebFocus 8.2.04

 
Posts: 182 | Registered: September 18, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Everyone,

While the solution David Briars provided works great and I appreciate the help, we'd like to add one more component-a where clause to eliminate records where the seventh column (RPCT7) is 100%.

I read elsewhere on this forum about creating another column with NOPRINT. Then, you can use that column for a where condition. The solutions indicate I'd have to change the format from a Print/Display Only format to something else. Yet, whenever I try to remove the PRINT and PRINTLISTONLY and save as a file like this HOLD AS (NAME) format focus, I receive an error message about no output.

So, clearly I'm missing something still.

Thoughts?


WebFocus 8.2.04
WebFocus 8.2.04

 
Posts: 182 | Registered: September 18, 2015Reply With QuoteReport This Post
Guru
posted Hide Post
you don't need an extra column, just add a WHERE TOTAL:

 TABLE FILE HOLD
"Total/Row % Sales Across Region By Year"
"Hiding Midwest Region"
PRINT YEAR           AS 'Year'
      RPCTNortheast  AS 'Northeast'
	  RPCTSoutheast  AS 'Southeast'
	  RPCTWest       AS 'West'	                       
COMPUTE ROW_TOTAL/D12.2% = RPCTNortheast + RPCTSoutheast + RPCTWest; AS 'Row,Total'
WHERE TOTAL ROW_TOTAL NE 100
END 


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 441 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Thank you for the reply.

Unfortunately, it's not quite what I'm looking for. Perhaps if I rephrase the question, it might help.

I only want to eliminate records when only the hidden column (in this case, it's known as RPCT7) results in 100 percent not the entire row total. The entire row total is always going to be 100 percent.

Would I just rephrase your solution
WHERE TOTAL ROW_TOTAL NE 100  

to be
 WHERE TOTAL RPCT7 NE 100  


WebFocus 8.2.04
WebFocus 8.2.04

 
Posts: 182 | Registered: September 18, 2015Reply With QuoteReport This Post
Master
posted Hide Post
quote:
...eliminate records when only the hidden column...results in 100 percent...

If the 'hidden column' (Midwest in this case) is 100 percent, wouldn't the sum of the other three columns be 0 percent?

So then, perhaps, add?:
WHERE TOTAL ROW_TOTAL NE 0;
 
Posts: 808 | Registered: April 23, 2003Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Custom Row Total on Table in AppStudio

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.