One problem many training companies face is that the dates they raise invoices on are often in a different month to the date that the course starts on. So, whilst a report of revenue by invoice date is useful, it doesn’t help much if you’re trying to allocate that revenue to the correct accounting periods.
Luckily, Salesforce has a reporting feature called matrix reports and it’s the perfect solution to this problem. It doesn’t take long to create the report – less than ten minutes if you’re quick at this sort of thing – but I realise that this is quite a long article. Just in case you don’t get to the end, here’s the report we’re going to create.
The first and most important step when creating any report is to have a design. It’s actually fairly simple for this report. We know we need to know the amount that’s been invoiced, the invoice month and the course start date. As far as the data goes, that’s it, but we need to work out where to get it from.
In Learnsmarter, an invoice can contain multiple registrations and these could be in different months, so we need to start our report by looking at these detail rows. You also need the course start date though and there’s no default Report Type (Salesforce-speak for a reporting query) that references both those objects. You have to build one, but it’s not that hard.
It helps to understand the data structure here (something we can help with at the help desk if you’re ever stuck). I’ve picked out the objects that you’re going to need. By default, a Report Type lets you select objects that are children of a parent object which isn’t enough, because an Invoice Item is the child of an invoice and of a Registration. I’m going to start with the Invoice and the Invoice Item and then use the related fields function to get the remaining data that I need (the course start date) from the Scheduled Course object.
So, now we need to create the Report Type. Start by going to the Salesforce Setup page. Type ‘Report’ in the quick find area and select Report Types from the available options.
Click the ‘New Custom Report Type’ button. If you don’t see this button, then you may not have a license type that supports this (OEM Platform licenses do not, OEM admin and full CRM licenses do), or your user profile may not support this action. The Platform User profile is an example of a user profile that is restricted in this way.
You may see a splash screen and if you do, just click Next. Select Invoices as the primary object. Put something appropriate in the label and description. You don’t get much choice with the Categories. I’d recommend ‘Other’. Set the deployment status, then click ‘Next’. Setting the status to ‘Deployed’ means that other users can use your report type, so if that could be a problem, then leave the report type as ‘In Development’ and deploy it when you’re finished, otherwise it saves a little time to set it as Deployed from the outset.
Once you’ve selected the first object, you can then select up to three cascading child objects. Click the section labelled ‘Click here to relate another object’.
Select Invoice Items from the list of related objects and select ‘Each “A” record must have at least one “B” record’, then click Save.
The Report Type now exists, but it’s not yet ready to use. If you look at the Report Type in the ‘Fields Available for Reports’ section you will see that there are more than 70 fields returned. To have a look at what you have now, click the ‘Preview Layout’ button.
By default, Salesforce includes every field in each of the objects within the Report Type. My recommendation is that you refine the Report Type by removing fields that are not needed. After that, we will add in the related fields that are required.
Close the preview window.
To remove fields from the Report Type, it’s easier to remove all of them and add back in the ones you want. In the Invoices area, click on the field in the top left and then shift and click on the field on the bottom right (not the last row, unless the last field in that row happens to be in the last column). Now let go of the shift key and drag those fields into the Fields pane. You have to hold your mouse over that area for a few seconds until the outside border goes black and then let go. It’s tricky and a pain, but gets easier with practice.
Remove any remaining fields in the Invoice section and then repeat for Invoice Items. When you’ve removed all fields, you then back in the fields that you need. In the Fields pane, there is a drop down to toggle between the objects and then you use the ‘Next Page’ button to scroll through the fields. When you find the fields that you want, drag these back into the layout.
We recommend that you have these fields …
• Invoice Number
• Invoice Date
• Invoice Completed
• Net Cost
This includes a field to filter on and a couple of extra fields which are not strictly required, but mean that we can to create a more detailed view to drill down to if we decide to do that.
Now we need to add fields from related objects that were not selected when we first created the report type. Make sure that the view is ‘Invoice item Fields’ and click the ‘Add fields related via lookup’ link.
This function lets you retrieve fields from related objects that weren’t in your initial selection. The field we want is Course Start Date and this is in the Scheduled Course object, which is linked to the Invoice Items object via the Registration. Click the ‘Registration’ link.
You now see the fields in the Registration object and can see a link to the related Scheduled Course fields. Click this link.
Now just check the fields you require and then click ‘OK’. You can select more than one field. Start Date is the one we need, but you can also select other fields such as Course Name, which may be useful if you want to drill down from the main report.
You now have all of the fields you need, but there is one further improvement we’d recommend and you can see why if you click the ‘Preview Layout’ button. The related fields have long names which show their relationship and these long names will appear on your report unless you edit them now.
Close the preview, click on the name of the field that you want to rename and then click the ‘Edit Properties’ button.
Edit the name and click ‘OK’.
When you’ve finished your changes, click ‘Save’.
You’re now ready to create the report. Go to the Reports tab and click the ‘New Report …” button.
To create a new report, the first thing is to select the Report Type. If you start typing the name of the Report Type you just created, you should see it appear in the search results. Click on the Report Type name and then click ‘Ceate’.
Drag the Net Cost field into the report Preview area.
You want to add this field up and show the totals in your results. Move your mouse over the Net Cost header. Click the drop down arrow that appears and select 'Summarize this Field ...' and check the 'Sum' box and click 'Apply'.
In the Preview area, change the format from Tabular to Matrix.
Drag the Invoice Date field into the Row Grouping area and drag the Start Date into the Column Grouping area.
At this stage, you'll have a lot of rows and a lot of columns, but you can group dates by month in any report. If you hold your mouse over the 'Invoice Date' header, you'll see a drop down arrow. Click this and select to group the dates by month. Repeat for the course date.
You now need to hide the detail rows, so you just see the totals in each result. To do this, click the 'Show' button and uncheck the 'Details' option.
Drag the Invoice Completed field into the filter area. Set the filter condition to Equals True. This will filter out any draft invoices.
Now save and run your report.
And that's it. If you’d like to find out more about Learnsmarter or you’re an existing user and you’d like to implement any of these ideas, then please contact us and we’ll be very happy to help you.