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,

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
 

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

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.

where's that LIKE button?

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?

Hi Todd,
Have you tried this SET command:
SET HOLDMISS = ON

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.

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

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.

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.

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

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.

quote:

a custom format in excel to display them as zero

Ok, nothing mentioned above about post rendering manipulation

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