I created a report that opens up in Excel. It is a report that uses the OVER statement that generates about 12 category lines over one another on the Excel report. I need to indent some of those rows a couple of spaces. Does anyone know how I would do that. I tried using the IN statement but it did not work. Thanks.


Windows 7
WF 761

This message has been edited. Last edited by: Michele Brooks,

I have noticed that over messes up the spacing a little. If nobody provides a solution, I would use across instead of over:

 
TABLE FILE CAR
ACROSS COUNTRY AS ''
ACROSS CAR AS ''
...
 

In the stylesheet you might be able to justify some of the columns center/left and the others right to meet your needs.

Hi Michele, Could you provide a sample of this using TABLE FILE CAR? That would help us to more easily resolve this for you.

I need RCost and Sales indented 3 spaces. Thanks.

SET ASNAMES = ON
DEFINE FILE CAR
TODAYS_DATE/YYMD = '&YYMD';
SORT_YR/YY = TODAYS_DATE;
MTH_NAME/A10 = IF DEALER_COST GE 0 AND DEALER_COST LE 5000 THEN 'January' ELSE
IF DEALER_COST GE 5001 AND DEALER_COST LE 5099 THEN 'February' ELSE
IF DEALER_COST GE 5010 THEN 'March' ELSE ' ';
-*AYEAR/A4 = '2013';
AYEAR/A4 = EDIT(SORT_YR,'9999');
MTH_SORT/A2= DECODE MTH_NAME (
'January' '01'
'February' '02'
'March' '03'
ELSE ' ');
END
TABLE FILE CAR
SUM DEALER_COST AS 'DCOST1'
RETAIL_COST AS 'RCOST1'
SALES AS 'SALES1'
BY SORT_YR
BY AYEAR
BY MTH_SORT
BY MTH_NAME
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
DEFINE FILE CAR
TODAYS_DATE/YYMD = '&YYMD';
SORT_YR/YY = TODAYS_DATE;
MTH_NAME/A10 = 'Total';
AYEAR/A4 = EDIT(SORT_YR,'9999');
MTH_SORT/A2 = '13';
END
TABLE FILE CAR
SUM DEALER_COST AS 'DCOST2'
RETAIL_COST AS 'RCOST2'
SALES AS 'SALES2'
BY SORT_YR
BY AYEAR
BY MTH_SORT
BY MTH_NAME
ON TABLE HOLD AS HOLD2 FORMAT ALPHA
END
MATCH FILE HOLD1
SUM DCOST1
RCOST1
SALES1
BY SORT_YR
BY AYEAR
BY MTH_SORT
BY MTH_NAME
RUN
FILE HOLD2
SUM DCOST2
RCOST2
SALES2
BY SORT_YR
BY AYEAR
BY MTH_SORT
BY MTH_NAME
AFTER MATCH HOLD AS HOLD3 OLD-OR-NEW
END
DEFINE FILE HOLD3
DCOST/D12.2CM = DCOST1 + DCOST2;
RCOST/D12.2CM = RCOST1 + RCOST2;
SALES/I9C = SALES1 + SALES2;
TITLE_NAME/A15 = 'Dealer Cost';
SORT_YR/A4 = IF AYEAR NE '9999' THEN AYEAR ELSE '';
END
TABLE FILE HOLD3
SUM DCOST AS 'Dcost' OVER
RCOST AS 'RCost' OVER
SALES AS 'Sales'
BY TITLE_NAME NOPRINT
ACROSS AYEAR NOPRINT
ACROSS SORT_YR AS ''
ACROSS MTH_SORT NOPRINT
ACROSS MTH_NAME AS ''
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=LANDSCAPE,
$
END

I do this all the time. Try using:

SET SHOWBLANKS=ON

or

ON TABLE SET SHOWBLANKS ON

ON Preserves leading blanks internal blanks in HTML and EXL2K report output. Also preserves trailing blanks in heading, subheading, footing, and subfooting lines that use the default heading or footing alignment

quote:

ON TABLE SET SHOWBLANKS ON

I tried your suggestion both ways and the SHOWBLANKS did not work.

I'm on 7.6.11 and I have maybe a dozen EXL2K report of all kinds: compound, compound with EXL2K TEMPLATE, reports using OVER and FML (or EML or whatever it is call now) and just plain Jane EXL2K, and it works exactly as is stated in the documentation in all of them. Maybe it is a bug in 7.1.7.

It still works in 7.7.03. Perhaps Michele didn't know what it was for:

 
DEFINE FILE CAR
sales/A9='   ' | EDIT(CAR.BODY.SALES);
END
TABLE FILE CAR
SUM 
     CAR.CARREC.MODEL AS '' OVER 
     CAR.BODY.sales AS ''
ACROSS CAR.COMP.CAR
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET SHOWBLANKS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END
 

You should be able to adapt this to your scenario.

I am using a macro template to generate this report and whenever I use SHOWBLANKS, the macro template will not work. Thanks.

SET BLANKINDENT = ON

and have a space in your AS name like ' Title'

quote:

SET BLANKINDENT = ON

Thank you for your suggestion, but it did not work. Nothing was indented,

Hi Michele,

Some days before, i faced a scenario of positioning the datas within a CELL in EXCEL and got it resolved.

It may helps you out.

http://forums.informationbuild...957006136#1957006136

Thanks,
Rifaz

Thank you for your response, but I did not explain my problem correctly. I do not need to indent the data values in the report. I need to indent the ASNAMES. For example:

If the ASNAME is

Green Grocer

I need it to appear indented as
Green Grocer

Leaving two blank spaces in front of Green Grocer does not work when using OVER. My apologies for not posting my problem correctly. I already tried SHOWBLANKS and CLASS=indent in the stylesheet. Neither works. Thanks.

[Closed] I figured out another way. Centering the Top line column heading worked for what I wanted to do. I thank everyone for their responses.