HaloITSM Guides

In this lesson we will cover:

- Adding a Report to a PDF Template

- Filtering reports for a specific entity

PDF templates are used to apply set formatting to various entities in Halo.

The following entities can also have reporting data added to their template:

• Tickets
• Quotations
• Sales Orders
• Purchase Orders
• Invoices 
• Agreements 
• Assets

Adding a Report to a PDF Template

First you will need to choose the report you would like to appear on your chosen template. To do this head to Configuration > Reporting > PDF Templates, here you can change the entity you are viewing templates for on the top left.

To change the entity you are viewing select the drop down and choose another entity. 

Fig 1. Template Entity Selection

Now select the template you would like to edit, once inside the template, under the details tab, you will need to set which report you would like to be visible on the template. Select 'Add' on the table to add a new report. 

Fig 2. Report selection Table

Now you will be prompted to search and select the report you would like to add, you can search either by report name, or report ID. Once selected your chosen report will appear in the table, and the variable for the report will be given. 

Fig 3. Report Variable

Now the report has been chosen and the variable is available, we need to edit the template pages to include this variable. Head to the 'Pages' tab and add a new page.

Fig 4. Adding a new page to the template

When adding the page the editor screen will appear. By default the HTML from the 'Main Page' will be copied over into the new page, remove any information that you do not want to appear on the second 'report' page. To have report data pull through we need to format the data to be in table form, and give it a title.

Here is some basic formatting you can use to copy and paste into your template HTML:

    <h1>REPORTNAMEHERE</h1>

            <div>

        </div>

        <div style="margin-top:30px">

            <table class="detail-table">

                $REPORT123DATA

            </table>

Fig. 5 Example HTML formatting for report table

If you are using the above, you will need to replace 'REPORTNAMEHERE' with the title of your report and replace '$REPORT123DATA' with the variable of the report you want to pull through. 

Once set ensure you save the HTML on the page you are editing, then you can use the 'Preview Print' function to see what the template will look like with the report data.

Fig 6. Preview Print function

Now when this template is applied to an entity, the report data will appear on the PDF. If any changes are made to the report, this will automatically update the report on the template, including if any filters are applied to the report. 

Filtering Reports For a Specific entity

If most of your reports are data overviews, showing data for various customers/suppliers/assets etc. you may want the report that shows on the PDF to be filtered so it only shows the data for the invoice/purchase order/sales order/customer etc. it is on. This can be done by adding a filter to the report using a $-variable. We will work through this with an example. 

Example 1

I have a report I would like to appear on all my quotes, this report shows a summary of all the outstanding quotes in my Halo. However, at the moment the report shows all quote details across all customers, I would like it to just show the quote details of the quote the template is being applied to. 

To do this I will need to add the following line to my report script:

This will filter my report so that when a PDF for this quote is generated it will only show data for the quote that PDF is for. Note that this query requires the report to use the 'quotationheader' table as the data source, or this table be joined onto the report. 

The variable used to filter the report in this example is $QUOTEID, this will change based on the entity your report/template relates to. The variable used to filter the report can be found on the template itself, above the reports table, see Fig 7. 

Fig 7. Variable that can be used to filter the report on the template 

Example 2

I have a report I would like to appear on all my invoices, this report shows all the outstanding invoices for all my customers. However, I would like this to only show the outstanding invoices for the customer the invoice is for. At the moment my report looks as shown in Fig 8.

Fig 8. Outstanding invoices report for all customers

To do this I will need to add the following query to my script:

WHERE Ssitenum = (SELECT usite FROM users WHERE uid = (SELECT ihuid FROM invoiceheader WHERE ihid = $InvoiceID))

This query filters the report to only show outstanding invoices for the customer linked to the invoice the report appears on.

When you add this query to your report in the reporting suite it will pull through no data, this is expected as the report is not linked to a specific invoice with a customer yet. After I add the report to a template, and preview this on an invoice in my Halo instance I can see the report is being filtered by customer. In Fig 9 you can see how the report looks after the filter is applied, it is showing all the invoices in the report for the customer the invoice is for. See Fig A1 in appendix for the whole report script used here. 

Fig 9. Invoice template with report being filtered by customer

The example SQL queries in this guide can be used as a base for a report filter, but note that they will need to be amended based on your report and the entity.

Appendix

Figure A1 - SQL Script used for report 'Accounts report-Outstanding invoices'