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     [SOLVED] grouping multiple sum columns separately using across
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] grouping multiple sum columns separately using across
 Login/Join
 
Member
posted
In the code snippet below the SALQTY and SALAMT columns are together for each YM but is there a way to get all the YM for SALQTY and then all the YM for SALAMT?

TABLE FILE MISTRAL_PDA_META/MISTRAL_PDA_FS00_V01
SUM
     MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.SALQTY AS 'Qty'
     MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.SALAMT/P12 AS 'Sales @'
BY  MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.PARTNO AS 'Part'
ACROSS  MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.YM AS ''

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


8.2.01m
Windows, All Outputs
 
Posts: 10 | Registered: June 05, 2017Reply With QuoteReport This Post
Guru
posted Hide Post
Would this work for you?

 
TABLE FILE CAR
SUM 
COMPUTE YM_VALUE/P12=SALES;
COMPUTE YM/A20 = 'Sales';
BY CAR
BY COUNTRY
ON TABLE HOLD AS ACC1
END

TABLE FILE CAR
SUM 
COMPUTE YM_VALUE/P12=DEALER_COST;
COMPUTE YM/A20 = 'Cost';
BY CAR
BY COUNTRY
ON TABLE HOLD AS ACC2
END

TABLE FILE ACC2 
SUM YM_VALUE
BY CAR
ACROSS YM
ACROSS COUNTRY
MORE
FILE ACC1
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
Member
posted Hide Post
It has gotten me closer but I have 3 values so I modified your example but when I try to add COLUMNS to the ACROSS YM to do a custom order it crashes. I would like ACROSS COUNTRY to be ACROSS COUNTRY COLUMNS 'Qty' AND 'Sales @' AND 'Current SC' unless there is a different/better way to get the across in the proper order

TABLE FILE CAR
SUM 
COMPUTE YM_VALUE/P12=SALES;
COMPUTE YM/A20 = 'Qty';
BY CAR
BY COUNTRY
ON TABLE HOLD AS ACC1
END

TABLE FILE CAR
SUM 
COMPUTE YM_VALUE/P12=DEALER_COST;
COMPUTE YM/A20 = 'Sales @';
BY CAR
BY COUNTRY
ON TABLE HOLD AS ACC2
END

TABLE FILE CAR
SUM 
COMPUTE YM_VALUE/P12=RETAIL_COST;
COMPUTE YM/A20 = 'Current SC';
BY CAR
BY COUNTRY
ON TABLE HOLD AS ACC3
END

TABLE FILE ACC3
SUM YM_VALUE
BY CAR
ACROSS YM
ACROSS COUNTRY
MORE
FILE ACC1
MORE
FILE ACC2
END


8.2.01m
Windows, All Outputs
 
Posts: 10 | Registered: June 05, 2017Reply With QuoteReport This Post
Expert
posted Hide Post
Your code should be:

ACROSS YM COLUMNS 'Qty' AND 'Sales @' AND 'Current SC'
ACROSS COUNTRY 


YM contains the values you specify, not COUNTRY. And that correction works in 7.7.05.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
where's that LIKE button?




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Member
posted Hide Post
I have one last issue. Any place where there is missing data (records do not exist for all YM possibilities) they show up as blank and I need those to be actual zeroes instead of blanks. I have tried all different options related to no data and missing and none of them work. It seems like the across generates the missing data and we do not have control over it at that point. Any ideas/thoughts?


8.2.01m
Windows, All Outputs
 
Posts: 10 | Registered: June 05, 2017Reply With QuoteReport This Post
Expert
posted Hide Post
Hi Todd,
Have you tried this SET command:
SET HOLDMISS = ON


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1969 | Location: Centennial, CO | Registered: January 31, 2006Reply With QuoteReport This Post
Member
posted Hide Post
The problem is that the across is what generates the missing data so not sure how to do the across to generate and hold the missing data and then do the across again. Just trying to see if we can come up with any way to work around the missing data on the report side.


8.2.01m
Windows, All Outputs
 
Posts: 10 | Registered: June 05, 2017Reply With QuoteReport This Post
Member
posted Hide Post
Below is my sample code block. The issue is when I do the across at the end it generates the missing data that I need to be zeroes where they are missing. If there is a different/better way I am open to ideas.

