Changing Chart Labels, Titles and Data Display with Formulas

Use the Formulas tab in Design Mode to change values and titles shown on the chart.

The chart title formula is usually a simple text string.  Labels and values sometimes may contain more complex formulas.  For example, you may show a name (text) instead of an ID value:

Only fields included in the Data Source are available for use.  By default, if you selected a primary key field as an index field in the Charting Wizard’s Options step, all non-binary and non-large text fields will be included in the Data Source and are available for use in formulas.  However, if the index field is not a primary key, only the index field will be included.

You can include, change or exclude fields and aggregate functions in the Query Wizard in the Data Sources tab.  Note, that adding fields potentially changes the result set because the SQL query must include a GROUP BY clause for all non-aggregate (those without functions) fields.  Accordingly, the only case when final values will not be affected is when the original index field is unique (a primary key, for example).

Formulas are useful for implementing Display As (display foreign key as) functionality.  For example, to create a chart of total sales per product category, you might want show category as category name, rather than as an ID value.  In this case, select Category / CategoryID as an index and than modify the formula.  Note, that same result set is possible to get if Products and CategoryID is selected, but in this case despite exactly same values for SUM only CategoryID field is included in the Data Source. So always better strategy to select primary key from the parent table instead of selecting foreign key field in the child table for index field.

Data formulas

Similarly you can modify formula for data display.  By default, the data formula fetches data from a Data Source via a LOOKUP() function, but you can select different data if your Data Source contains more than one aggregate function, and you can modify the value with arithmetic operations.  Also if you elected to show values as percentage in the Charting Wizard, the Data Source retrieves values values and calculates percentages.

You can modify the display format of the data values, but make sure that your data’s formula always returns a numeric value.

To verify which Data Source your chart uses, look either in the Property Sheet for the Data Source name property or in the Data Sources tab:

Note: when you modify your Data Source, especially when adding or removing fields, you must ensure that any formula using the Data Source is appropriately updated appropriately.

Using chart formulas in code customizations

These formulas are used when populating arrays of labels and values for a chart using one of the following methods:




For example:

Dim indexArray() As String = UnitPriceSumQuery.GetComposedStringColumn("""Employee :"" + LastName + "" "" + FirstName", EvaluateFormulaDelegate, EvaluateFormula("Resource(""Txt:RemainderLabel"")"))

Where the first string is a Formula. These calls are generated automatically but could be overridden in Set…Chart method on a Code tab for a chart.

See Also

Configuring Charts with the Charting Wizard