Advanced Field Settings
Advanced Properties
This section briefly outlines the different field settings available and what they can be used for.
Properties Table
| Property | Description |
|---|---|
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 |
Italic | Formats the values in this column to Italics |
Bold | Formats the values in this column to Bold |
Width | 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 |
URL | 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 |
Expression | 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: https://matt.accessplanit.com/accessplan/futureplan/pagehost.aspx?phid=203&id=12432
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:
https://matt.accessplanit.com/accessplan/futureplan/pagehost.aspx?phid=203&id={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
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:
- Type in the Data Sources used within Data Sources tab the report (e.g. ap_invoices_view).
- These then need to be separated with a full stop to detail the field name used in the fields tab in the report.
- Specifying the fields in this way allows the report to understand exactly which field from which table it should calculate.
- Wrap the Data Source and fields in parentheses to group them together.
- 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
| Expression | Full Expression |
|---|---|
| + | expression + expression |
| - | expression - expression |
| / | expression / expression |
| < | expression < expression |
| <= | expression <= expression |
| <> | expression <> expression |
| = | expression = expression |
| > | expression > expression |
| >= | expression >= expression |
| AND | boolean_expression AND boolean_expression |
| AVG | AVG (expression) |
| BETWEEN | BETWEEN (test_expression, begin_expression, end_expression) |
| CASE WHEN...THEN...ELSE...END | CASE WHEN (boolean_expression) THEN (result_expression) [...n] [ELSE (else_result_expression)] END |
| CASE...WHEN...THEN...ELSE...END | CASE (input_expression) WHEN (when_expression) THEN (result_expression) [...n] [ELSE (else_result_expression)] END |
| CAST...AS | CAST (expression AS data_type) |
| CONCAT | CONCAT (expression, expression[,expression...]) |
| CONVERT | CONVERT (data_type [( length)], expression[, style]) |
| COUNT | COUNT (expression) |
| DATEADD | DATEADD (datepart, number, expression) |
| DATEDIFF | DATEDIFF (datepart, startdate, enddate) |
| DATEPART | DATEPART (datepart, date) |
| DISTINCT | DISTINCT (column) or DISTINCT column |
| GETDATE | GETDATE () |
| IF...THEN...ELSE...END | IF (boolean_expression) THEN (true_expression) [ELSE (false_expression)] END |
| IIF | IIF (boolean_expression, true_expression, [false_expression]) |
| ISNULL | ISNULL (check_expression, replacement_value) |
| LEFT | LEFT (expression,number_of_characters) |
| LEN | LEN (expression) |
| MAX | MAX (expression) |
| MIN | MIN (expression) |
| OR | boolean_expression OR boolean_expression |
| ROUND | ROUND (expression, length[, function]) |
| RUNNING AVG | RUNNINGAVG (column) |
| RUNNING COUNT | RUNNINGCOUNT (column) |
| RUNNING SUM | RUNNINGSUM (column) |
| SUM | SUM (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":
CASE WHEN (users.surname is NULL) THEN ('NO LAST NAME') ELSE ('HAS LAST NAME') END