Hi All,

The ACROSSTITLE is not displaying properly for all the rows in a report. The below mentioned code is giving proper report in EXL2K format and the same is not working fine in XLSX(Excel 2007).


EXL2K Code:

**********

TABLE FILE CAR
PRINT
DEALER_COST
ACROSS CAR AS 'Car Type'
ON TABLE ROW-TOTAL AS 'Test'
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLESHEET *
TYPE=ACROSSTITLE,BORDER=LIGHT,COLOR=GREEN,STYLE=BOLD,JUSTIFY=CENTER,$
ENDSTYLE
END

XLSX Code:

**********


TABLE FILE CAR
PRINT
DEALER_COST
ACROSS CAR AS 'Car Type'
ON TABLE ROW-TOTAL AS 'Test'
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLESHEET *
TYPE=ACROSSTITLE,BORDER=LIGHT,COLOR=RED,STYLE=BOLD,JUSTIFY=CENTER,$
ENDSTYLE
END

Please see the across title is not working for the first row data "TRIUMPH".

Please help me on this.


Regards,
Suresh

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

Suresh,
Try:

 
TABLE FILE CAR
PRINT
DEALER_COST
ACROSS CAR AS 'Car Type' ACROSS-TOTAL AS Test
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLESHEET *
TYPE=ACROSSTITLE,BORDER=LIGHT,COLOR=RED,STYLE=BOLD,JUSTIFY=CENTER,$
ENDSTYLE
END 

Hi Danny,
Thanks for your code and it looks good now.
Is it possible to display the across title only for the data section (in our case till the "TRIUMPH")?. I want to display this title only for the data sections from "ALFA ROMEO" till "TRIUMPH" and not for the Test column.

Can you please tell me how can we achieve this?.

Regards,
Suresh

Sureshkuamr

Here is the revised code...

TABLE FILE CAR
PRINT
DEALER_COST
ACROSS CAR AS 'Car Type' ACROSS-TOTAL AS ''
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLESHEET *
TYPE=ACROSSTITLE,BORDER=LIGHT,COLOR=RED,STYLE=BOLD,JUSTIFY=CENTER,$
ENDSTYLE
END 

Hi Chuck,
I have tried your revised code and it works fine for the across title. Find the below scenarios which i am trying to implemented in my report.

I have a BY field amount value and it is displaying as first column in my report. I wanted this first column amount also to be summed with the across amounts.
In the below case (RETAIL_COST + DEALER_COST ) and the final total amount should be "5,765".

RETAIL_COST = 3,139
DEALER_COST = 2,626

Final total should be 5,765 ( But i am getting only 2,626) as total. Please run the below code for report.

Sample Code

TABLE FILE CAR
PRINT
DEALER_COST
BY RETAIL_COST
ACROSS CAR AS 'Car Type' ACROSS-TOTAL AS ''
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLESHEET *
TYPE=ACROSSTITLE,BORDER=LIGHT,COLOR=RED,STYLE=BOLD,JUSTIFY=CENTER,$
ENDSTYLE
END

Note : If we use ROW-TOTAL i am getting the total as we expected, but the formatting is not happening in ACROSSTITLE.

Thanks for your help in advance.

Regards,
Suresh

Does this may suits your need ?

TABLE FILE CAR
SUM COMPUTE TOTAL_COST /D9 = DEALER_COST + RETAIL_COST;
BY RETAIL_COST

PRINT DEALER_COST
BY RETAIL_COST
ACROSS CAR AS 'Car Type'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLESHEET *
TYPE=ACROSSTITLE,BORDER=LIGHT,COLOR=RED,STYLE=BOLD,JUSTIFY=CENTER,$
ENDSTYLE
END

Hi MartinY,

The code which you have shared is giving the correct results. But i wanted to display the TOTAL_COST at the last column in my report after the ACROSS columns printed.

Currently the column TOTAL_COST is coming 2nd column in the report.
Can you please help me to get this done?.

Regards,
Suresh

What you are trying to do is to mimic the ACROSS-TOTAL feature.
But since you want to display the RETAIL_COST as the BY field and DELEAR_COST as the measure (PRINT) to have the total of both in a separate column,
you will need to "create" that total and include it as a fake CAR value that will then be positioned as the last one.

DEFINE FILE CAR
TOT_COST /D7 = DEALER_COST + RETAIL_COST;
END
TABLE FILE CAR
SUM TOT_COST AS 'DEALER_COST'
BY RETAIL_COST
BY TOTAL COMPUTE POS /I2  = 2;
BY TOTAL COMPUTE CAR /A16 = '';
ON TABLE HOLD AS TOTREC FORMAT FOCUS
END
-RUN

TABLE FILE CAR
SUM DEALER_COST
BY RETAIL_COST
BY TOTAL COMPUTE POS /I2 = 1;
BY CAR
ON TABLE HOLD AS DETREC FORMAT FOCUS
END
-RUN

TABLE FILE DETREC
SUM DEALER_COST
BY RETAIL_COST
BY POS
BY CAR
ON TABLE HOLD AS ALLREC FORMAT FOCUS
MORE
FILE TOTREC
END
-RUN

TABLE FILE ALLREC
SUM DEALER_COST
BY RETAIL_COST
ACROSS POS NOPRINT
ACROSS CAR AS 'Car Type'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLESHEET *
TYPE=ACROSSTITLE,BORDER=LIGHT,COLOR=RED,STYLE=BOLD,JUSTIFY=CENTER,$
ENDSTYLE
END
-RUN

Hi MartinY,

I have tried your code and it worked fine for me.

I have another scenario where i need to use only the ROW-TOTAL to get the total. Find the below code for both EXL2K and XLSX versions.


EXL2K Code

TABLE FILE CAR
SUM
DEALER_COST
BY CAR
SUM
RETAIL_COST AS ''
BY CAR
ACROSS CAR AS 'Car Type' ROW-TOTAL AS 'Total'
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLESHEET *
TYPE=ACROSSTITLE,BORDER=LIGHT,COLOR=GREEN,STYLE=BOLD,JUSTIFY=CENTER,$
ENDSTYLE
END
-EXIT

XLSX Code

TABLE FILE CAR
SUM
DEALER_COST
BY CAR
SUM
RETAIL_COST AS ''
BY CAR
ACROSS CAR AS 'Car Type' ROW-TOTAL AS 'Total'
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLESHEET *
TYPE=ACROSSTITLE,BORDER=LIGHT,COLOR=GREEN,STYLE=BOLD,JUSTIFY=CENTER,$
ENDSTYLE
END
-EXIT

Can you please tell me why the across title is not coming properly for XLSX alone?.

Thanks a lot for your help in advance.

Regards,
Suresh

I can't explain and you probably need to contact TechSupport for that since it may be related to internal code on how ROW-TOTAL react with EXL2K vs XLSX

Myself I would use that version of the code

TABLE FILE CAR
SUM DEALER_COST
BY CAR
SUM RETAIL_COST AS ''
BY CAR
ACROSS CAR AS 'Car Type' ACROSS-TOTAL AS 'Total'
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLESHEET *
TYPE=ACROSSTITLE,BORDER=LIGHT,COLOR=GREEN,STYLE=BOLD,JUSTIFY=CENTER,$
ENDSTYLE
END

since it gives the same layout in either EXL2K or XLSX.
And even if it seems that you don't want to see "Car Type" header above the Total column, at the end it's the Car Type's Total for which make senses to have it under the same header as other car's types.