Hello,

I'm writing a WebFocus Financial report where I have an ACROSS for my year selection (and I have two year values) . The output looks something like this:

--------------- Current Year Prior Year
Company Account
TOTAL NET SALES 100,000 -
- 90,000
COST SALES 30,000 -
- 31,000

What I'm seeking is to rebuild the report so it looks like this:

--------------- Current Year Prior Year
Company Account
TOTAL NET SALES 100,000 90,000
COST SALES 30,000 31,000

I've read posts where I would "HOLD" the first example and rebuild it into the format I want but I cannot figure this out.

Below is my FOCEXEC code. Any ideas?

Thanks!

  
-OLAP OFF;
-SET &ECHO=ALL;
-SET &CODE = 'ACTUAL - 2008.05 - GEO1-MGT1 - PARKERRATE - USD';
-SET &CODEYR = EDIT(&CODE,'$$$$$$$$$9999$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$');
-SET &FRONTHALF = EDIT(&CODE, '99999999$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$');
-SET &BACKHALF = EDIT(&CODE, '$$$$$$$$$$$$$9999999999999999999999999999999999');
-SET &CODELASTYR = &CODEYR-1;
-SET &LCode = '&FRONTHALF &CODELASTYR|&BACKHALF';
TABLE FILE SCOPE_HISTORIZE
SUM
     Consolidated_Amount_Measure_DT
BY Historized_Scope_parent NOPRINT
BY Historized_Scope NOPRINT
BY Historized_Scope_caption
BY HIGHEST Account_ACTUAL___V11_parent NOPRINT
BY Account_ACTUAL___V11 NOPRINT
BY Account_ACTUAL___V11_caption
BY Consolidations1 NOPRINT
ACROSS Consolidations1
HEADING
""
FOOTING
""
WHERE ( Account_ACTUAL___V11_caption EQ 'TP8A - Net Sales' OR 'T5200 - Total Cost Of Sales' OR 'TP7A - Gross Margin' OR 'TP7B - Adjustments To Cost Of Sales' OR 'TP6A - Net Manufacturing Margin' OR 'TP6B - Total Other Operating Costs' OR 'TP5A - Net Operating Margin' OR 'TP5B - Total Division Sales Expense' OR 'TP4A - Division Net Operating Margin' OR 'TP4B - Total Sell Gen &| Admin Exp' OR '8SCD - Sales Company Direct' OR '8SCI - Sales Company Indirect' OR '9H00 - Retiree Benefits' OR '5A00 - Suspense Error-North America' OR '9D00 - Contra Accounts' OR 'TP3 - Divisional Net Earnings' ) AND ( Consolidations1 EQ '&Consolidations1.(FIND Consolidations1 IN scope_historize).Consolidations1.' OR '&LCode.EVAL' ) AND ( Flow1 EQ 'F99 - Closing Balance' ) AND ( Main_geographic_caption EQ 'T-COMPANY' ) AND ( Historized_Scope_caption EQ '&Historized_Scope_caption.(FIND Historized_Scope_caption IN scope_historize).Historized_Scope_caption.' );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=DATA,
     COLUMN=N6,
     STYLE=NORMAL,
$
TYPE=DATA,
     ACROSSCOLUMN=N1,
     TARGET='_self',
     FOCEXEC=app/salesthroughdne1(MGTL=N8 ACCT=N5 CONS=N7),
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TITLE,
     COLUMN=N6,
     STYLE=BOLD,
$
TYPE=TITLE,
     ACROSSCOLUMN=N1,
     TARGET='_self',
     FOCEXEC=app/salesthroughdne1(MGTL=N8 ACCT=N5 CONS=N7),
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     IMAGE=app/excel_icon.gif,
     POSITION=(+0.000000 +0.000000),
     TARGET='_blank',
     FOCEXEC=app/salesthroughdne2(MGTL=N3 CONS=N7),
     IMAGEBREAK=ON,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
