<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><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><p><strong>In this guide we will cover:</strong></p><p><strong>- How to use a custom SQL query to calculate the Licence/Subscription Count on Recurring Invoice Lines </strong></p><p><br></p><p><br></p><p>Custom SQL queries can be used to calculate the quantity of invoice lines based on information from your database. This provides many more options and flexibility to how your invoice line quantities are calculated as they can be calculated using data in your database rather than just based on the standard entities (licence count, user count, asset count etc). A SQL query will need to be written to calculate the line quantity using data in the database, the query must return a column labelled 'QUANTITY' that contains the desired quantity. </p><p><br></p><p>Custom queries can be configured by pressing the button below found within Configuration > Billing > Recurring Invoices (this requires the setting to allow calculated quantities to be enabled).</p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjBlNzM4ZTFkLWY5ZWMtNGRmZC1iODk4LTFjZWQ5MTVlNTM5MyJ9.08rkDrWmclXaKXkU1P8Gs17dTSGj0Kw3xh5unOFYU3A" class="fr-fic fr-fil fr-dib" width="895" style="width: 897px; height: 351.641px;" height="352"></p><p><strong><span style="font-size: 10pt;">Fig 1. 'Allow recurring invoice line quantities to be calculated using Licence, User and Asset counts' setting</span></strong></p><p><br></p><p>The query must return a single cell with a column named [QUANTITY]. A test button is provided so you can input an example recurring invoice to ensure the calculation works as intended before generating a recurring invoice.</p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImVjNDRlNWNkLWRmZWUtNDU2MC04ZWQ4LTMwYWVmNDNmM2E4OSJ9.UMHIwKCa01wK2dvo0zteuuXUd_McdPl9AZ_DUZwRx7M" class="fr-fic fr-fil fr-dib" width="898" style="width: 900px; height: 388.684px;" height="389"></p><p><strong><span style="font-size: 10pt;">Fig 2. Custom SQL quantity example</span></strong></p><p><br></p><p>These custom queries can then be selected on recurring invoice lines using the 'Quantity' dropdown.</p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImRlMzMzNTIzLTllZDQtNDg1My04ZDU1LTI5NjQyMWM4NDQxOSJ9.Cnk1tMaThCP7ixXBZ_i9me6mjEHDydvcisx0P5z-brE" class="fr-fic fr-fil fr-dib" width="487" style="width: 489px; height: 402.53px;" height="403"></p><p><strong><span style="font-size: 10pt;">Fig 3. Set invoice line to be calculated based on custom SQL quantity </span></strong></p><p><br></p><p>Adding for example a MS 365 Business Licence product line to a recurring invoice, and then editing the line and then selecting the quantity option "Custom SQL Quantity"</p><p><br></p><p><strong><em>Note: These will not be calculated in recurring invoice list views. Pro-rata will not pull through for the quantities</em></strong></p><p><br></p><p><strong>Calculating quantities from custom table data</strong></p><p>If you have a custom table in your instance that already contains/calculates the quantities you would like to use for your invoice lines you can use $-INVOICELINEID to match each invoice line to a row in the the custom table. This allows you to use the same SQL query to calculate multiple line quantities, rather than having to create multiple queries that only return a single cell of data. You can have a column containing all the line quantities, then determine which row the invoice line uses by matching on the invoice line ID. This requires the invoice line ID to be stored in the custom table.<em> </em></p><p><br></p><p><strong>Worked Example - Calculating Quantity of Software Installed on a Device per Customer</strong></p><p><br></p><p>For this specific scenario we need the ability to calculate the quantity of software installed on all devices under a specific customer. </p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjYwNDdhOTM0LTZjYjEtNDM0Yi05Y2M4LTJmNzg4Y2RhZWYzYSJ9.ObgXwJ6COHOmxnDcwl1ymwo25jHVIhrhTC7YdVEeuic" class="fr-fic fr-fil fr-dib" width="1496" style="width: 1498px; height: 334.824px;" height="335"></p><p><br></p><p>This would likely be a case where you would like to charge your clients automatically for particular pieces of software installed on the devices you provide. In my example, we have Outlook and Netflix installed on one laptop and just Outlook installed on another users laptop, under the same client:</p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6Ijk1NmVhNjk3LTY2OGMtNGNiYy1hZTU0LTc3MzE2YmVlMDE4NCJ9.TB7ZLbNhvfO9RAkKNtQ9UvKPC6n3aTKtTLQSQ2_LTn8" class="fr-fic fr-fil fr-dib" width="1471" style="width: 1473px; height: 441.804px;" height="442"></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImNlNGZmMGFmLThkNDUtNGUwZS05ZDdjLWNhMThkMGNjMDJlMCJ9.4WQYDeNkaOrItqkFss9jimP7F5-l32GlknpMCkBwpf8" class="fr-fic fr-fil fr-dib" width="1521" height="392"></p><p><br></p><p>In this example, I would like to bill my client for the software I have provided them automatically through a recurring invoice. To do this I can utilise Custom SQL Quantities. The first step would be to build out my SQL Quantity calculations for each software application I would like to bill for against a customer's assets. The query would look as follows:</p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjhlMWU2M2RkLTNhMGEtNGVmZi05MzI3LTM4MjQ3NTc2MmUyNSJ9.jERAeJ7I3T9F5HOysBysI1vNJQi8vjYdcZAEpPfNbgk" class="fr-fic fr-fil fr-dib" width="815" height="662"></p><p><br></p><p>The only key difference is to change the where clause to match the piece of software I would like to bill for. In this case WHERE dadesc like 'Netflix':</p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjQ3ZjcyYWU4LWMyYTctNGVhYS05YTQ0LTNjMmFlNjY0ZmIwZSJ9.Qm8hytDHDwJwGkR4_S1kYX-GJctJ1wraPbjlrul34pc" class="fr-fic fr-fil fr-dib" width="616" height="283"></p><p><br></p><p>Once these have been configured I can now update my line items to calculate the quantity of these subscriptions for my client as follows:</p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6Ijc0ODk0MDkxLTI3YWUtNDg3MS1iMjNhLTVlZDQyNmU0OGY0YiJ9.-nBddcGNrIoKKfaw53YA-RmibxcEhr0Jh9QYV6lrgg4" class="fr-fic fr-fil fr-dib" width="1253" height="156"></p><p><br></p><p>Now my recurring invoice can automatically calculate the quantity of software licences per client in my recurring invoice. A quantity of two for Outlook and a quantity of one for Netflix:</p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImEzMGI2Y2NlLTYyNmMtNDJlOS1hYWZkLTEwYzJhYWExODNkNCJ9.To3OBvNjjsavz6hEAoEznrST9cBLFRag21xDzxvve5M" class="fr-fic fr-fil fr-dib" width="1207" height="366"></p><p><br></p><p>The SQL query can be expanded to calculate the quantity per asset type, asset group, site etc</p>
