Introduction

A report consists of a cross section of data, presented to the user in an artistic layout, with totals and other attributes. Fieldpine provides a generic report writer capable of extracting the information directly from the underlying database, or via the data model symbols provided by a running POS instance.

The report writer is designed to provide fast development of reports and superior run performance. The output is designed to be used by both users and automatic systems. Reports can be interactively designed in a web browser, or stored in pre designed report definition files for rapid recall. Stored format reports can be further tailored to suit individual requirements.

Running Pre Defined Reports

All reports are stored under the following URL, http://server.name:8095/reportdata.

Quick Start Examples

Example 1 - Simple Sales List

Start your web browser on the machine running your POS system, and enter the following URL.

http://127.0.0.1:8095/reportdata?data.sql=select * from sales&data.limit=8

If everything is configured, the report writer will display up to 8 sales header records from the database. In this case, all columns in the database are displayed, including some internal information that is not of relevance to many retailers.

Importing into Microsoft Excel

Microsoft Excel 2002 has a feature that allows Excel to upload data from Web Queries. When you use this feature linked to Fieldpine Report Writer, users can rapidly create Excel reports and analysis information, which dynamically change each time the spreadsheet is opened.

From within Excel, click on the menu options Data, then Import External Data, and finally New Web Query. A screen similar to the following will appear.

In the address field, enter the URL of the report you wish to import into Excel, and press Go. The report will be displayed in the Excel preview window. Once this is done, select the report by clicking the small arrow near the top left of the preview. Pressing Import will now bring this report data into Excel where it can be manipulated.

To learn more about this feature, refer to your Excel documentation.

Designing a Report

Reports consist of several parts, which cooperate to produce a final finished output, whatever that actual output presentation may be. A report at its most primitive is a user view of data stored in either the database or memory. A report is a visualation of the information presented in a form the user can comprehend and utilise.

Database. Technical view of information	-->	Selection. Select data matching user requirements (DATA)	-->	Format. Prepare data for user visual output (PRESENTATION)	-->	User. Consumes information on report
		Report Writer

Internally, the report writer consists of two parts that mirror the above flow. First, the data section selects and filters the data required. This part is focused on high speed selection and interacting with a variety of input sources, such as databases. The second part is the presentation component, which works with the output of the data section and formats the information into a form the user is able to interact with. The presentation component is concerned primarily with the display of information. When building a report, always consider whether you are trying to alter the data selection, or the presentation section, as this distinction alters how you relate to the report design.

DATA Section

The data section selects the data to be processed for presentation. Data can be drawn from several sources, including

• Direct database SQL statements.

• POS Data Model tree branches

• Output from other reports. This allows reports to be built on top of other reports, allowing complexity to be hidden in lower reports.

The data section is altered by prefixing the following keywords with data. in either layout files, or as arguments to the HTML request.

Keyword	Description
sql=String	Specify an SQL query to provide the base data source for this report.
report=String	Specify a previous report step to draw the input data from
limit=N	Set the maximum number of output rows to be selected from this data source.

Selecting from SQL tables

The quickest way to select data for presentation is to supply a SQL statement. The data section will run this query against the database and return the output to the presentation section. You need not create an SQL statement to completely present the report, as the presentation layer is able to fetch data model values from the POS to emblish the output.

http://127.0.0.1:8095/reportdata?data.sql=select * from sales order by completeddt desc&data.limit=8

PRESENTATION Section

Keyword	Description
hide=names	Hide the named columns from the report output. Where a select * is used in the data SQL, many columns may be represented that are not desired, and can be suppressed with the hide option.
show=names	Show only the columns named and in the order listed. When a select * is used in the data SQL and a show= value is specified, then ONLY the named columns from the show argument are shown, and all other columns are automatically hidden.
stylesheet=file	If the output style is HTML, insert the named file as a cascading stylesheet to use for the HTML page.
style=keyword	Sets the main output style for the report. While most reports are output in HTML format, other layouts are also possible, which changes the visual appearance of the output. Other options are: HTML Full HTML Web page output, this is the default. HTMLCONTENT Partial HTML output. The output consists only of the report component, and not the header and footers around the report. This style is used when the report is embedded within Web pages and is not generally used. CSV Output each report line in a comma separated file. XMLSS Output report in Excel Compatible XML format, allowing direct definition of Excel sheets. Not yet supported.
total=1	Automatic totalling of columns.
column.name<.i>.arg=value	Set column specific attributes, see below.
datatype.arg=value	Provide format options for a specific datatype. When data comes out of the data section, a variety of datatypes, such as number, money and string are used. The datatype argument provides defaults for how each column should present this format. Column formats load from the datatype format first, and this is then altered by column formats before finally being used.

Column Format Keywords

Keyword	Description
column.name.datamodel=value
column.name.formula=value
column.name.datatype=value
column.name.All Others=value	Any of the common formating options can be specified.

HTML Specific Keywords

When the presentation style is full HTML (but not HTMLContent), the following presentation keywords can be used to enhance the HTML output. For all other output styles, these keywords are ignored.

Keyword	Description
html.headline=value	Specify additional HTML lines to be inserted into the HEAD section of the HTML output page.
html.bodyline=value	Supply additional HTML lines to be inserted directly after the BODY tag, and before the report output is generated.
html.bodyendline=value	Supply additional HTML lines to be inserted beforew the closing BODY tag, after the report output is inserted.

Common Format Keywords

Keyword	Description
align=value	Sets the field alignment within the output cell. Valid values are left, right or center.
title=value
mask=value
html.td=value
html.td.open=value
html.td.close=value

Example

Using Layout Files

The data and presentation section options can be saved in a text file, with one argument per line. The complete report can then be run by calling up that report file. By convention, files are saved with the extension, .RPT

http://127.0.0.1:8095/reportdata?layout=myreport.rpt

When using a Web Browser to display the output, additional arguments can be supplied, which override the report file definition. For example, if the report file contains data.limit=40 and the user uses the following URL

http://127.0.0.1:8095/reportdata?layout=myreport.rpt&data.limit=10

The output of the above report run will only contain 10 output rows, not the original 40 specified.

Multiple reports can be contained within a single report file. Each report is identified by the keyline
report name
When a report file contains multiple reports, the user can select individual reports using the following syntax.

http://127.0.0.1:8095/reportdata?layout=myreport.rpt,report2