This section briefly outlines the different field settings available and what they can be used for.
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 |
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:
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)
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 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
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 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. |
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:
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.
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.
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) |
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))
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))
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
On this page: