BS Publisher – Printing to Excel

BS Publisher adds a page (tab) to the OperaII publisher to allow for printing to Excel.

The default is that this tab is disabled but it can be enabled by changing l_bs1pub2xl in BS Settings.

It requires a modified version of the standard report design.

In use, you select Excel as the destination and click on Publish.

Your Excel file will be created ready for you to save as you require.

The modified report design takes its name from the requested report and is located in the same folder. (e.g. Server VFP\reports or Server VFP\bespoke\XXX\others)

The modified report name is the same plus ‘2XL’.

For example sqslincr.frx – sqslincr2xl.frx; z_slincr.frx – z_slincr2xl.frx.

On first requesting an Excel report, BS Publisher will offer to create the modified report. The new report will be saved initially as a copy of the existing report.

Some subsequent work is required, which you can do in OperaII or with VFP.

This amounts to simplifying the report to (typically) a header line in the Title band and a single Detail band.

BS|Publisher does not process any group or page headers or totals.

Login as ADMIN (or tick ps-develop in BS Browse) and request your report in the ususal way. In the Publisher form, selet the Excel tab – you will see this

Click Ok and, after a pause, you will be able to edit the new design.

1. Modify the report layout to generate the required Excel file.
Typically this will include

remove all total fields and minimise their bands.

create a line across the top of the title band with the column headings in the same order as the fields on the detail line.

2. remove all other fields from Title, Page and Group bands.

Remove all groups (Report – Data Grouping – Remove

3. Use the preview facility to ensure that there is a clear space between columns. And that the heading and details appear on one line.

4. Save the report by Ctrl+W.

5. Subsequently you can tidy the report from within Opera as usual by logging in as ADMIN

N.B.

Title Band

Each entry is a column header so use separate fields. Put them in the same order as the entries in the Detail band. The width of the field is irrelevant so you can squeeze many more on the line than if you were printing them.

Detail Band

There should be a separate field corresponding to each field in the Title. Put them in the same order as the entries in the Title band. The width of the field is irrelevant so you can squeeze many more on the line than if you were printing them.

Printer

To provide the maximum amount of space across the ‘page’, BS Publisher creates the new report for an A3 printer in landscape mode. The default is the Kyocera 9000. If you have no A3 printer you should install the Kyocera 9000 printer driver on your PC(s) and no other changes will be required.

If you already have an A3 printer which you would rather use, please follow this sequence.

1. Create or identify a report which defaults to the A3 printer in landscape mode.
2. In VFP or in BS Browse – use ? in 0
3. (in BS Browse, Click More)
4. Change ‘files of type’ to all files
5. Locate your modified report (sqxxxx.FRX)
6. Browse to the top line, locate column EXPR, double click to open it.
7. Write down or or snapshot the contents.
8. exit from VFP or BS Browse.
9. in BS Settings – compare the entries in l_bspub1 with your notes and change them to match.
10. Logon again to OperaII.

Column/Cell Format

BS publisher will format numeric quantities with 2 decimal places. Character fields will be left justified. If you want a particular format, you may have to change the report design.

Variables

Start by leaving all variables as they are. During initialisation of a report, the variables are listed. If there is a problem, the variable causing it will be the last on in the list. Note its name and ignore an error by typing Alt+i. You may have to repeat this if a variable depends on a previous variable’s presence or value. If this eventually allows the report to run you can consider removing them from the report from the report