The ability to produce charts, and sometimes display these charts as graphs, is a built in feature of the Report Generator. It is a simple yet effective means of providing powerful analysis of the database. The charts are designed using all of the tabs available on the screen exactly as described in previous topics with the exception of the format tab. The Format tab for charts is very different to the one used for other printed or displayed reports. Once complete the chart will have the appearance of a spreadsheet and it may be used as a basis for a screen based graph provided there are not too many elements.
What do you want to do?
As the production of a chart, instead of a printed or displayed
report, differs only in the way it is formatted, the Format tab will
be the only tab that needs discussion here. See the earlier topics
for details about tables, derived
fields, prompts and so on.
Set the output type on the Title tab to 'C' for chart.
The Format tab only requires the input of three elements; Row
information, Column information and Value information. The first two
of these must be character fields while the third has to be numeric.
There can be a maximum of 20 columns on the chart, with one column
for overflow and one for totals. A maximum of 50 rows is allowed with
one row for overflow and one for totals. This is mainly to allow the
chart to display properly in the limited space allowed on the screen.
More complicated charts should be produced on a spreadsheet
application designed for the purpose.
The chart option will allow you to output a table to a PC even though
it has more than 20 elements.
The following example show's how to display the outstanding amount on
the Sales Ledger by Sales Ledger Area code and by Sales Ledger
Account category code. Tip :
The first fields asked for by the Format tab are for the Row information.
Summarise by: Enter the field name, if it is known, in the
same way as you would enter a derived field name or a field name in
the Select tab. A field called CATEGORY from table number one would
be entered as !1_CATEGORY. This field must be character.
Row labels (blank for above): Enter the label for the row on
the chart. Leave the field blank for the row label to be defaulted as
the Summarise by: field.
Row axis title: Enter the row axis title.
Fill from table: Completion of this field will produce a row
for every possible code that could be used. See Fill
from table.
The first fields asked for by the Format tab are for the Column information.
Summarise by: Enter the field name, if it is known, in the
same way as you would enter a derived field name or a field name in
the Select tab. A field called AREA from table number one would be
entered as !1_AREA. This field must be character.
Column labels (blank for above): Enter the label for the row
on the chart. Leave the field blank for the column label to be
defaulted as the Summarise by: field.
Column axis title: Enter the column axis title.
Fill from table: Completion of this field will produce a
column for every possible code that could be used. See Fill
from table.
To explain this we will assume that we are going to produce a chart
of units sold by each sales person during a week. There are seven
sales executives and each must be represented on the chart.
The report will use two tables. The prime table is the New Vehicle
Stock Books table, and the second table is the Sales Executives table.
Each of the Sales Executives exist as entries within the Sales Exec.
table. This table enables us to put a name together with the Sales
Exec. code we are used to seeing on the system.
Let us assume that if the chart is produced on Tuesday, only three of
the Sales Executives have actually made a sale during that week. If
the chart is produced now it will only show those three people on the chart.
The fill-using attribute will allow us to get the Report Generator to
look at table number 2 (the Sales Exec table), and produce a column
on the report for each Sales Executive whether they sold anything or
not, purely because they exist in the Sales Exec table.
The chart will show four of the columns with zero values, but it will
be complete.
If we go back to the example we are using for the on-line help, we
are analysing the outstanding debt by Area code.
In the Tables tab there should be three tables; Table number one is
the SL.accts table, table two is the SL.areas table and table three
is the SL.ctgry table. These are Accounts, Area codes and Category
codes respectively.
The row is being defined as Area code. If you need to represent every
area code, whether it has a collective balance or not, you need to
enter '2' to represent table number two. This is a signal to the
system to search through the Areas table and include all areas on the
chart. If, however, you we only want to include Areas which have an
outstanding balance then you must enter 'N' for No.0
This completes the input for the Row information. The Column
information is entered in a similar fashion although, in this
example, the title will be different and the fill using would need to
show '3' to pre-fill the chart with all known Category codes.
Tip: Select the
Display value: Enter the Display value. This is the only
numeric table named on the tab. It could be a value from the prime
table or it may be a derived field.
Display image: Once the Display value field is named the image
should be agreed. As charts tend to deal with Totals rather than
individual amounts, decimal places on amounts are not always
meaningful. If they are not included it will save a considerable
amount of column space. Remember that the chart will display on the
screen, which has limited space, therefore the smaller the image the
better, if there are liable to be a lot of columns.
In some cases it may be necessary to divide the number by 100 or
1,000 in derived fields in order to make the chart display.
In this case an image of ### would display an amount up to 999, while
an image of -######.## would allow a number of +-999999.99.
Show zeroes: The Show zeroes check box will decide whether
zero values are to be displayed as a zero or as blank spaces.
Once this tab is defined, you should complete, compile and save the
rest of the report in the usual way.
See the Run Report generator option.
Once the report has been selected and the OK button clicked
the system will start to scan through the database and sort the
information required into the right order for the chart display. Once
complete you will be presented with a Select Item From Menu dialog
box with the following choices:
Display: The Display option will display the
chart on the screen.
Print: The Print option will print the chart to
the currently selected printer.
Graph: The Graph option will display the chart
in a graphical format.
Spreadsheet: The Spreadsheet option will allow you to output the data
to a PC file.
Select the Display option from the Select Item From Menu dialog box.
Click the OK button.
The display will immediately show the charted data with the row and
column headings to the left and top of the screen. If any of the
numbers are too large to fit into the image allowed, the screen will
print hash symbols in the place of the number. Increase the size of
the image if this occurs. You can click the scroll bars to view
columns or rows that may exist beyond the initial screen. Right click
on a Row or Column heading and select an appropriate chart, or graph.
You have the choice of a Pie-chart, A Bar-chart or a Cluster-graph.
Click on Exit to return you to the generated chart information
without launching a chart or graph.
Select the ESCAPE key to return you to the Report Generator main menu
Select the Print option from the Select Item From Menu dialog box.
Click the OK button.
Tip: If the chart is quite wide, it may be necessary to put
the printer into condensed mode.
Once the chart has been printed the Select Item From Menu dialog box
will be redisplayed.
Select the Graph option from the Select Item From menu dialog box.
Click the OK button.
When the system prepares the graph it will automatically use the
numeric value to populate the vertical axis, however it will offer
you both of the character fields for possible use on the horizontal
axis. Select whichever seems most appropriate and click the OK button.
The system will now ask you to select the row that you want to use
for the graphic presentation. Select the appropriate row and click
the OK button.
The system will now ask you which Graph type you want to display,
either Pie, Bar or Cluster. Select the appropriate graph type and
click the OK button.
Note: You may present each of the rows in turn or may elect to
display the totals.
Once a graph is displayed on the screen (Providing you have WDW
emulation) a number of Icon push buttons will be seen to be available
at the top of the presentation. To see a description of the usage of
each Icon move your mouse over each one without clicking on them.
Experiment as required to achieve the desired result.
More information about the formatting of graphs can be seen in the
topic Graph Control in the System Utilities on-line help.
The Chart option is a powerful tool when used properly. It requires a
degree of experimentation but, once mastered, it will prove a
valuable addition to the suite of reporting and analysis tools.
Some people are initially confused about the need to provide two axis
points and a value in order to produce a graph. The answer to this
confusion is that these three values are initially there to produce a
two-dimensional chart, the graph is a by-product of the chart.
Most common compilation and presentation problems are solved by
remembering the following:
You must have two character fields for the row and column, and a
numeric field for the element.
The number of components of the chart should be kept to a reasonable
number to enable a successful display of both the chart and the graph.
Formatting a chart
![[...]](Ellipsis.gif){kind=small}
This button is called an Ellipses . It provides a search facility.
Click on the button to search for the information that you require.
Enter the Row information
Enter the Column information
Fill from table
drop down select facility, adjacent to the Fill
from table field, to view / select from all available options.
Value information
Generating a chart
Display
Print
Graph
Presentation of the graph
Summary
