Graphical Analysis Maintenance

Document path Reference Contents > General Program Functions > Graphical Analysis Maintenance

Ad Hoc Graphs is a tool that enables you to produce graph displays of any data held in the system. There is a rich set of alternative graph types and variants (subtypes) of each type. You can design a graph that collects data from multiple search passes and each search can have up to 3 joined files and optionally a joined list file for drill-down data.

A graph consists of one series (in simple terms the x-axis) and at least one group (the y-axis). In the example below, sales data is shown by month. The series is the month, and the group is sales value. The data collected is sales history. The series is dispatch date subtotalled into months. The group is the value of sales and is accumulated as a sum for each series value (being the month).

Some graph types support more than one group. For example, sales and cost of sales. These can then be viewed beside eachother. In all circumstances there is only one series in which group values are accumulated.

Simple Graph showing Series and one Group.

Data is initially collected into a list in memory, much like a spreadsheet, that contains one column for the Series and a column for each Group defined. Each data record encountered creates one row, the values for which are calculated according to the series and group calculations.

The processes involved in drawing a graph are as follows, and in this order:

  1. Any search interface is offered and the search options and entries applied. A User Search will take precedence over a Search Interface.
  2. When activated, or drag-and-drop, ScratchPad Transfer or KeyWord Search applied with results, the graph design is tested to check that entries are logical and any calculations can be evaluated.
  3. If a User Search is set for the design, there will be no Search Interface, and that custom user search is set up.
  4. Pre-search program code is run, which allows for initialisation of constants, for example. Each search can have its own pre-search program code.
  5. Data is collected using the searches in sequence. Each search can apply up to 3 joins and each successful record set will add one line to the list in memory. If a Listfile is defined, any record of the listfile that is found adds a row to the list.
  6. Any Post Data Program code is then run, which allows for special raw data manipulation.
  7. If a series substitution list is defined, series value substitution will take place.
  8. The results list is sorted by series value then consolidated. This means that each group column is totalled into a single row for each series value using the subtotal options. Each group column is accumulated in an appropriate manner for the Total Mode chosen.
  9. The consolidated results are then sorted according to the design sort columns. The series is column 1, the first group column 2, etc.
  10. If there is a series format string, formatting will be applied and then, if a series format calculation is defined, that will be applied to the series values.
  11. The graph display object has the design attributes applied to it.
  12. The final result list is applied to the graph object and the graph drawn.
  13. Any Graph control program code is run, which allows for special settings to be applied.

The raw internal list of data that is generated from which the graph is drawn.

The results are consolidated, sorted and then the graph is drawn.

The window has six tab panes.

Texts

Graph

Options

Groups

Searches

Joins

Texts

Texts for the graph.

Texts can be simple texts but they can also contain variables which are evaluated at runtime. To do this prefix the variable or calculation with ## and suffix it with another ##. For example, a footnote of:

###60## records found.

will produce:

128 records found.

where #60 is a global variable containing the number of records found.

The following are a number of useful variables you could use:

Variable

Contents

#59

Number of series rows in the final list making up the graph.

#60

The number of records found that make up the graph.

#D

The current computer date.

#T

The current computer time.

