HI All,

I have a report which looks like this

product1 product2 product3 ACROSS TOTAL FOR ALL PRODUCTS
A B C A B C A B C A B C
Name1 2 4 5 0 2 1 1 0 0 3 6 6
Name2 1 0 0 0 3 4 0 0 0 1 3 4

How do I get Across Total for all products for A,B and C?

Thank You

If I read your request correctly, one solution is to use a multiverb request:

TABLE FILE CAR
-* Add the 3 columns together (that's your A,B & C)
SUM
COMPUTE R_TOT/D12.2 = SALES+RCOST+DCOST; AS 'TOTAL LINE'
BY COUNTRY
-* Normal request
SUM SALES RCOST DCOST
ACROSS BODY
BY COUNTRY
ON TABLE SET STYLE *
-* move total column to be last
TYPE=REPORT, COLUMN=R_TOT, SEQUENCE=99, $
ENDSTYLE
END

HI Alan,

Sorry if I have confused you

what I need is across total seperately for A,B and C

product1 A + product2 A + product3 A = ACROSS TOTAL A
product1 B + product2 B + product3 B = ACROSS TOTAL B
product1 C + product2 C + product3 C = ACROSS TOTAL C

Thank you

Oh, that's easy:

SUM A B C ROW-TOTAL
ACROSS PRODUCT
BY NAME

HI Alan,

Thanks for you reply. But A,B and C are not columns(sorry I didn't mention that earlier), they are values coming of from a column(I have done across on the column for this report)

Thank you

This will give you the result you wanted but this is not the recommended way to do this. If you didn't find any other way, you can use this

TABLE FILE CAR
SUM DEALER_COST RETAIL_COST
ACROSS CAR
BY COUNTRY
WHERE CAR EQ 'AUDI' OR 'BMW' OR 'DATSUN'
ON TABLE HOLD AS TEST1
END
-RUN


? HOLD TEST1

-*0FIELDNAME ALIAS FORMAT
-* COUNTRY E01 A10
-* DEALER_COST E02 D7
-* RETAIL_COST E03 D7
-* DEALER_COST E04 D7
-* RETAIL_COST E05 D7
-* DEALER_COST E06 D7
-* RETAIL_COST E07 D7

TABLE FILE TEST1
HEADING
" <+0>AUDI<+0>BMW<+0>DATSUN<+0> "
PRINT
E02 AS 'DEALER,COST' E03 AS 'RETAIL,COST'
E04 AS 'DEALER,COST'
E05 AS 'RETAIL,COST' E06 AS 'DEALER,COST'
E07 AS 'RETAIL,COST'
COMPUTE DCTOT/D7=E02 + E04 + E06; AS 'TOTAL, DEALER, COST'
COMPUTE RCTOT/D7=E03 + E05 + E07; AS 'TOTAL, RETAIL, COST'
BY COUNTRY
ON TABLE SET STYLE *
TYPE=REPORT,GRID=OFF,$
TYPE=HEADING,COLSPAN=9,HEADALIGN=BODY,$
TYPE=HEADING,LINE=1,ITEM=1,OBJECT=TEXT,COLSPAN=1,JUSTIFY=CENTER,$
TYPE=HEADING,LINE=1,ITEM=2,OBJECT=TEXT,COLSPAN=2,JUSTIFY=CENTER,$
TYPE=HEADING,LINE=1,ITEM=3,OBJECT=TEXT,COLSPAN=2,JUSTIFY=CENTER,$
TYPE=HEADING,LINE=1,ITEM=4,OBJECT=TEXT,COLSPAN=2,JUSTIFY=CENTER,$
TYPE=HEADING,LINE=1,ITEM=5,OBJECT=TEXT,COLSPAN=2,JUSTIFY=CENTER,$
ENDSTYLE
END
-RUN
-EXIT

Hope it helps,

If you know your data then try:

DEFINE FILE CAR
CONTINENT/A10 = IF COUNTRY EQ 'JAPAN' THEN 'ASIA' ELSE 'EEC';
SEDAN/I8=IF BODYTYPE EQ 'SEDAN' THEN SALES ELSE 0;
CONVERTIBLE/I8=IF BODYTYPE EQ 'CONVERTIBLE' THEN SALES ELSE 0;
ROADSTER/I8=IF BODYTYPE EQ 'ROADSTER' THEN SALES ELSE 0;
END
TABLE FILE CAR
SUM
SEDAN AS 'TOTAL,SEDAN'
CONVERTIBLE AS 'TOTAL,CONVERTIBLE'
ROADSTER AS 'TOTAL,ROADSTER'
BY CONTINENT
SUM SALES
ACROSS BODYTYPE AS '' COLUMNS SEDAN AND CONVERTIBLE AND ROADSTER
BY CONTINENT
ON TABLE SET STYLE *
TYPE=REPORT, COLUMN=SEDAN, SEQUENCE=97, $
TYPE=REPORT, COLUMN=CONVERTIBLE, SEQUENCE=98, $
TYPE=REPORT, COLUMN=ROADSTER, SEQUENCE=99, $
ENDSTYLE
END

Which is a slighlty different version from Kamesh.

Thanks Kamesh, what you posted will definetly work.

But I am going to make changes at the table level to make things easier.

Thanks Alan, I know my data and will try your suggestion in future.

Please post your final version of your code. That will help us in future.

HI Alan, Kamesh

Now I have A,B and C as columns and below is my code. I still couldn't get this to work properly.

Thank you
----------------

-OLAP ON
-* File report.fex
OLAP DIMENSIONS
-* DIMENSIONS FILE TABLE1
Time Period: YEAR, QUARTER, MONTH, WEEK, DATE;
PRODUCT NAME: PRODUCT_NAME;
NAME: NAME;
END
TABLE FILE TABLE1
SUM
A
B
C
BY NAME
ACROSS PRODUCT_NAME
SUM A B C ROW-TOTAL
BY NAME
ACROSS PRODUCT_NAME
HEADING
"REPORT"
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET ONLINE-FMT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET AUTODRILL ON
ON TABLE SET OLAPPANE TOP
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='SCREEN',
LEFTMARGIN=0.000000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.000000,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=ON,
FONT='ARIAL',
SIZE=9,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
RIGHTGAP=0.125000,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
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,
$
ENDSTYLE
END

Madhu

When you say it's not working properly, how?

Also you have the ACROSS at both levels, I think it should only be on the second verb request.

HI Alan,

I removed the first across and report layout seems to work fine as designed, when I run it the first time. But when I do a
selection by any of the dimensions, report layout goes haywire for example two or three A columns together and the name column goes somewhere. When I do another selection name column completely disappears.

This is an OLAP Report

Thank you

Madhu

My OLAP is sketchy above the basics, and I have never used an ACROSS with OLAP.

Hopefully someone with greater OLAP experience can help here.

Hi Alan, Kamesh

I was able to make the Across Total Perfectly as desired. Below is the code. It turned out to be simple than I expected.

-OLAP ON
-* File report.fex
OLAP DIMENSIONS
-* DIMENSIONS FILE TABLE1
Time Period: YEAR, QUARTER, MONTH, WEEK, DATE;
PRODUCT NAME: PRODUCT_NAME;
NAME: NAME;
END
TABLE FILE TABLE1
SUM
A
B
C
BY NAME
ACROSS PRODUCT_NAME
HEADING
"REPORT"
ON TABLE SET PAGE-NUM OFF
ON TABLE ROW-TOTAL AS 'TOTAL'
ON TABLE NOTOTAL
ON TABLE SET ONLINE-FMT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET AUTODRILL ON
ON TABLE SET OLAPPANE TOP
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='SCREEN',
LEFTMARGIN=0.000000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.000000,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=ON,
FONT='ARIAL',
SIZE=9,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
RIGHTGAP=0.125000,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
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,
$
ENDSTYLE
END