Custom Report Layouts give me the fear. When I was freelance one of my clients asked if I could slightly amend their Sales Invoice print. Figuring that I could google how to do it, I agreed. Long story short: I royally screwed it up and somehow managed to mess up the invoice print for all the other companies on their tenant (and there were A LOT)
The silver lining to this story is that, when they called their Partner to sort out my mess, the Partner stumbled round like a bull in a china shop too before getting it right!
Anyway, one of my current clients needed a custom Reminder document so I thought I’d have another bash. I’ll be honest, it’s not an intuitive process. I found a few videos that were helpful but none that addressed the issue I wanted to solve, namely how to include a list of the overdue invoices, whether there is one or twenty.
So, here’s what I did, including my accidental solution!
- Activate the Developer tab in Word
- Download the layout in Word format
- Take whatever template your client has given you (including logos, layouts fonts etc)
- Clear the downloaded template and paste in your client’s template instead.
- Click on the Developer Tab in Word and then on the XML Mapping Icon
6. This will open up the XML Mapping pane on the right of the screen.
7. Click on the dropdown. Because you are using the downloaded word template (albeit you’ve overwritten it) the bottom schema on the list will be the schema that relates to the report. Here, I’m using the Reminders report. If you look in Custom Report Layouts you’ll see that this is report no. 117.
8. The schema offers a nightmarish list of fields that are available to you to add to the report. I found it useful to download the original again and compare the report it outputs against the schema properties
9. You can now start to see the values that are output versus the weird looking XML mapping codes
10. To start to populate your document you are best advised to use tables to position your data. As you can see below, I’ve used a table for the customer address and, lower down the document I’ve used another for the headers and lines of the reminder report. It’s actually quite a good idea to include borders on your tables until you’ve postioned everything. You can always remove them later.
11. Positioning the various XML Controls on the document is relatively easy.
- Locate your cursor in the cell of the table you want to populate
- Then right click the control
- Choose from the dropdown (I chose Rich Text because my client wanted to use a specific font)
- This will add the control at the point in the document where your cursor was.
12. The biggest headache I encountered was in trying to show multiple lines on the report. It’s actually easy once you know how but this is one of those things that the internet seems to be curious silent on. I stumbled upon this by accident. As you can see from the schema it’s broken down into indented layers. If you right click on one of the outdented “Headers” the Insert Content Control option gives you a different choice from if you right click on a “Line”. It says Repeating (rather than Rich Text, Plain Text etc). If you choose this it will give you a box into which you can fit other XML Controls such as document lines.
13. In my example I’ve got a reminder with three invoices all of which are overdue. As you can see, I’m only using a single line of XML controls but, because they are nested inside a Repeating control, they all come through. Without the Repeating control the report would only return the first line on the reminder.
So there you have it. To use the report go to the Report Layout Selection, chose the report and change the Selected Layout field to Custom Layout.
There’s an ellipsis (…) in the next field, Custom Layout Description, Click on that and pick the layout you’ve designed.
Play around with this on other reports and see what you can come up with.