Create an Invoice with Separated Shipping Costs
We were recently tasked with creating a custom invoice for Datapath, an MSP from Modesto, CA, based on their mock-up below. As I began to create it in Report Writer, I noticed a problem. They wanted a separate shipping total, which is not an available ConnectWise Manage invoice field.
Like many partners, Datapath’s shipping charges flow though as a product from a Quotewerks quote. In this case, all invoices have the specific Product ID of “QWShipping” for shipping costs. In order to create this, I made two additional subreports with just a single amount–one for product total without shipping and one for only the shipping total. This process will work for both cloud partners and on-prem installations.
Main Report
The report for the main invoice is essentially the same as the CW-provided invoice, other than Datapath’s custom formatting and and the two additional subreports, Total_noship and Total_ship. I diverged from the original mock-up slightly by putting the shipping above the subtotal. This avoids having to create even more subreports.
More information about linking to subreports can be found in CW documentation (see additional resources below).
Products subreport
The Product_Detail subreport needs a small modification. In order to exclude shipping costs in the line items and total, we need to go to the Filters tab and filter out anything matching the Product ID used for shipping.
Total product amount subreport
While still on the Products subreport, click Save As to create a copy of the report with the data source, drill-down key, and filter to use for the product total without shipping. In this copy, you only need the Extended_Amt and Currency_Symbol fields and you will need to Sum the Extended_Amt field. Then, go into the HTML under Design Form and remove everything and enter only “[Currency_Symbol][Extended Amt]“. It’s important to do this in the HTML area and immediately save the report. If you ever go in to modify the report later, check the HTML tab and make sure CW didn’t automatically add any code, such and <p>/</p> around your text.
Total shipping amount subreport
After saving your no-shipping total subreport, do another Save As and create a copy for the separated shipping cost total. The only thing we need to change here is the filter. Instead of excluding QWShipping, we want to include only QWShipping.
Conclusion
Now that you’ve created your additional subreports, you can now open up your primary report and link these subreports to your invoice. Here is what the bottom section of their final invoice looks like when printed to PDF.
Additional Resources
Official CW documentation on methods to account for shipping charges.
Video: Creating subreports (drill-through)
Want to do more?
Congratulations, you now have an invoice with separated shipping costs. 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.