Advanced Field Settings

Advanced Properties

This section briefly outlines the different field settings available and what they can be used for.

Properties Table


Column Group

Define which parent column this column belongs to

Break Page After VG (PDF)

Not used 

Hide this field

Hides the field from the report completely

Sort (z-a)

Sorts the values of this column from Z-A


Formats the values in this column to Italics


Formats the values in this column to Bold


Sets the width of a column to px or pt

Label Justification

Aligns the column header to the left, middle or right

Value Justification

Aligns the column values to the left, middle or right


This field can be used to turn the value into a hyperlink

Subtotal Function

Changes the subtotal value to be a Count, Sum, Average etc

Gradient Cells Shading

Applies a gradient shade to the column which gets darker for higher values

Text Highlight

Changes the text colour for certain values or value ranges

Cell Highlight

Changes the cell colour for certain values or value ranges

Value Ranges

Converts certain values to a defined value


Perform complex calculations using logic

Expression type

The format the result of the above expression shows as (e.g. Date, Time, String, Binary)

Group By Expression

Groups the report by the output of the above expression

Column Group - Use Case

Columns in the report can be grouped together by entering a column group name in this field. 

Entering the same name in other columns will group multiple columns together.

In the example below, entering "Course Date Details" into the column group for the first four fields and "Candidate Details" for the last two groups the fields together like so:

Width - Use Case

Although columns in report writer do extend to show the values they hold, columns can be set at a fixed width.

The value needs to be entered in pixels (px) or points (pt)

URL - Use Case

Values in reports can be converted into a hyperlink which can help to provide links to web pages or pages within accessplanit.

This can be done by entering the URL for the page in accessplanit along with the wildcard to enter the report value into the URL.

The URL for a page can be found by opening the page in a new tab. The URL for that page appears in the browser.

In the example below, a basic course date details report can be altered to give a link directly to the course date in accessplanit:

First, navigate to a course date and open the course date in a new tab.

The URL details the unique reference for the course date (coursecalid) at the end of the URL:

Having the field "coursecalid" in the report allows you to enter a URL against this field that will add the coursecalid in the URL.

Following the same format as the URL, replacing the coursecalid with the wildcard will change the coursecalid into a hyperlink:{1}

Text Highlight - Use Case

Text highlight can be used to change the colour of certain values within the column.

First, specify the value, then after using a colon, specify the colour.

Multiple values can be changed at the same time by separating each change with a semi colon

Cell Highlight - Use Case

Much like text highlight above, the same principle can be used to change the colour of cells in a column that match certain criteria.

First, specify the value, then after using a colon, specify the colour.

Multiple values can be changed at the same time by separating each change with a semi colon

Value Ranges - Use Case

Value ranges uses the same principle as the two highlighting methods above. However, this is used to convert one value into another.

First, specify the value, then after using a colon, specify the Value you would like to show instead.

Multiple values can be changed at the same time by separating each change with a semi colon.

In the example below, the Venue Country field has been altered to convert the value "United Kingdom" into "UK"


Expressions can be used to perform complex calculations between fields that contain numerical values or to combine multiple text fields together.

They need to be created using a combination of algebra and Standard Query Language (SQL).

Expressions can be a really useful tool when the basic calculations in Report Writer are not flexible enough to meet your needs.

It is important that the Data Sources and Fields used in the expression exist within the report. Expressions can only function if the Data Source is being used.

Expression Fundamentals

When an expression is entered within the setting area against a field, the field used is irrelevant as the expression will change the values.

Example: In "Expression example 2" the field used was for course start date which, due to the expression, performed a calculation on invoices.

To create expressions, you will do the following:

  1. Type in the Data Sources used within Data Sources tab the report (e.g. ap_invoices_view).

  2. These then need to be separated with a full stop to detail the field name used in the fields tab in the report.

  3. Specifying the fields in this way allows the report to understand exactly which field from which table it should calculate.
  4. Wrap the Data Source and fields in parentheses to group them together.
  5. From there, SUM, COUNT and AVERAGE functions should be listed before the field.

In the example below, the values in the column will show the course date income, minus the SUM of all resource costs for that course date.

If the report has functions assigned within the fields tab (below), the tick box "Group by expression" will also need to be selected.

Creating an expression

In the example SUM (ap_resourcerequirements_view.cost) will add all of the resource requirement costs into one value.

This can then be combined with the plus, minus, divide, or multiple symbols (+,-,/,*) to perform a calculation between the two fields:

(coursescalendar.income) - SUM(ap_resourcerequirements_view.cost)

Complex calculations can be created to yield different results.

Available expressions

ExpressionFull Expression
+expression + expression
-expression - expression
/expression / expression
<expression < expression
<=expression <= expression
<>expression <> expression
=expression = expression
>expression > expression
>=expression >= expression
ANDboolean_expression AND boolean_expression
AVGAVG (expression)
BETWEENBETWEEN (test_expression, begin_expression, end_expression)
CASE WHEN...THEN...ELSE...ENDCASE WHEN (boolean_expression) THEN (result_expression) [...n] [ELSE (else_result_expression)] END
CASE...WHEN...THEN...ELSE...ENDCASE (input_expression) WHEN (when_expression) THEN (result_expression) [...n] [ELSE (else_result_expression)] END
CAST...ASCAST (expression AS data_type)
CONCATCONCAT (expression, expression[,expression...])
CONVERTCONVERT (data_type [( length)], expression[, style])
COUNTCOUNT (expression)
DATEADDDATEADD (datepart, number, expression)
DATEDIFFDATEDIFF (datepart, startdate, enddate)
DATEPARTDATEPART (datepart, date)
IF...THEN...ELSE...ENDIF (boolean_expression) THEN (true_expression) [ELSE (false_expression)] END
IIFIIF (boolean_expression, true_expression, [false_expression])
ISNULLISNULL (check_expression, replacement_value)
LEFTLEFT (expression,number_of_characters)
LENLEN (expression)
MAXMAX (expression)
MINMIN (expression)
ORboolean_expression OR boolean_expression
ROUNDROUND (expression, length[, function])
SUMSUM (expression)

Expression example 1

In the example below, a SUM of course date Net profitability + a SUM of course date Tax is added together to give a total income.

A SUM of all fees is added to the SUM of all resource requirement costs and then finally a SUM of all venue costs.

These two figures are then calculated give a final total income figure minus course date costs:

((SUM(ap_coursescalendar_viabilityitems_view.calculatedvalue) + SUM(ap_coursescalendar_viabilityitems_view.calculatedtax))


(((SUM (Fees.amount) + SUM(resourcerequirements.cost) + SUM(ap_venues_view.cost))) * -1))

Expression example 2

In this example, the course calendar viability items are used to collate information about income of course dates minus expenses:

((((SUM(ap_coursescalendar_viabilityitems_view.invoicedvalue) + SUM(ap_coursescalendar_viabilityitems_view.invoicedtax)) * 6.15)-(SUM(ap_coursescalendar_viabilityitems_view2.invoicedvalue) + SUM(ap_coursescalendar_viabilityitems_view2.invoicedtax)))


(SUM(ap_coursescalendar_viabilityitems_view.invoicedvalue) + SUM(ap_coursescalendar_viabilityitems_view.invoicedtax)
* 6.15))

Expression example 3

In this example, we want to check whether the 'Last Name' field is empty, and if so, show the text "NO LAST NAME", else show "HAS LAST NAME":