Using Formulae, Functions And The Flash Builder

When analysing and grafing your data in graf-fx, the software has been designed to utilise the power of functions and formulae in the queries that are generated by the system.

Formulae To enter a formula as a field for displaying data, you can modify the data source of the data to a valid choice of your own. Following are examples of a number of formula that could be used for analysing data in graf-fx. To enter the formula, do not choose from the pull down choices but type the field names in manually and enclose the field names in brackets.

Note that more complicated but valid functions may not be supported directly by graf-fx grafs and queries but can be worked through by clicking on the button as follows where queries can be modified and grafs can opened after modifications. If you have trouble entering a formula in the space provided, right click on the field to bring up the Zoom option.

Formula will be shown using Expr1001 type column names which can only be modified by using the See Sql advanced settings. Generally, Graf-FX will attempt to give a useful name to the column name in both the queries and titles.

Aggregate Functions

Access is powerful in its support of functions in the Group By consolidation component of the drilldown queries. This Group By Combines records with identical values in the specified field list into a single record. The usefulness of utilising a function in a Group By is that alternative means of comparing data can be used rather than just the fields that currently exist in a table.

A really useful example is the consolidation of dates where showing information that is collected daily grouped by date would be fairly useless as one years data would show 365 columns to be graffed. So you change the group by consolidation to show the totals for all data in a quarter which would break down the daily data into 4 totals.

Use the example that follows to practice using consolidation function. The Quick Advice window pops up when you select a Date field. You can hide it by clicking on it.

Now in the group by field, manually enter the function format([SalesDate], "mmm") and run the drilldown query on level 1 (the yellow ?)

You can enter many valid Access functions that relate to data in the table or query that you are reviewng.

Flash Builder

To make the building of useful consolidation functions easier, Graf-FX has a flash builder that generates special Access functions that can used. These functions are all part of a library of functions that come with the product. The builder will throw up a special form that offers a variety of selections depending on whether the field is a Date, Number or String type. Graf-FX checks the system tables to work out what data type a field is.

To start the Flash Builder, click in the Group By field and select the button or Right Click in the GroupBy field and choose FlashBuild from the shortcut menu as follows

This then brings up a form that looks like this for Date fields.

To select the Calendar Year Quarters functions, click next to the Calendar year option selector and the Quarter (1-4) Selector. When you have chosen the function, click on the button which will return the function to the launchpad form as follows.