dat(#D,#FDT)

The current computer date and time.

m_ReportSearch

The text description of the user interface search, when one is used. This is the text typically seen at the bottom of standard reports produced in a Print Reports window. For example: Standard search selection: Part number from "A" to "B".

m_Company

Your company full name.

m_UserId

The user initials of the person running the graph.

MCDSTCU

Your local currency (e.g. GBP).

MCDSTCT

Local currency full title (e.g. Pounds Sterling).

Field

Description

Main title

The main title for the Graph.

Sub title

A subtitle for the graph.

Footnote

A footnote for the graph.

Series title

A title for the data series.

Group title

A title for the data group.

X axis

A title for the X axis.

Y1 axis

A title for the first Y axis.

Y2 axis

A title for the second Y axis.

Z axis

A title for the Z axis on 3D graphs.

Show text option

Select from the dropdown list of text options.

Button

Action

Save

To save the changes you have made to the data file. This button is available on all tab panes.

Revert

To revert any changes to the previously saved version. This button is available on all tab panes.

Back to top

Graph

Graph settings and the extras that can be associated with it.

Field

Description

Major type

To set the type of graph. You can interactively change the major type on the graph, so experimentation is recommended.

  • 3D

    Typical 3D Graph

  • Area

    Typical Area Graph

  • Bars

    Typical Bars Graph

  • Line

    Typical Line Graph

  • Pie

    Typical Pie Graph

  • Special

    Typical Special Graph

Minor type

To set the sub-type of the graph. The list of options offered depend on the major type selected. You can interactively change the minor type on the graph, so experimentation is recommended.

Orientation

To set the orientation of the graph. You can interactively change the orientation on the graph, so experimentation is recommended.

Search group swap

To swap the series and groups. You can interactively swap on the graph, so experimentation is recommended.

Search Interface

The class name and parameters of the subwindow that will provide the user interface for data collection. This will be automatically selected, if available, after choosing the primary search main file.

There are 4 alternative primary data selection mechanisms you can use to collect data from the data file to populate the graph. Each primary search record can then be complemented by or with secondary selections, being joins or link file records. The primary searches can be:

  • Search Interface: This uses the standard selection subwindows that are used for producing reports in the program. So typically, if you are graphing parts the seach interface would be wPtmSelect. The subwindow would be available just as if you were producing a report from Masters -- Parts -- Print Reports. The user can then enter ranges, use KeyWord Search methods, Parts, Supplier and G/L ScratchPad drag-and-drop or Transfer, a Custom Search design or a combination of these.
  • User Search: This is the name you have given to a Custom Search designed for the same type as the Main File (see later) you choose for the first search.
  • ScratchPad drag-and-drop without user interface: If you enter one of PartsScratchPad, CustomersScratchPad, SupplierScratchPad or GLScratchPad (case-sensitively) into the User Interface you will be able to use ScratchPad drag-and-drop onto the graph to provide the primary selection for the graph. With PartsScratchPad you can also drag-and-drop from the Bill of Materials and Where-Used Listings window.
  • Entire File Contents: If you fail to enter anything in either the User Interface or User Search fields, all records from the main file of the first search will be used for the graph.

The following are available Search Interfaces for the appropriate primary search main file:

User Instructions

Any user instructions or help that will be shown above the search interface, whether or not you are using one.

User Search

Optionally the name of a user custom search for the primary search main file.

Warning WARNING: Any user that has access to the Graph Design must also have access to the User Search.

Sort columns

A comma separated list of column numbers.

Descending

A comma separated list of 0 or 1 associated with the Sort columns. 1 indicates descending order sort.

Series name

Dataname of the Series. It must start with a letter and not contain punctuation.

Series datatype

Select from the dropdown list of field types. The subtype list will be reset depending on your selection.

Series data subtype

Select from the dropdown list of data subtypes. The subtype list will be reset depending on your selection of datatype.

Series datalength

The maximum length for character fields.

Series Interval

The series subtotal interval. Number of characters for character fields or integer for numbers.

Series Interval Start

The series subtotal interval start for number datatypes.

Series date subtotal options

Select from the dropdown list of date subtotal options.

Back to top

Options

Options that can apply to the graph as a whole.

Field

Description

Series literal substitution list

Comma separated list of values and their substituted text, in pairs.

Post data collection processing program code

Omnis Studio code that will be run at the end of data collection. The results are in a local list variable named pList.

Graph control program code

Omnis Studio code that will be run after data collection and before the graph is redrawn. You can address the graph object using local reference variable named pGraph, and the data with pList.

For example, if you had previously saved a template of the graph (see Graph Functions Menu help) this can be loaded using the following where the full path of the template file must be given (if not, a file selection dialog will be presented):

Do pGraph.$loadtemplate('c:/My Documents/Graph.tdl')
Do pGraph.$ResetControls()

Tip TIP: You can use a form of relative (downward only) path addressing for the template path so that users on the network with different mappings (or operating systems) can see the template file at a common location. The syntax ##DATA## will substitute the directory of the datafile and ##VISION## will substitute the directory path of the user's Vision directory. For example:

Do pGraph.$loadtemplate('##VISION##\Reports\PieChart.tdl')
or
Do pGraph.$loadtemplate('##DATA##\Graphs\PieChart.tdl')

Series Format String

Optional series formatting string as used in a jst() function. The following justification syntax may be useful:

  • Syntax = Result
  • ^ = causes the data to be centre justified in the field
  • = places a sign in front of the data
  • $ = places a $ sign in front of the data
  • < = causes left justification, overriding the default
  • - = causes right justification, overriding the default
  • Pc = causes the part of the field not filled with data to be filled with the character c, E.G. -6N2P* will give **2.99
  • nX = causes the data to be truncated to a fixed number of characters or, if shorter, to be packed with spaces
  • U = causes the data to be converted to upper case
  • L = causes the data to be converted to lower case
  • C = causes the data to be capitalised
  • Nnn = causes the data to be treated as a fixed decimal number. If there is no nn parameter, then a suitable number of decimal places is applied
  • D = causes the data to be treated as a date
  • T = causes the data to be treated as a time
  • B = causes the data to be treated as a Boolean Yes/No
  • E = applies only to numbers and leaves the field empty when the value is zero
  • , = applies only to numbers and places a separating comma in thousands positions: E.G. N2, will yield 2,555,666.22
  • ( = applies only to numbers and places negative values in brackets: E.G. -22.88 with N2( will display (22.88)
  • ) = applies only to numbers and shows negative values with a '-' on the right: E.G. -22.88 with N2) will display 22.88-
  • + = applies only to numbers and shows positive values with a '+': E.G. 22.88 with N2+ will display +22.88
  • : = causes the following characters to be interpreted as a formatting string. This must be the last option since all characters following it become part of the formatting string. The meaning of the formatting string depends on the type of the data. This is particularly useful for date/time fields where the following characters can be used as in D:CY

    Y = Year in the form 01
    y = Year in the form 2001
    C = Century in the form 20
    M = Month in the form 06
    m = Month in the form JUN
    n = Month in the form June
    D = Day in the form 12
    d = Day in the form 12th
    W = Day of week in the form 5
    w = Day of week in the form Friday
    H = Hour in the form 0..23
    h = Hour in the form 1..12
    N = Minutes in the form 00..59
    S = Seconds in the form 00..60
    s = Hundredths in the form .00...99
    A = AM/PM in the form AM..PM

    For example "D:w, d n CY" will format as "Saturday, 29th November 2001"

    If the data is neither a date or a time, and the formatting string contains an X, the data value is inserted at the position of the X: For example, where the data is 0, "BC:The answer is X! will format as "The answer is No!"

    If the formatting string does not contain an X, then the formatting string is concatenated to the left of the data value: For example, with data 25.89, "-7N2:" will format as " 25.89".

Series Format Calculation

A calculation applied to the series for formatting purposes. #S1 must be used to refer to the series value before formatting. The following are examples:

  • mid(#S1,1,3) = truncates to the first three characters
  • upp(#S1) = uppercase like CALIACH
  • cap(#S1) = capitalise like Caliach
  • low(#S1) = lowercase like caliach
  • $cinst.$PeriodDesc(#S1) = the descriptor for the ledger period

Axis text formats

Alternative formats for data labes on the axis.

Back to top

Groups

Data group definitions. Groups are the data values for the graph.

Field

Description

Group List

List of data groups for the graph. Use the delete key to remove a group. You must have at least one group.

Group Name

The name of the group data column.

Groups need names so that they can be identified on the graph (unlike searches that need no names).

Group datatype

Select from the dropdown list of field types. The subtype list will be reset depending on your selection.

Group data subtype

Select from the dropdown list of data subtypes. The subtype list will be reset depending on your selection of datatype.

Group datalength

The maximum length of character fields.

Total mode

Select from the dropdown list of totalling modes for the group.

The data is collected into a list in memory, much like a spreadsheet, that contains one column for the Series and a column for each Group defined. The columns of group data are accumulated into subtotals for each series value. The Total Mode controls how the values in the groups are accumulated. There are a number of options. See the table below.

Total Mode

Accumulation process

Post-Accumulation process

Sum

Sum addition of all values.

None.

Average

The mean average of all values.

None.

Count

The number of records encountered by the searches.

None.

Minimum

The minimum of all values.

None.

Maximum

The maximum of all values.

None.

Growth Rate %

Sum addition of all values.

The percent change on the previous value. The first series result is always zero.

Difference Value

Sum addition of all values.

The actual value change from the previous value. The first series result is always zero.

Accumulate Value

Sum addition of all values.

The accumulating value. Each value is the sum of the groups previous values.

Percent of Total

Sum addition of all values.

The percent the value represents of the group sum.

Button

Action

Add Group

Click to add a further group.

Back to top

Searches

Searches on the data file to generate the data for the graph.

Data is collected for the graph by one or more Searches. The first search is ususally derived from the User Interface. The use of multiple searches enables you to collect data from more than one independent main file. For example you may want a graph showing sales and work processing activity. The first populating the first group, the later the second. The first, or primary, search uses the SAHFILE main file and perhaps the wSahSelect user interface. The second search would use the WOHFILE and a search calculation. The first search would have the second group calculation as simply zero, 0. And the second search would have the group 1 calculation 0.

For example, a simple search of sales history would have the Main File as SAHFILE and the Key Field as SAHDDAT which is the indexed dispatch date field.

Field

Description

Searches list

Searches are carried out in sequence. Select to maintain.

Pre-search program code

Omnis Studio code that will be run before the data collection search starts. Optionally used for preparation and setting up constants.

Search calculation

An optional explicit search calculation. If you enter a search calculation for the first search, this will overwrite any standard search set by the search interface or user search, if any.

Note NOTE: ScratchPad drag-and-drop or Transfer, and KeyWord Search Transfers when available in a Search Interface bypass normal search rules for the first search. This is because data is collected into a list prior to the graph data collection process. The list data is then used for the first search rather than the more normal datafile record finding process.

Main File

The main file to be used for the search.

Key Field

The indexed field to be used for the main find.

Series Calculation

A calculation used to generate the value for the graph series.

Group Calculations

A calculation used to generate the value for each graph group member.

Button

Action

Add Search

Click to add a further search.

Delete Search

Click to remove the selected search. There must be at least one search.

Back to top

Joins

For each search you can define up to 3 joined and one listing file. You must be careful to select the search first in the previous tab pane.

A join is the term used for making available data from files that are directly related to data already collected. For example, each sales history record is related to a specific customer, currency and country, all of which are recorded in seperate files. So if you wanted to view sales by customer's user reference, you would need to join the customer record to the sales history record:

1st Join File = CUSFILE
1st Join Key = CUSCODE
1st Join Calculation = SAHCUSC

Once a join is defined you can use fields from both the search main file and the joined file(s) in your search and group calculations. For the example above the series calculation would be CUSUREF and the group calculation for net sales value in local currency would be:

SAHPSUM*SAHQTY*(100-SAHDISC)/(100*SAHRATE)

Joins are one-to-one relationships, which means that there must be a related record in the join file with the exact key value, and typically no more than one. If no record is found nothing is recorded for the search main file record. If there is more than one, only the first found will be used. For example, if you were to join the part record with sales history, all non-part sales history would be ignored.

You can modify this behaviour to perform an Outer Join by checking the Outer checkbox. This allows the main file record to be recorded even if the exact-match join is not successful (when all fields of the join file are cleared). For example, sales, purchase and job line items may or may not have parts associated with them. If you want to join the parts file to sales history, without the outer option checked, only part line items will be included. With the option checked, all history records will be included.

It is therefore important that you choose the search main file and join files carefully. A good rule-of-thumb is that you choose the lowest-level file, with the largest number of records, as the search main file and then join master records to it. However, this can be somewhat inconvenient, especially with drag and drop from a ScratchPad.

While a join is a one-to-one relationship, a listfile is a one-to-many relationship. If you define a listfile, all records from that file matching the key calculation and listfile search calculation will be included.

For example, if you wanted a graph of the last 6 months sales for a customer (or customers) selected from the customer ScratchPad, make the search main file CUSFILE and key field CUSCODE (the user interface of wCusSelect will be automatically set), Then set the following:

Listfile main file = SAHFILE
Listfile key field = SAHCUSC
Listfile join Calculation = CUSCODE
Listfile search caculation = SAHDDAT>dim(#D,-6)

Where dim(#D,-6) is a function acting on today's date which increments the date by a number of months (in this case minus 6 months). Appropriate search calculation would be SAHDDAT (with appropriate subtotalling) and the group calculation would be the same as in the example above.

You can now drag and drop customers onto the graph to view sales history.

Another example of listfile use would be General Ledger history. You are most likely to want to control the graph by G/L account, but the data is held in the BUDFILE that does not lend itself to account selection. In this case you would set (search calculation is one line with no spaces):

Listfile main file = BUDFILE
Listfile key field = BUDNOMA
Listfile join calculation = GLACODE
Listfile search caculation = int(mid(BUDCODE,11,6))>=(MCDACPE-12)
     &(int(mid(BUDCODE,11,6))<=(MCDACPE-1))

The calculation gives you the last 12 closed periods of data.

Field

Description

1st join file

The first join file to be linked to the main file with the calculation below.

1st join key field

The indexed field in the join file whose value equals the result of the calculation below.

1st join outer checkbox

Check for an outer join. An outer join permits the inclusion of records where the join exact match fails.

1st join calculation

A calculation, the resulting value of which is used to locate the appropriate join file record.

2nd join file

The second join file to be linked to the main, and/or first join, file with the calculation below.

Note NOTE: If there is no second join main file, any third join defined will be ignored.

2nd join key field

The indexed field in the join file whose value equals the result of the calculation below.

2nd join outer checkbox

Check for an outer join. An outer join permits the inclusion of records where the join exact match fails.

2nd join calculation

A calculation, the resulting value of which is used to locate the appropriate join file record.

3rd join file

The third join file to be linked to the main, and/or first and second join, file with the calculation below.

3rd join key

The indexed field in the join file whose value equals the result of the calculation below.

3rd join outer checkbox

Check for an outer join. An outer join permits the inclusion of records where the join exact match fails.

3rd join calculation

A calculation, the resulting value of which is used to locate the appropriate join file record.

Listfile join file

Optional Listfile file to be linked to the main, and/or joins, file with the calculation and search below. The listfile join will find multiple records for data collection.

Listfile key field

The indexed field in the listfile file whose value equals the result of the calculation below.

Listfile join calculation

A calculation, the resulting value of which is used to locate the appropriate listfile file records.

Listfile search calculation

An optional search calculation applied to the listfile multiple finds.

Back to top

See also: -

Compiled in Program Version 3.10. Help data last modified 22 MAY 2008 08:24. Class wAdHocGraphsMaint last modified 27 FEB 2008 06:27:10.

Document path Reference Contents > General Program Functions > Graphical Analysis Maintenance