Guides - Pulling Report Data into a PDF template

Pulling Report Data into a PDF template

This guide has multiple versions available:

<div><div><div><div><div><div><div><div><div><div><div><style>p { margin: 0; }span.fr-emoticon.fr-emoticon-img { background-repeat: no-repeat !important; font-size: inherit; height: 1em; width: 1em; min-height: 20px; min-width: 20px; display: inline-block; margin: -0.1em 0.1em 0.1em; line-height: 1; vertical-align: middle; } span.fr-emoticon { font-weight: normal; font-family: "Apple Color Emoji", "Segoe UI Emoji", "NotoColorEmoji", "Segoe UI Symbol", "Android Emoji", "EmojiSymbols"; display: inline; line-height: 0; } blockquote { border-left: solid 2px #5e35b1; color: #5e35b1; margin-left:0; padding-left:5px;}blockquote blockquote{ border-color: #00bcd4; color: #00bcd4;}blockquote blockquote blockquote{ border-color: #43a047; color: #43a047;} table.grid{ border-collapse: collapse;} table.grid td, table.grid th { border: 1px solid #ddd;} .fr-fic.fr-dib{ display: block; margin: 5px auto;}.fr-fic.fr-dib.fr-fir{ text-align: right; margin: 5px 0 5px auto;}.fr-fic.fr-dib.fr-fil{ text-align: left; margin: 5px auto 5px 0;}.fr-fic.fr-dii{ float: none; margin: 5px auto;}.fr-fic.fr-dii.fr-fil{ float: left; margin: 5px auto;}.fr-fic.fr-dii.fr-fir{ float: right; margin: 5px auto;}img.fr-dib.fr-fir { margin-right: 0; text-align: right;}img.fr-dib.fr-fil { margin-left: 0; text-align: left;}img.fr-dib { margin: 5px auto; display: block; float: none;}img.fr-bordered { box-sizing: content-box; border: solid 5px #CCC;}img.fr-shadow { box-shadow: 10px 10px 5px 0px #cccccc;}img.fr-rounded { border-radius: 10px; -moz-border-radius: 10px; -webkit-border-radius: 10px; -moz-background-clip: padding; -webkit-background-clip: padding-box; background-clip: padding-box;}</style><div><div>In this guide 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: <ul><li>Tickets</li><li>Quotations</li><li>Sales Orders</li><li>Purchase Orders</li><li>Invoices </li><li>Agreements </li><li>Assets</li></ul> Adding a Report to a PDF TemplateFirst 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.  <img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImMzNGU2MDA0LWJjYjAtNGI5MS04MjliLWRiMDQ1ZGVjZGZlNiJ9.r1l-kWG3bCpieYn9G5gQZqXyPv1ZF8ADPTHgT6K4q5o" class="fr-fic fr-fil fr-dib" style="width: 510px;">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.  <img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImFjYTg4MWZiLTkyMWQtNDI2YS1hNzFkLTQwNjEwOGIwMTEzZSJ9.f6OPzSUOXhrBTQE-_TihAxPKRmIwczMcNXC5m1PwZyI" class="fr-fic fr-fil fr-dib" style="width: 534px;">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.  <img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjA0MDJjODBkLWU1MjYtNDM0NS04M2M0LWFmYjA4YWNlZTI4ZSJ9.WFb5fnDSl2MKaZdh6K3VgJ16i7fhTwV6DkJBn-PNS68" class="fr-fic fr-fil fr-dib" style="width: 931px;">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. <img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImE3YWM2NzYzLTRlZGYtNDBhOS05ZGUzLTNhNDc3NzAyOGJkNCJ9.81We9fhqUr7pXMwA0i_3c4ApoTnznyxfYX94xT_MHPg" class="fr-fic fr-fil fr-dib" style="width: 914px;">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. <img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImJjNjQxNGQ4LTI5NjktNDMyYi05MTQ1LWUzMWE3MTg3Y2Q0MCJ9.pfY-R14KRfFtz2F4DeO3RjJtYMchZgQpqNxKgq3mf_k" class="fr-fic fr-fil fr-dib" style="width: 687px;">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 entityIf 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 1I 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: <div style="color: #000000;background-color: #fffffe;font-family: Consolas, 'Courier New', monospace;font-weight: normal;font-size: 14px;line-height: 19px;white-space: pre;" id="isPasted">where QHid= $QUOTEID </div> 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.  <img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjA2ZGM4YTc5LTg4MzYtNDg5ZC1iYWZjLWU3Mjc2MzlkMzkxYiJ9.YtKO9qFHAp6hZ-bwmV7KfoVyXqAx0sFNkMECXwxGV-U" class="fr-fic fr-fil fr-dib" style="width: 835px;">Fig 7. Variable that can be used to filter the report on the template  Example 2I 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. <img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjZlZGU5MjA2LWY2MjctNDNlYS04Mjc2LTk3MmZkYWNjYmY4NyJ9.kkNYjvAaIPgfbQgUdNnkZ9sHBL52nua-b_sCtYHDhw8" class="fr-fic fr-fil fr-dib" style="width: 839px;">Fig 8. Outstanding invoices report for all customers To do this I will need to add the following query to my script: <div style="color: #000000;background-color: #fffffe;font-family: Consolas, 'Courier New', monospace;font-weight: normal;font-size: 14px;line-height: 19px;white-space: pre;" id="isPasted">join site on ihsitenumber = Ssitenum</div> 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.  <img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6Ijg2MDQ2MGUyLTVjYWItNDRhNy1hYmZiLTQzMTI1YjAxZjgwYSJ9.6NMJVHEkB8VJl_iYV4i6B0NThFSaat5YTz_24a9CToI" class="fr-fic fr-fil fr-dib" style="width: 929px;"><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjcyZmM1MDEzLTY5ZDItNDZlMC04NGIzLTQ4ZjU3ZGNmZmJjZiJ9.sICDcJEkRhe-RA4T2w6opwKbOtgNaG4wZOFyMymnnLs" class="fr-fic fr-fil fr-dib" style="width: 929px;"> 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. AppendixFigure A1 - SQL Script used for report 'Accounts report-Outstanding invoices' <div style="color: #000000;background-color: #fffffe;font-family: Consolas, 'Courier New', monospace;font-weight: normal;font-size: 14px;line-height: 19px;white-space: pre;" id="isPasted">select    (select IHname from invoiceheader where ihid=idihid)'Client',  (select ihid from invoiceheader where ihid=idihid  )'InvoiceNumber',  (select IHInvoice_date from invoiceheader where ihid=idihid)InvoiceDate,   IDItem_ShortDescription as 'Item' , idqty_order as 'Quantity' ,  idunit_price as 'UnitPrice' ,  round(IDqty_order*idunit_price,2) as 'LineTotal'  from invoicedetail    join invoiceheader on ihid=idihid join site on ihsitenumber = Ssitenum WHERE Ssitenum = (SELECT usite FROM users WHERE uid = (SELECT ihuid FROM invoiceheader WHERE ihid = $InvoiceID))</div> </div></div></div></div></div></div></div></div></div></div></div></div></div>

Feedback

Guide name here

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.

One unified platform. Every team. Every workflow.

Tailored solutions for every industry.

Powerful capabilities, endless possibilities.

Browse Guides

Platform

Use Cases

Resources

Halo

Subscribe to Halo for latest news

Follow