How to Perform Additional Calculations on your Report Writer Expression Results
One big limitation of working with a ConnectWise cloud installation is that I can’t create my own data views to do any calculations on my fields before running the report in Report Writer. This makes it difficult to do multiple levels of calculations since your custom field Expressions can’t refer to each other (all Expressions are calculated at once when the report is run).
I recently needed a way to work around this for a commission report for our client, Twin Technology. I had a complicated CASE statement in the Expression box to determine the margin depending on the type of Agreement, product, or whether it was a time entry. I then needed to run this against the commission percentage, which was another CASE statement to allow for different commission rates depending on the total margin.
The trick to getting Report Writer to allow me to calculate was to do it as a Subtotal Expression. Subtotal Expressions refer to your custom report’s fields, rather than fields in the database tables.
Here is how the setup looks in Report Writer:
Instead of putting your Expression in the regular Expression field, add a new field to your report (it doesn’t matter which field) and add a Subtotal Expression field by selecting “(Expression)” in the Subtotal Function drop-down.
In here, I put my expression as usual. Now, if you don’t actually want the data from the field you selected to show up, type “NULL” in the Expression box or, if you want to get fancy, copy/paste N’↓’ (this way report users will see an arrow directing them to the subtotal instead of wondering why the field is blank).
Now, for my particular report, this wasn’t pretty enough. I wanted to call out the commission total at the top of the report, next to the person’s name. To do that, I added an HTML layer to the report and created my own custom table (this is beyond the scope of this article but you can find help with this at ConnectWise University or by looking at the code for a built-in Report Writer report such as the invoice template).
Additional information
Subtotal Expressions on izenda documentation site
Report Writer documentation at CW University