Focal Point
[RE-OPENED] Custom Row Total on Table in AppStudio

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/3217054396

May 20, 2020, 10:00 AM
JulieA
[RE-OPENED] Custom Row Total on Table in AppStudio
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: JulieA,


WebFocus 8.2.04
WebFocus 8.2.04

May 20, 2020, 12:33 PM
Tony A
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 
May 20, 2020, 01:00 PM
JulieA
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

May 20, 2020, 04:32 PM
David Briars
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
May 20, 2020, 04:41 PM
MartinY
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
May 20, 2020, 04:44 PM
JulieA
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

May 20, 2020, 04:50 PM
JulieA
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

May 20, 2020, 05:04 PM
MartinY
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
May 20, 2020, 06:34 PM
David Briars
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!
May 20, 2020, 06:49 PM
Doug
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
May 21, 2020, 03:08 AM
Tony A
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 
May 21, 2020, 07:15 AM
Danny-SRL
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

May 21, 2020, 12:52 PM
JulieA
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

June 11, 2020, 09:40 AM
JulieA
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

June 18, 2020, 11:55 AM
Frans
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.
June 18, 2020, 12:16 PM
JulieA
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

June 19, 2020, 05:45 PM
David Briars
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;