If you're reading this you're probably aware that the support/documentation provided by ConnectWise for more difficult tasks in Report Writer is a bit... lacking. I ran into a few issues on Saturday and while i figured them all out the same day, i did a CW chat and submitted a ticket and have yet to hear back (4 full business days later). Basically, if you want to use Repeaters and/or Subtotals, there are very specific constraints that you need to keep in mind. This post is not for someone just getting into report writer, you need to already know how to build queries, use joins, and more.
Repeaters
These are a great solution to not having to use a sub-report. That said, they're not very intuitive and are in fact pretty basic in nature. The gist of it is that once you start the repeater, the first column you reference and all subsequent ones (unless you hit a nested a repeater) are repeated until the initial column changes. The documentation flip flops between using [repeater], <!--[repeater]-->, and <repeater>. The latter is never correct. The first two can be used interchangeably, but if you're wrapping this around HTML content then the middle option is the correct one.
As an example, say you are making a report that lists a client's agreements with their additions. The data in table form may look like this:
| Agreement Name | Anniversary | Addition Name | Addition Qty | Addition Price | Extended Price |
|---|
| Managed Services | 1/1/2019 | Antivirus | 30 | $5.00 | $150.00 |
| Managed Services | 1/1/2019 | Agent | 30 | $1.00 | $30.00 |
| Managed Services | 1/1/2019 | Spam Filtering | 35 | $3.00 | $105.00 |
| Telecom Support | 2/1/2020 | Handset Fee | 20 | $10.00 | $200.00 |
| Telecom Support | 2/1/2020 | PBX Maintenance | 1 | $100.00 | $100.00 |
As you can see, the agreement name is duplicated for each time you have an addition on it, because this is how SQL works. So in Report Writer, on the Fields tab, you would click Design Form and then (if you're sane) edit the HTML directly. The code to put this together would look something like this:
[Company_Name]
<!--[repeater]-->
<h3>[Agr_Name] - [Agr_Anniversary]</h3>
<table>
<tr><th>Item</th><th>Quantity</th><th>Cost</th><th>Ext Cost</th></tr>
<!--[repeater]-->
<tr>
<td>[Line_Desc]</td>
<td>[Qty]</td>
<td>[Cost]</td>
<td>[Ext]</td>
</tr>
<!--[/repeater]-->
</table>
<hr />
<!--[/repeater]-->
Note that there are nested repeaters. The first level is for the Agreement Name and Amount, the second level is for the items associated with each one. The resulting output looks something like this:
Company Name
Managed Services - 1/1/2019
| Item | Quantity | Cost | Ext Cost |
|---|
| Antivirus | 30 | $5.00 | $150.00 |
| Agent | 30 | $1.00 | $30.00 |
| Spam Filtering | 35 | $3.00 | $105.00 |
Telecom - 2/1/2020
| Item | Quantity | Cost | Ext Cost |
|---|
| Handset Fee | 20 | $10.00 | $200.00 |
| PBX Maintenance | 1 | $100.00 | $100.00 |
Subtotals
Now let's say that you want to add up all of the addition amounts on the agreement and show that total. Since you still want the itemized list, you need to use the Subtotal Decorator. The CW Manage documentation flip flops on whether this is @Subtotal or #Subtotal, don't ask me why. The correct usage is @Subtotal. There are however a few constraints to consider:
- Subtotal only makes sense in a repeater. If your data looks how you want it and you're not using a repeater, then this should be done in the query, not in the report designer.
- You must check the VG (visual group) box on every column not part of the repeater that will be subtotaled. This will force them to be sorted as well, but that's not a big deal.
- You must check the Add Subtotals box at the bottom of the Fields tab.
- CW will tell you that you can only use Subtotal on one field. This is incorrect, you can subtotal as many fields in a repeater group as you want.
- CW neglects to tell you that the column you are subtotaling cannot have spaces OR underscores in it.
Using the repeater example above, here is how you would add a subtotal of the overall ext costs of the additions:
[Company_Name]
<!--[repeater]-->
<h3>[Agr_Name]</h3>
<table>
<tr><th>Item</th><th>Quantity</th><th>Cost</th><th>Ext Cost</th></tr>
<!--[repeater]-->
<tr>
<td>[Line_Desc]</td>
<td>[Qty]</td>
<td>[Cost]</td>
<td>[Ext]</td>
</tr>
<!--[/repeater]-->
<tr><td colspan="3">Total:</td><td>[Ext@Subtotal]</td></tr>
</table>
<hr />
<!--[/repeater]-->
It's nice to note, the Subtotal can go above or below the repeater group. If you want to subtotal more than one column, just add that column with the @Subtotal decorator wherever you need.
