Show Child Agreement Details on Invoices

At NexNow, we’ll often get requests for custom invoice templates that display things in a way that’s a bit outside the box. One client did a lot of agreement bundling and some child agreements needed to have their titles and products show on the invoice. Since ConnectWise doesn’t allow us to specifically call out child agreements when creating invoice templates, we needed to create a custom SQL View.

Requirements

• Self-hosted ConnectWise with full database access
• Creation of subreports depending on which display variations you want for your agreements
• Knowledge of Report Writer to build a report/subreports using the custom View as a Data Source

Create custom SQL View

To make this all possible, log into your SQL server and create this new view in your CW database. We called ours v_NN_Agreement_Invoice and defined it as follows:

The key things this view accomplishes:

• Since child agreements don’t have the billing_log_recid column, which is necessary for invoice display, the third JOIN statement uses a COALESCE to give the child the same billing_log_recid as the parent agreement.
• The second JOIN statement pulls in custom fields for agreements. In our client’s implementation, we used this to indicate with a checkbox which agreements should show detail, which should just show the title, and which should show a title even if there are no associated products (e.g. if we want the parent agreement to display as a title and then have the child agreements titles and products below that).

Creating the invoice templates in Report Writer

We based our primary template on the default agreement template included in CW, referencing two subreports by billing_log_recid:

Filtering by checkbox custom fields we created for agreements, we created two different subreports to display agreement details a specific way depending on which boxes were checked. This made it possible to print one invoice with different agreement detail display formats per agreement.

Here’s an example of the finished product. Since everyone’s needs will be different, I chose not to go into detail here on the steps to create the invoice but if there’s interest perhaps I’ll do detailed writeup in another post.

Want to do more?

Do you want to do more of your own ConnectWise reporting in Report Writer, Power BI, or another reporting tool? Contact usfor one-on-one help or connect with us on the ConnectWise subreddit or the ConnectWise University Forums.