TYPE=REPORT,
     COLUMN=N6,
     SQUEEZE=3.166667,
$
TYPE=REPORT,
     COLUMN=N4,
     WRAP=0.305556,
$
TYPE=REPORT,
     COLUMN=N5,
     WRAP=0.305556,
$
TYPE=REPORT,
     COLUMN=N1,
     WRAP=0.291667,
$
TYPE=REPORT,
     COLUMN=N2,
     WRAP=0.305556,
$
TYPE=REPORT,
     COLUMN=N7,
     SQUEEZE=0.305556,
$
ENDSTYLE
END

This message has been edited. Last edited by: fjkreiner,

FJ,

quote:

BY Consolidations1 NOPRINT
ACROSS Consolidations1

Why do you sort vertical and horizontal with the same field?

Daniel is right.
Remove you 'BY Consolidations1 NOPRINT' line and quite probably you're in business.
If this still does not give you the desired result, then start by removing all the NOPRINTs on the BY clauses, this will most probably show you where things go wrong.

I made my statement read as

 
SUM 
     Consolidated_Amount_Measure_DT
BY Historized_Scope_caption
BY Account_ACTUAL___V11_caption
ACROSS Consolidations1

and I still get

------------------------Current   Prior
Total Company  T-SALES    100       -
                           -       90 

Is it something to do with "ACROSS"?

FJ,

What is the file type of your data?

I noticed you had a lot of BY phrases. Are you sure that they are all the same for each of the years? If they are not, that would cause the behaviour you are seeing.

To paraphrase Ginny -- First know your data.

Remove the NOPRINT on the BY's, and you'll see what differentiates the two rows.

I actually removed all the rows that had "NOPRINT" on them (for debugging purposes) and I still have the same result.

My data is coming off of a SQL Server Analysis Service Cube.

fjk,

Sometimes is it better to use sample (simple) data to understand a method and then apply what you learn. Try using the GGSALES data in the IBISAMP apps folder to give your basic layout, change the verb object to see the differences. This basic code will get you started - play with it to understand how you can get the results that you want.

DEFINE FILE GGSALES
  YEAR/YY = DATE;
END
TABLE FILE GGSALES
  SUM DOLLARS
   BY REGION
ACROSS YEAR AS ''
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
  GRID=OFF, SIZE=9, $
ENDSTYLE
END

T

Tony and everyone,

Thanks for your help above. I was talking to another one of my WF users at my job and he developed the below code to help me on this issue.

Thought you might like to see it.

Thanks everyone!

 
-OLAP OFF;
-SET &ECHO=ALL;
-SET &CODE = 'ACTUAL - 2008.04 - GEO1-MGT1 - PARKERRATE - USD';
-SET &CODEYR = EDIT(&CODE,'$$$$$$$$$9999$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$');
-SET &FRONTHALF = EDIT(&CODE, '99999999$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$');
-SET &BACKHALF = EDIT(&CODE, '$$$$$$$$$$$$$9999999999999999999999999999999999');
-SET &CODELASTYR = &CODEYR-1;
-SET &LCode = '&FRONTHALF &CODELASTYR|&BACKHALF';
TABLE FILE SCOPE_HISTORIZE
SUM
     Consolidated_Amount_Measure_DT