-* File: IBFS:/Development/EDA/EDASERVE/APPPATH/ras2107/Procedure1.fex Created by WebFOCUS AppStudio
-*-DEFAULT WH_SEGMENT_CODE=FOC_NONE;
-*-DEFAULT &MU_CODE = 'HHH';
-*-DEFAULT &WH_SEGMENT_CODE = '''X0''';
-SET &ECHO = 'ALL';
-*SET XRETRIEVAL = OFF
SET TRACEOFF = ALL
SET TRACEON = STMTRACE
SET TRACEON = SQLTRANS
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACEUSER = ON
SET MESSAGE = ON
-*-INCLUDE mistral/mistral_initialize.fex
SET NODATA = '0'
SET EMPTYCELLS = OFF
SET BYDISPLAY = ON
-SET &CUR_YM = EDIT (&DATEYYMD,'9999$99$$$');
-SET &MONTH_PRIOR_SIXTY_YM = AYM(&CUR_YM, -24, 'I6YYM');
-SET &MONTH_NEXT_TWELVE_YM = AYM(&CUR_YM, 12, 'I6YYM');
-*-TYPE &CUR_YM &MONTH_PRIOR_SIXTY_YM &MONTH_NEXT_TWELVE_YM
TABLE FILE MISTRAL_PDA_META/MISTRAL_PDA_FS00_V01
SUM
     COMPUTE YM_VALUE/P12=MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.SALQTY; AS 'Qty'
     COMPUTE YM_TEXT/A20 = 'Qty';
     COMPUTE YM_TEXT_DISPLAY/A25 = MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.YM | '-Qty';
BY  MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.PARTNO AS 'Part'
BY  MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.TRUNCATED_PART AS 'Truncated,Part'
BY  MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.PC
BY  MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.PRDCAT3NM AS 'Prod,Category,Name'
BY  MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.WH
BY  MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.PLTNM AS 'Plant'
BY  MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.YM AS ''
WHERE ( CO EQ 'C' );
WHERE ( BRH NE 'A1' OR 'H4' );
WHERE ( CSTGRP NE 'RVP' );
WHERE ( YM GE '&MONTH_PRIOR_SIXTY_YM' ) AND ( YM LT '&CUR_YM' );
ON TABLE HOLD AS HOLD_1
END
TABLE FILE MISTRAL_PDA_META/MISTRAL_PDA_FS00_V01
SUM
     COMPUTE YM_VALUE/P12=MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.SALAMT; AS 'Sales @'
     COMPUTE YM_TEXT/A20 = 'Sales @';
     COMPUTE YM_TEXT_DISPLAY/A25 = MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.YM | '-Sales @';
BY  MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.PARTNO AS 'Part'
BY  MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.TRUNCATED_PART AS 'Truncated,Part'
BY  MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.PC
BY  MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.PRDCAT3NM AS 'Prod,Category,Name'
BY  MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.WH
BY  MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.PLTNM AS 'Plant'
BY  MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.YM AS ''
WHERE ( CO EQ 'C' );
WHERE ( BRH NE 'A1' OR 'H4' );
WHERE ( CSTGRP NE 'RVP' );
WHERE ( YM GE '&MONTH_PRIOR_SIXTY_YM' ) AND ( YM LT '&CUR_YM' );
ON TABLE HOLD AS HOLD_2
END
TABLE FILE MISTRAL_PDA_META/MISTRAL_PDA_FS00_V01
SUM
     COMPUTE YM_VALUE/P12=MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.SALE_CURRENT_SC; AS 'Current,SC'
     COMPUTE YM_TEXT/A20 = 'Current SC';
     COMPUTE YM_TEXT_DISPLAY/A25 = MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.YM | '-Current SC';
BY  MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.PARTNO AS 'Part'
BY  MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.TRUNCATED_PART AS 'Truncated,Part'
BY  MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.PC
BY  MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.PRDCAT3NM AS 'Prod,Category,Name'
BY  MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.WH
BY  MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.PLTNM AS 'Plant'
BY  MISTRAL_PDA_FS00_V01.MISTRAL_PDA_FS00_V01.YM AS ''
WHERE ( CO EQ 'C' );
WHERE ( BRH NE 'A1' OR 'H4' );
WHERE ( CSTGRP NE 'RVP' );
WHERE ( YM GE '&MONTH_PRIOR_SIXTY_YM' ) AND ( YM LT '&CUR_YM' );
ON TABLE HOLD AS HOLD_3
END
TABLE FILE HOLD_1
SUM
     YM_VALUE
