General Program Functions: Graphical Analysis Maintenance

#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).

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.

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.

Major type

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

• 3D

  

• Area

  

• Bars

  

• Line

  

• Pie

  

• Special

  

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 search 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:

• ASSFILE : Asset Register = wAcGlAssSelect : Asset Selection
• COAFILE : Commissions archive log = wCoaSelect : Sales Commissions Selection
• CPTFILE : Customer Parts = wCptSelect : Customer Part Selection
• CUSFILE : Customers = wCusSelect : Customer Selection
• DEAFILE : Marketing Businesses = wMarketSelect : Marketing Business Selection
• DEEFILE : Marketing Events = wMarketEventSelect : Marketing Event Selection
• ENGFILE : Engineering Data = wEngSelect : Engineering Data Selection
• GLAFILE : General Ledger Accounts = wAcGLSelect : General Ledger Account Selection
• INVFILE : Invoices = wInvSelect : Invoice selection
• JBBFILE : Job Bookings = wJbbSelect : Job Booking Selection
• JBIFILE : Job Items = wJbiSelect : Job Item Selection
• JOBFILE : Jobs = wJobSelect : Job Selection
• MRPFILE : MRP Results = wMrpSelect : MRP Results Selection
• NCRFILE : Non-Conformance Reports = wNcrSelect : Non-Conformance Selection
• POHFILE : Purchase Order Headers = wPohSelect : Purchase Order Header Selection
• POLFILE : Purchase Order Line Items = wPolSelect : Purchase Order Line Item Selection
• PPTFILE : Purchase Parts = wPptSelect : Purchase Part Selection
• PROFILE : Process Routes = wProSelect : Process Route Operation Selection
• PTCFILE : Part Current Costs Log = wPtcSelect : Part Current Costs Report Selection
• PTMFILE : Parts Master = wPtmSelect : Parts selection
• PUHFILE : Purchase History = wPuhSelect : Purchase History Selection
• QAHFILE : Quality Assurance Result Header = wQASelect : Quality Assurance Results Selection
• QAPFILE : Quality Assurance Part Tests = wQAPartSelect : Part Quality Assurance Selection
• SAHFILE : Sales History = wSahSelect : Sales Dispatch History
• SNOFILE : Product Serial Numbers = wSnoSelect : Serial Number Selection
• SNTFILE : Serial Number Tracking = wSntSelect : Serial Number Tracking Selection
• SOLFILE : Sales Order Line Items = wSolSelect : Sales Order Item Selection
• STHFILE : Stock Movement History = wSthSelect : Inventory History Selection
• STOFILE : Stock Locations and Quantities = wStoSelect : Stock Location Selection
• SUPFILE : Suppliers = wSupSelect : Supplier Selection
• TRAFILE : Ledger Transactions = wAcTraSelect : Transaction selection
• TRSFILE : Transfer Stock Details = wTrsSelect : Stock Transfer Selection
• WKOFILE : Work Orders = wWkoSelect : Work Order Selection
• WOHFILE : Work History = wWohSelect : Work Order History Selection
• WOSFILE : Shortages = wWosSelect : Shortages Selection

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: 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.

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 dialogue will be presented):

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

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##ReportsPieChart.tdl')
or
Do pGraph.$loadtemplate('##DATA##GraphsPieChart.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 labels on the axis.

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.

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.

Add Group

Click to add a further group.

Search number

Searches are carried out in sequence. Select to maintain.

Pre-search program code

Omnis Studio code that will be run before the data collection starts. Optionally used for preparation and setting up constants or otherwise setting values to drive the selection process.

Standard search generator context menu

Use this menu applies standard pre-search program code and Main table where clause.

Dynamic Query name

A Dynamic Query is a pre-defined data collection mechanism. Typically all reports that use a selection window use Dynamic Queries. They are stored in the Extras/Statements.db database. If you have no User Interface or User Search entered, the Dynamic Query will be the mechanism of data aquisition that will be ued. Others will be ignored. Click on the button beside the field to choose from a list.

Sort Set,Number

With a Dynamic Query the order of data returned can be controlled by a named Sort Set, just as typical report selection windows has radio buttons to allow the user to control the order. A sort set name followed by a comma and number will set the Sort Order just like the radio buttons do. For example, A parts report uses Cal_PtmPrint sort set and ,5 will order parts by stores preferred location (first radio buton is value 0).

Statement Shortcut name

Statement Shortcuts are pre-defined data collection mechanisms of a more targetted nature than Dynamic Queries. They are similarly stored in Statements.db and are generally work for any server engine. They often are much faster than Dynamic Queries as they target a more limited set of data. If you have no User Interface, User Search or Dynamic Query entered, the Statement Shortcut will be the mechanism of data aquisition that will be ued. Others will be ignored.

Bind variable row

Bind variables are a way of passing variable data into a fixed SQL statement in text. They can be used for Statement Shortcuts, SQL Text and Table and Join mechanism. For example, say you wanted to have a data view of dispatches yesterday. The where clause would need to be:

WHERE SAHDDAT='2015-01-10'

It would be very tedious to manually have to change the date each day. What we can do to solve this is change to a bind variable which would look like this:

WHERE SAHDDAT=@[iRow.C1]

and have a Bind variable row of:

#D-1~23~0~0

The bind variable is a column of a row variable which in this example is today’s date (#D) minus 1. The syntax used defines the data calculation for one or more columns and it’s type, subtype and sub-length as 4 entries separated by a ~ character. To add more columns repeat, so:

#D-1~23~0~0~#D-2~23~1000~0

Will create a two column row with C1 containing yesterdays date and C2 containing tomorrow’s date and time.

The permitted types are:

• 22 = Boolean always with ~0~0
• 21 = Character always with ~0~N for the max length
• 23 = Date or time (see below)
• 26 = Integer (see below)
• 25 = Number (see below)
• 24 = Sequence number always with ~0~0

Permitted subtypes are:

• For Date:
• ~0~0 = Date only
• ~1000~0 = Datetime
• ~6~0 = Time only
• For Integers:
• ~32~0 = short 0-255
• ~0~0 = 32 bit
• ~64~0 =64 bit
• For Numbers:
• ~0~0 = 0 decimal places to:
• ~14~0 = 14 decimal places (excluding 7,9,11 and 13)
• ~32~0 = short number 0dp
• ~34~0 = short number 2dp
• ~24~0 = floating point

WARNING: It is important for some servers that you have the appropriate type and subtype or errors may occur.

Select SQL statement text

You can write a raw SQL statement to drive the data collection. The statement must begin with the SELECT keyword and contain a FROM keyword. If you have no User Interface, User Search, Dynamic Query or Statement Shortcut entered, the Select SQL Statement Text will be the mechanism of data aquisition that will be ued. Others will be ignored. The following is an example:

SELECT SOLSONO,SOHDATE,SOLPRIC,SOLDISC,SOLSQTY,SOHRATE,SOLCUSC,CUSCNAM,SOHCREF,SOHAREF
FROM SOLFILE JOIN SOHFILE ON SOLSONO=SOHSONO JOIN CUSFILE ON SOLCUSC=CUSCODE

This will return every quotation and sales order line items. To limit it to Orders you could add a where clause:

WHERE SOHRELF

You can incorporate predefined Join and Where Shortcuts that will be expanded into the SQL statement before execution. For example the above FROM clause could be replaced by:

FROM =[Cal_SolSohCusAdrPtm]

Where Shortcuts can be included in the WHERE clause:

WHERE =[Cal_SoOnly]  AND SOLDATE=[Cal_Yesterday]

The above illustrates the two types of Where Shortcuts. The first simply is a subtitution of conditional logic. The second applies a set of logic to a particular column, in this case SOLDATE. The resulting SQL for SQLite will be:

WHERE SOHRELF AND SOLDATE>=date('now','localtime','start of day','-1 days') AND SOLDATE<date('now','localtime','start of day')

This will select all Order line items due for delivery yesterday.

Search Calculations

A calculation used to generate the value for each graph series. This is an Omnis calculation needing the iRow.<ColumnName> syntax.

Column calculations

A calculation used to generate the value for each result column from the raw results data delivered from the server.

Column name

The name of the result list column.

Column calculation

A calculation used internally to generate the value for each graph group member. Delivered columns must be refered to in the form iRow.<ColumnName> where iRow in this case is the list variable line of the results returned from the server.

Add Search

Click to add a further search.

Delete Search

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

Main table

The main table to be used for the search.

Find key column

The column to be used for ordering the data. This is only useful generally if a Limit is being applied (see below).

Where clause

You enter here the where clause elements to restrict the rows returned from the server. It is executed on the server so needs to include only functions and syntax that the server can understand.

A context menu is available with pre-prepared engine-specific where parts which will load into the field. Many of them involve dates and expect a column to be pre-entered. You enter a column, say SAHDDAT, operate the menu, say Last Week and the Where clause will be expanded and the Pre-search program code. In this case the Pre-seach will be set to:

Do $ctask.tSqlData.$CurDate() Returns iD
Calculate iRow.m_SelDate1 as dadd(kDay,-(dtw(dadd(kDay,pick(getws()-12,-6,0,-1,-2,-3,-4,-5),iD))-1)-1,iD)
Calculate iRow.m_SelDate0 as dadd(kDay,1,dadd(kWeek,-1,iRow.m_SelDate1))

The Where clause will be set to:

SAHDDAT>=@[iRow.m_SelDate0] AND SAHDDAT<=@[iRow.m_SelDate1]

The iRow here is a universal select row containing all the m_Sel… variables. Their values are set in the Pre-search code and the Where clase uses them in the form of bind variables (surrounded by @[…]).

1st Join table

The first join table that is related to the main table with the column relationship below.

1st Join key column

The column in the join table whose value equals the result of the equlity column(s) below.

1st Left Join

Check for a left join. A left join permits the inclusion of records where the join exact match fails.

1st Join equality column(s)

A column or combination of columns that when compared to the key column value is an equality when matched. This is executed on the server so if it includes functions they must be executable by the server engine employed.

2nd Join table

The second join table that is related to the main or first join table with the column relationship below.

2nd Join key column

The column in the join table whose value equals the result of the equlity column(s) below.

2nd Left Join

Check for a left join. A left join permits the inclusion of records where the join exact match fails.

2nd Join equality column(s)

A column or combination of columns that when compared to the key column value is an equality when matched.

3rd Join table

The third join table that is related to the main or previous join tables with the column relationship below.ow.

3rd Join key column

The column in the join table whose value equals the result of the equlity column(s) below.

3rd Left Join

Check for a left join. A left join permits the inclusion of records where the join exact match fails.

3rd Join equality column(s)

A column or combination of columns that when compared to the key column value is an equality when matched.

Listfile join table

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

Listfile key column

The column in the listfile table column whose value equals the result of the calculation below. Data is collected in the key column order with respect to any Limit and Reverse.

Listfile join calculation

A calculation, evaluated intermally, the resulting value of which is used to locate the appropriate listfile file records. It must be an Omnis calculation that references the columns returned by the Main and/or Join tables using iRow.<ColumnName> syntax. For example:

con('''',iRow.SOHSONO,'''')
con('''',dat(iRow.SOHDATE,'y-M-D'),'''')

When evaluated this forms the initial where clause part for the Listfile select, which is ANDed to the Listfile Where clause, if entered.

Listfile where clause

An optional where clause applied to the listfile select.

• Graphical Analysis
• – Introduction to Data Views