I have a task where I am using FML to create a spreadsheet in EXL2K.

The report is shaping up but I have some questions.

In the FML I have blank lines between sections. I need to shrink the height of the blank lines. In Excel, the standard height is 12.75 and I need to shrink it to 4. Anyone have any advice?

Second question is how to get a heading into cell A1. Starting in B1 and going out to J1, I have the years which are from an ACROSS statement. Starting in A2 and going down I have the Tag/Row Headings. I am trying to get a heading into cell A1, anyone know how?

Third question is how to get borders around the cells. I've tried using the BORDER and BORDER-STYLE attributes and they work in PDF but not in EXL2K.

One Final question. Currently, this report has data for every year back to 1998. The purpose of the report is to add a new year at the end of the year so the user does not have to manually do it. Also, there are over 90 worksheets in this spreadsheet, so manually adding a year to every worksheet is quite a task. What I need to do in the output is hide everything except the last 5 years. The data has to be there, but they want to columns hidden. For example, when I add 2007, they want to hide the columns with 1998-2002 data.

Unfortunately, it appears that you cannot get the row to be less than the Excel default of 12.75 units:

TABLE FILE CAR
SUM
SALES
-*BY CAR
FOR COUNTRY
'ITALY' OVER
" " LABEL S1 OVER
'JAPAN'
HEADING
"WEBFOCUS REPORT"
ON TABLE SET PAGE NOLEAD
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLESHEET *
TYPE=REPORT, FONT='ARIAL', SIZE=15, $
TYPE=REPORT, LABEL=S1, SIZE=1, $
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K
END

TYPE=REPORT, LABEL=S1, SIZE=1, $ makes the blank row the smallest it can be, which is 12.75. It does work correctly for PDF and HTML.

I don't have version 7 yet, but have you looked into Excel templates? I think Excel templates would be the solution to hiding/showing columns, you would have to only change the template...

Francis, thanks for the reply. I have tried a template with an auto_open macro in it, but it crashed the agent when I tried doing all 97 work sheets. I'm going to try and rename the macro to something else and see if that works. Also if I use a template, the TITLETEXT='&SHEETNM.EVAL', does not name the work sheet. I tried it with the .EVAL and without.

I did find this on page 600 of the 7.13 Creating Reports manual. There is a section on Hiding Columns and User Controlled Options (i.e. Freeze columns, Filter, etc.) but it appears that it is only available for Active Reports and we don't have a license for that, but I am working on management to get Active Reports.

I am going to open a NFR to allow this in EXCEL as well.

I'm also going to open a problem report on the TITLETEXT problem after I confirm it is really a problem.

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

jgelona where you able to specify titletext while using the templates?

For this particular report, no. I ended up doing an Excel compound document and wrote a macro that I put in the following folder on each user PC:

C:\Documents and Settings\userid\Application Data\Microsoft\Excel\XLSTART

This way, when the users open the spread sheet, they can run the macro to finish the formatting that I can't do in WebFOCUS.

Fortuneately, there are only about 8 users of this report.

Thanks jgelona for the reply.
Even in my case i wrote a macro which does that job. But I was just wondering whether there was a simpler method to achieve the same.
Looks like its not there.

Thanks
Shyam