HaloCRM Guides

Guides > Configuring Custom Appearances in Reports (using HTML)

Configuring Custom Appearances in Reports (using HTML)

Overview

This guide outlines how to create reports with custom appearances using the "Appearances" tab within reports and custom HTML.

To initiate the custom appearance of a report, navigate to the desired report and the "Appearances" tab. Check the checkbox, "Customize Table HTML" to get started. From here you will notice four boxes/blocks where HTML can be pasted into to apply custom appearances.

Report Table HTML
- To configure the appearance of the table's column headers.
- Plain text should be used to name columns.
- Use $REPORTROWS to print the next HTML block (the report table rows).
Report Table Row HTML
- To configure the appearance of the table's column's values.
- You are able to use variables to print the column values on each row. Spaces in the column name should replaced with underscores ("_") in these variables.
  - E.g. A column with the name "Ticket ID" should be replaced with the text, "$Ticket_ID" to be used as a variable to generate the cell's value in this HTML block.
Report Table Group HTML
- To configure the appearance of the grouping value(s) for the report. This HTML will only take effect if there are columns in the report which are set to be grouped by.
- Use $GROUPHEADER to print the grouped value.
- Use $GROUPCOUNT to print the count of entries in each group.
- If there are multiple groupings in your report, you can number the variables used like...
  - $GROUP1HEADER, $GROUP2HEADER, etc.
  - $GROUP1COUNT, $GROUP2COUNT, etc.
Report Table Total Row HTML
- To configure the appearance of the totaling value(s) for the report. This HTML will only take effect if there are columns in the report which are set to be totaled.
- The variable for this HTML block works exactly the same as the Report Table Row HTML block.
  - E.g. A column with the name "Hours Logged" should be replaced with the text, "$Hours_Logged" to be used as a variable to generate the cell's totaling value in this HTML block.

Example Report / Basic HTML Blocks

Let's take a basic report like shown below and go through all of the HTML blocks.

Report Table HTML

<table>

<tr>

<td style="background-color: #2BD3C6; color: white;">Team</td>

<td style="background-color: #2BD3C6; color: white;">Agent</td>

<td style="background-color: #2BD3C6; color: white;">Hours Logged</td>

</tr>

$reportRows

</table>

Only adds column headers until the Report Table Role HTML until the next block is filled out

Report Table Row HTML

<table>

<tr>

<td></td>

<td>$Agent</td>

<td>$Hours_Logged</td>

</tr>

</table>

Notice the blank row to give space for the grouping added later.
Adds column values.

Report Table Group HTML

<table>

<tr>

<td style="background-color: #AAEDE8; color: black;" colspan="100%">$GROUPHEADER ($GROUPCOUNT)</td>

</tr>

</table>

Adds the grouping labels.

Report Table Total Row HTML

<table>

<tr>

<td style="background-color: #AAEDE8; color: black;"></td>

<td style="background-color: #AAEDE8; color: black;">$Hours_Logged</td>

</tr>

</table>

Notice the two blank columns used in order to position the total under the correct column.
Adds the totals underneath each grouping.

This example is now complete.

Advanced

The example shown above was quite basic in showing each of the HTML blocks. Let's go through an example of adding a link to the report as this is quite commonly requested for in custom reports. Let's say we want a link to the agent profile page of the agent shown in the report embedded into their name located in the Agent column.

Firstly, we need to be able to build the link to be dynamic to the agent we're clicking on. I know that the agent pages in configuration correlate with the ID of the agent we're viewing. Given this information, we must expose that information in the report as a column to then be used in the HTML to build the link.

Initial SQL query

SELECT

sectio_ AS [Team],

uname AS [Agent],

ROUND(SUM(timetaken), 2) AS [Hours Logged]

FROM faults

JOIN actions ON actions.faultid = faults.faultid

JOIN uname ON unum = assignedtoint

WHERE

timetaken > 0

GROUP BY

sectio_,

uname,

Adjusted SQL query

SELECT

sectio_ AS [Team],

uname AS [Agent],

ROUND(SUM(timetaken), 2) AS [Hours Logged],

unum AS [ID]

FROM faults

JOIN actions ON actions.faultid = faults.faultid

JOIN uname ON unum = assignedtoint

WHERE

timetaken > 0

GROUP BY

sectio_,

uname,

unum

Note that I don't have to actually show the column in the resulting report to be able to use the value in the HTML. However, please be sure to add the new field to the the list of fields exposed from the SQL query in the resulting report to the "Fields" tab. This allows the HTML variable for my new column ($ID) to be used.