BY Historized_Scope_parent
BY Historized_Scope
BY Historized_Scope_caption
BY HIGHEST Account_ACTUAL___V11_parent
BY Account_ACTUAL___V11
BY Account_ACTUAL___V11_caption
BY Consolidations1
ACROSS Consolidations1
WHERE ( Account_ACTUAL___V11_caption EQ 'TP8A - Net Sales' OR 'T5200 - Total Cost Of Sales' OR 'TP7A - Gross Margin' OR 'TP7B - Adjustments To Cost Of Sales' OR 'TP6A - Net Manufacturing Margin' OR 'TP6B - Total Other Operating Costs' OR 'TP5A - Net Operating Margin' OR 'TP5B - Total Division Sales Expense' OR 'TP4A - Division Net Operating Margin' OR 'TP4B - Total Sell Gen &| Admin Exp' OR '8SCD - Sales Company Direct' OR '8SCI - Sales Company Indirect' OR '9H00 - Retiree Benefits' OR '5A00 - Suspense Error-North America' OR '9D00 - Contra Accounts' OR 'TP3 - Divisional Net Earnings' ) AND ( Consolidations1 EQ '&Consolidations1.(FIND Consolidations1 IN scope_historize).Consolidations1.' OR '&LCode.EVAL' ) AND ( Flow1 EQ 'F99 - Closing Balance' ) AND ( Main_geographic_caption EQ 'T-COMPANY' ) AND ( Historized_Scope_caption EQ '&Historized_Scope_caption.(FIND Historized_Scope_caption IN scope_historize).Historized_Scope_caption.' );
ON TABLE HOLD
END

DEFINE FILE HOLD
PRIORMONTH/D18.2 = IF E08 EQ MISSING THEN 0 ELSE E08;
ACTUALMONTH/D18.2 = IF E09 EQ MISSING THEN 0 ELSE E09;
CONSOL/A60 = E07;
END

TABLE FILE HOLD
SUM
     ACTUALMONTH AS 'Actual'
     PRIORMONTH AS 'Prior'
     COMPUTE PERCENTCHANGE/D8.2% = ( ( ACTUALMONTH - PRIORMONTH ) / PRIORMONTH ) * 100;
     CONSOL NOPRINT
BY Historized_Scope_parent NOPRINT
BY Historized_Scope NOPRINT
BY Historized_Scope_caption
BY HIGHEST Account_ACTUAL___V11_parent NOPRINT
BY Account_ACTUAL___V11 NOPRINT
BY Account_ACTUAL___V11_caption
ON TABLE SUBHEAD
""
ON TABLE SUBFOOT
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=DATA,
     COLUMN=N7,
     TARGET='_self',
     FOCEXEC=app/salesthroughdne1(ACCT=N5 CONS=N10 MGTL=N2),
$
TYPE=DATA,
     COLUMN=N6,
     STYLE=NORMAL,
     JUSTIFY=LEFT,
$
TYPE=DATA,
     COLUMN=N4,
     JUSTIFY=CENTER,
$
TYPE=DATA,
     COLUMN=N3,
     JUSTIFY=LEFT,
$
TYPE=DATA,
     COLUMN=N5,
     JUSTIFY=CENTER,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TITLE,
     COLUMN=N7,
     JUSTIFY=CENTER,
$
TYPE=TITLE,
     COLUMN=N6,
     STYLE=BOLD,
     JUSTIFY=LEFT,
$
TYPE=TITLE,
     COLUMN=N4,
     JUSTIFY=CENTER,
$
TYPE=TITLE,
     COLUMN=N3,
     JUSTIFY=LEFT,
$
TYPE=TITLE,
     COLUMN=N5,
     JUSTIFY=CENTER,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     IMAGE=app/excel_icon.gif,
     POSITION=(+0.000000 +0.000000),
     TARGET='_blank',
     FOCEXEC=app/salesthroughdne2(MGTL=N3 CONS=N7),
     IMAGEBREAK=ON,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
TYPE=REPORT,
     COLUMN=N6,
     SQUEEZE=3.166667,
$
TYPE=REPORT,
     COLUMN=N4,
     SQUEEZE=0.875000,
$
TYPE=REPORT,
     COLUMN=N3,
     SQUEEZE=2.930556,
$
TYPE=REPORT,
     COLUMN=N5,
     SQUEEZE=0.305556,
$
TYPE=REPORT,
     COLUMN=N1,
     SQUEEZE=1.513889,
$
TYPE=REPORT,
     COLUMN=N2,
     WRAP=1.152778,
$
ENDSTYLE
END