BY  PARTNO AS 'Part'
BY  TRUNCATED_PART AS 'Truncated,Part'
BY  PC
BY  PRDCAT3NM AS 'Prod,Category,Name'
BY  WH
BY  PLTNM AS 'Plant'
ACROSS YM_TEXT AS '' COLUMNS 'Qty' AND 'Sales @' AND 'Current SC' NOPRINT
ACROSS YM AS '' NOPRINT
ACROSS YM_TEXT_DISPLAY AS ''
HEADING
"Server Name<+0>: &SERVER_NAME<+0>  User Name<+0>: &IBIMR_user<+0>  Report Date<+0>: &DATEtMDYY &TOD"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD AS 'planning' FORMAT XLSX
ON TABLE SET XLSXPAGESETS ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = nsk_americas_style_gui,
$
     SUMMARY='Sales Canada',
     TITLETEXT='Sales Canada',
$
TYPE=REPORT,
     SIZE=9,
$
TYPE=HEADING,
     SIZE=8,
$
TYPE=HEADING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=1,
     STYLE=BOLD,
$
TYPE=HEADING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=3,
     STYLE=BOLD,
$
TYPE=HEADING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=5,
     STYLE=BOLD,
$
TYPE=HEADING,
     LINE=2,
     OBJECT=TEXT,
     ITEM=1,
     STYLE=BOLD,
$
TYPE=HEADING,
     LINE=3,
     OBJECT=TEXT,
     ITEM=1,
     STYLE=BOLD,
$
TYPE=HEADING,
     LINE=4,
     OBJECT=TEXT,
     ITEM=1,
     STYLE=BOLD,
$
TYPE=HEADING,
     LINE=5,
     OBJECT=TEXT,
     ITEM=1,
     STYLE=BOLD,
$
TYPE=ACROSSVALUE,
     STYLE=BOLD,
$
ENDSTYLE
MORE
FILE HOLD_2
MORE
FILE HOLD_3
END


8.2.01m
Windows, All Outputs
 
Posts: 10 | Registered: June 05, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
quote:
If there is a different/better way I am open to ideas.

Here's one: MATCH FILE

-- you can use it to augment your final Hold file, ensuring that there is a row instance for every combination of by-variable/across-variable values for your final report.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
You could do a DEFINE on the final HOKD file such as
YM_VALUE2/D12.2 MISSING OFF= IF YM_VALUE IS MISSING THEN 0 ELSE YM_VALUE;
Then use YM_VALUE2 in your report.
 
Posts: 142 | Location: Adelaide South Australia | Registered: October 27, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
Try changing your SET NODATA = '0' to SET NODATA = 0 e.g. remove the single quotes.

This works on the example against the car file above.

SET NODATA = 0
SET PAGE = NOLEAD
TABLE FILE CAR
SUM 
COMPUTE YM_VALUE/P12=SALES;
COMPUTE YM/A20 = 'Qty';
BY CAR
BY COUNTRY
ON TABLE HOLD AS ACC1
END

TABLE FILE CAR
SUM 
COMPUTE YM_VALUE/P12=DEALER_COST;
COMPUTE YM/A20 = 'Sales @';
BY CAR
BY COUNTRY
ON TABLE HOLD AS ACC2
END

TABLE FILE CAR
SUM 
COMPUTE YM_VALUE/P12=RETAIL_COST;
COMPUTE YM/A20 = 'Current SC';
BY CAR
BY COUNTRY
ON TABLE HOLD AS ACC3
END

TABLE FILE ACC3
SUM YM_VALUE AS ''
BY CAR AS ''
ACROSS YM AS '' COLUMNS 'Qty' AND 'Sales @' AND 'Current SC'
ACROSS COUNTRY AS ''
ON TABLE SET STYLE *
  grid=off, size=10, $
ENDSTYLE
MORE
FILE ACC1
MORE
FILE ACC2
END


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: 5687 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Member
posted Hide Post
Those zeroes are not really there and if you use XLSX format you will see the cells are blank because the values really are missing but the XLSX format uses a custom format in excel to display them as zero but that is an issue when the user tries to do anything with the data because it is not a real zero which is the underlying issue. The across generates the missing data which I need to be converted to zeroes.


8.2.01m
Windows, All Outputs
 
Posts: 10 | Registered: June 05, 2017Reply With QuoteReport This Post
Expert
posted Hide Post
quote:
a custom format in excel to display them as zero

Ok, nothing mentioned above about post rendering manipulation Smiler

The custom format is passed by WebFOCUS so you might wish to raise this with Tech Support.

Not sure how this would be interpretted - a bug or NFR (arguments for both as always) - but contact with Tech Support will at least let you know.

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: 5687 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] grouping multiple sum columns separately using across

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