Add Summary Matrix to HTML in ConnectWise Manage Report Writer

A while back, I had a client that needed a summary matrix of time spent by agreement type on their overage invoices in a matrix format. This is possible with a custom invoice in ConnectWise Manage. In this post, I’ll show you how to create the matrix formatted HTML subreport in Report Writer that you can then add to your custom invoice or any other Report Writer report. If you haven’t created a subreport in Report Writer before, here’s a ConnectWise University article you can refer to (login required).

Field Setup

Something like the Data Sources setup below is required to pull in agreement types. To simply group by agreement name, you can stick with the v_rpt_invoicetime view and use the Agreement field.

On the Fields tab, add all the fields you’d like in your summary table and Check the Add Subtotals box.

For the WorkPerformed field, my client had logic for how to display according to whether it was a block agreement or other type. For this example, I just have ISNULL([agr_type_desc],’Other’). This tells Report Writer to show “Other” if there is no agreement type for the work. Here is the logic to enter in the Advanced Field Properties for the other fields in this example.

Hourly Rate (Subtotal Expression)

SUM([billable amt]) / NULLIF(SUM([billable hours]),0)

Total Hours (Expression)

SUM([v_rpt_InvoiceTime].[NonBillable_Hrs])+SUM([v_rpt_InvoiceTime].[Billable_hrs])

Billable Amt (Subtotal Expression)

CASE WHEN sum([billable amt]) = -.01 THEN 0 ELSE sum([billable amt]) END

Billable Amt (Expression)

SUM([Billable_Amt])-SUM([Agramtcovered])

Billable Hours (Expression)

SUM([Billable_Hrs])-SUM([Agrhrscovered])

HTML Table Design

The key here is to set everything up as subtotal calculations and to end with an extra set of repeaters with nothing between them so the report only attempts to show those totals. Here’s the entire block of code I have in the Design Form HTML on this example. I didn’t bother cleanup up some of the extra stuff Report Writer added.

<table class="mce-item-table" style="margin: 0px auto; font-size: 100%; font-family: sans-serif; padding: 0px; height: 58px;"
width="750" cellspacing="0" cellpadding="0" align="center" data-mce-selected="1"
data-mce-style="margin: 0px auto; font-size: 100%; font-family: sans-serif; padding: 0px; height: 58px;">
    <colgroup>
        <col width="22%">
            <col width="12%">
                <col width="12%">
                    <col width="12%">
                        <col width="12%">
                            <col width="12%">
                                <col width="18%">
    </colgroup>
    <tbody>
        <tr style="background-color: white" data-mce-style="background-color: white;">
            <th align="right">
                <br>
            </th>
            <th align="right">
                Total Hours
            </th>
            <th align="right">
                Hours Included
            </th>
            <th align="right">
                Hours Not Charged
            </th>
            <th align="right">
                Billable Hours
            </th>
            <th align="right">
                Rate
            </th>
            <th style="text-align: right; margin: 0; padding: 0" width="18%" data-mce-style="text-align: right; margin: 0; padding: 0;"
            align="right">
                Price
            </th>
        </tr>
        <!--[repeater]-->
        <tr>
            <td style="text-align: left; margin: 0px; padding-top: 5px" data-mce-style="text-align: left; margin: 0px; padding-top: 5px;">
                [WorkPerformed]:
            </td>
            <td style="text-align: right; margin: 0px; padding: 0px; vertical-align: top"
            data-mce-style="text-align: right; margin: 0px; padding: 0px; vertical-align: top;">
                [Total Hours@subtotal]
            </td>
            <td style="text-align: right; margin: 0px; padding: 0px;  vertical-align: top"
            data-mce-style="text-align: right; margin: 0px; padding: 0px;  vertical-align: top;">
                [Covered Hours@subtotal]
            </td>
            <td style="text-align: right; margin: 0px; padding: 0px; vertical-align: top"
            data-mce-style="text-align: right; margin: 0px; padding: 0px; vertical-align: top;">
                [Non-Billable Hours@subtotal]
            </td>
            <td style="text-align: right; margin: 0px; padding: 0px; vertical-align: top"
            data-mce-style="text-align: right; margin: 0px; padding: 0px; vertical-align: top;">
                [Billable Hours@subtotal]
            </td>
            <td style="text-align: right; margin: 0px; padding: 0px; vertical-align: top"
            data-mce-style="text-align: right; margin: 0px; padding: 0px; vertical-align: top;">
                [Hourly Rate@subtotal]
            </td>
            <td style="text-align: right; margin: 0px; padding: 0px; vertical-align: top"
            data-mce-style="text-align: right; margin: 0px; padding: 0px; vertical-align: top;">
                [Billable Amt@subtotal]
            </td>
        </tr>
    </tbody>
</table>
<!--[repeater]-->
<!--[/repeater]-->
<!--[/repeater]-->

That’s it! If you are using this as a subreport, don’t forget to set your Drill-down key in the Misc tab (probably billing_log_recid if you’re doing an invoice subreport).

This design can of course be applied to other summary reports in ConnecWise Manage Report Writer. Do you have a great report you’ve built? Feel free to reply below in the comments!

Want to do more?

Do you have any other Report Writer formatting issues you’ve been struggling with? Contact us for one-on-one help or connect with us on the ConnectWise subreddit or the ConnectWise University Forums.