The first step in creating a report is to select a main table to
report on from those in your application's file list (filelist.cfg).
You can do this either through the Main Table combo box, or
by using the Table selector button to
the right of it. This will enable
the various controls and allow you to proceed with defining your
report. You may wish to give your
query a title at this point as well.
To the right of the Main Table combo are four buttons. From left to right these allow you to:
You can select any fields from your main table or any of the tables that relates to, whether directly or through the releationship tree (with the exception of any tables or fields which have been excluded by being prefixed with an "@" sign, or have been excluded programmatically). Click on a table in the "Tables" list to see the fields for that table in the "Fields" list.
Fields can be added, inserted or deleted. A double-click on a field will add it, or you can use the buttons.
You can also create "Expressions", which are calculated fields, although this feature is experimental and should be used with care. When creating an expression you will be asked for a "long" and a "short" label for it (the short will default to the long), but currently the long label is not used for anything, while the short label is used as the column heading. The "width" of a calculated column will only be important if you are downloading it as a "text" report, however setting the "Return type" and "Decimal places" (if it is numeric) correctly will be important in most forms of output. The "Edit" button will then open the expression editor which will allow you to construct the expression for your calculated field.
You can add totals on numeric fields (the "Sum" column) and break report lines over physical output rows (the "CR" column).
You can change the column headings which will be used in the report by changing the default field names in the Printed Fields list to be what you require - a good idea if you are including the "Name" column from several different tables for instance.
You can select from the data to be reported on based on the values in any of the fields available to you - click on a table in the "Tables" list to see the fields for that table.
Selections can be based on a number of operators: the usual equal to, greater/less than, greater/less than or equal to, not equal to, but also string includes or does not include ("CIN"), in range ("between") or not in range ("CBT"), or a user defined list ("in list").
Once you have chosen a field to filter on and a selection operator, the "Default" button will allow you to supply the appropriate value(s).
You can also define a single selection expression which should evaluate to a boolean (true/false) value. As with calculated fields, selection expressions are still somewhat experimental, so use with care.
Selections can be added, inserted or deleted. A double-click on a field will add it, or you can use the buttons.
As with the printed fields, you can change the names of the selection fields to what you require, although these will only appear if you include the selection criteria in the report.
You can select any of the indexes defined for your main table to order your report by, or select the "Ad hoc index" option, which will enable the "Ad hoc" button, which in turn will allow you to create an ordering based on any of the fields from any of the available tables. Be aware however that this will involve the data being sorted in memory, which may take a long time if there are a large number of rows in your report.
The "break" column will allow you to insert "breakpoints" in your report on a change of value in that field, producing a subheader and, if you have any totals defined, subtotals. You can have multiple break levels (although more than around three would seem extreme).
You can add fixed text which will appear before and after your report. To create multi-line texts you need to use Shift-Enter to move to the next line as most web browsers interpret the raw "Enter" key as an instruction to "Submit" a form.
In HTML reports
these texts can potentially be used to include arbitrary HTML elements
into the start or end of your report. Such things as hyperlinks or even
images can be inserted this way (with images this will only work when
viewed in the browser, or if a fully qualified URL rather than a
relative one is used for the image source). See the "Vendor list with
image" report for an example of this.
On the output tab page you can customise the appearance of your report. In most cases this customisation will only apply to HTML output (either viewed in the browser or downloaded as HTML). However such items as the inclusion of selection criteria, printing "totals only", printing a record count or printing the date and time the report was generated will also apply to text downloads. Printing totals only will produce no output whatsoever if CSV or XML output formats are selected (these do not include totals).
The choices of colour for text, background and highlight can be made using colour names, or colour codes (preceeded with the "#" sign; both six hex-digit - #FFFFFF - and three hex-digit - #FFF - forms should work), or by using the colour selector dialog (click on the "prompt" icon or on the coloured button beside each entry).
The "Use SQL" checkbox will allow the TableQuery engine, which WebQuery uses for extracting its data, to use embedded SQL if running on an RDBMS such as Microsoft SQL Server®, Oracle® or MySQL®, which may make some queries run much faster, however there may be issues with some data type conversions. It will have no effect if running on the DataFlex embedded database.
You can chose either to view your report in the browser or to download it to a file on your local computer.
If you choose to view it then that can be in a pop-up window (which will work in all browsers), or in a new browser tab (currently the Google Chrome browser opens this in a new window instead), or in a new browser window, however browser settings and pop-up blocking add-ins may stop these latter two from working - you may have to adjust those to get this to work.
If you choose to download your report, you can select between HTML, Text, CSV (comma-separated variable: a useful format for loading into spreadsheets), or XML. Just what your browser does with it then will depend on your browser and platform, but in most cases you will be offered the choice to either "open" or "save" the file (Google Chrome will only offer "save").
Once you have your report working the way you want it, you can save
it for later use. Click on the third of the four buttons to the right
of the "Main Table" combo box: .
If you are not logged in, you will only have "Public Queries", which all uers will be able to access (and overwrite or delete), available as a location, however if you are logged in you will also have the choice of "My Queries" where you can save your report as private to you. You can create subdirectories in either of these locations (and below them) to help you organise your saved reports.
Saved reports can then be loaded using the second button to the right of
the "Main Table" combo box: .