In the world of data analysis, there are moments when you find yourself in need of a fresh perspective, a LiveReport that can shed light on the absence of something crucial. In this blog, we’re going to create an Autotask New LiveReport that lists Recurring Service Contracts that are missing Services. To do this, we need to focus on the Contract, not the Services, and we’ll do that using SUM(), Suppress Rows, and Conditional Formatting.
To start off, let’s create a new report named “Recurring Contracts without Services.” Be sure to use a Standard Report, not an Express Report.
For the Categories, choose the following and in this order:
- Recurring Service Contracts / Contract
- Recurring Service Contracts / *Contract Service/Bundle
- Recurring Service Contracts / Service
For the Sort:
- Account
- Contract ID
And, finally, for filters, we want to ensure we only look at Active contracts:
- Active=Yes
Now, let’s go to the design screen and put the following values in there. Note, other than the formulas, you can drag&drop the fields. Be sure to note that we have a Footer: Contract! That’s going to be important later.
Now, run the report. You should get a very ugly report that has some numbers in it, like in the example below:
Notice how we have a number in each Detail row (Service) and a sum of those numbers in the Contract: Footer. Also, did you catch how some of the Footer: Contract rows have a 0? That’s because there were no Services for that contract, so the SUM() just added up to 0.
That 0 is our trigger. So you can see how this becomes important, let’s hide the Detail row. Set Row 4 to ‘Suppress Rows’ and then remove the blank line in the Footer:
Run the report again. You’ll get an easier-to-read version:
Any row where there is a 0 is a Contract you want to review for no Services. Next, we can eliminate the non-0 rows entirely with the Cell Formatting button. Click it and go to Conditional and let’s set any row where [D5] > 0 to Suppress Row:
Run the report yet again and you will only see accounts/recurring service contracts that need your attention:
