Sage Intacct Custom Report Wizard

Have you ever generated a report in Sage Intacct only to be surprised to find a significant amount of missing data?

In the Smart Rules post, we discussed how to set up Smart Rules to preserve data integrity in reports. I discovered the importance of Smart Rules when I found many Sales Reps missing from a custom Customer List by Sales Representative report.

While Smart Rules have since emerged as the proactive solution, the report was the initial step in identifying the problem. It’s also worth noting, if Smart Rules are not a viable solution for your company, building a custom report to regularly monitor can be a good alternative.

Today, we’ll walk through how to create custom transactional reports using the Custom Report Wizard.

There are three core ways to export data from Sage Intacct via the User Interface:

• List Views — List views are the most basic way to export data from Sage Intacct.
• Custom Reports — Custom reports offer more advanced features than list views, allowing you to include related objects. Typically transactional, these reports can combine Sales Invoice Line Items, Sales Invoice Headers, Customer, and Item fields using the custom report wizard.
• Financial Reports — Financial reports use your account groups, chart of accounts, and dimension structures.

The first step in building any report is deciding what type of report will extract the information you’re looking for. Today, we’ll create a custom report to identify customers missing sales representatives or start dates. This process can be applied to any custom report. Once you understand this example, you'll be able to generate your own reports, such as a detailed Sales Invoice or AP Bill reports.

Finding the Wizard

Customization Services (or Platform Services)> Custom Reports>Add

Step 1: Select data source

At this step, we’ll determine the source of the data. For this report, we’ll choose “Customer.” Always choose the most detailed level as your source. For instance, if your report includes fields from both sales invoice headers and line items, selecting sales invoice line items (order entry transaction detail) will also provide the option to include related header fields.

Not sure which object to select? Review the Custom Report Object Glossary which provides a description of what information is contained in each data source.

Step 2: Add columns

Columns are the fields you’d like to include in your report. Some helpful tips for this step:

• With so many fields available, sometimes it’s difficult to find the field you’re looking for. Typing “ctrl+F” will bring up the in-browser search box, allowing you to type the field name and more easily find the field you’re looking for.
• A field with a green up arrow indicates a drill-down field. Including it in your report will allow for drilling down to the record or transaction.
• A field with a red circle indicates a custom field. When reporting on objects with multiple potential transaction definitions (Order Entry, Purchasing, Inventory) the custom field must be available on all the transaction types included in the report to be available as a column.

In this example, I chose to include the following information on my report: Customer ID, Customer Name, Customer Rep, Start Date, and from the related Customer Rep>Contact object, the Contact Name to display the customer rep’s name.

Step 3: Add calculated columns

Calculated columns can add, subtract, divide, or multiply other columns. If your report requires more advanced functions, the Interactive Customer Report Writer (ICRW), Interactive Visual Explorer (IVE), PowerBI, or external Excel plugins may be options to consider.

As an aside, did you know CLA has a team that can help to build PowerBI reports and dashboards? If you’re finding yourself spending too much time trying to design dashboards or reports in PowerBI, need some ideas, or don’t know where to start we can help.

Step 4: Select column sequence

This step outlines column sequencing. Dragging and dropping the Customer ID field to the top of the list will position that column on the far left of your report, followed by their Name, Customer Rep, and Start Date.

In the text box, you can also change the name of your columns, which is helpful if you are building a custom report to import data to another system requiring specific column headers.

Step 5: Add more totals

Adding more totals allows you to summarize or count within group subtotals and report totals. For instance, to find the count of customers with missing information in this report, select the “Count” checkbox next to either “Customer ID” or “Customer Name.”

Step 6: Sort columns

With column sorting, you can select how results are sorted. In my example, I chose to sort by Customer ID “Ascending” meaning the report will sort by Customer ID sequentially.

Step 7: Filter report

Here you can filter the data displayed in the report. For example, when I run this report, I don’t necessarily need to see all customers we have in the database every time. I set my filters such that any Customer with a blank (“is empty”), Start Date or Customer Rep will appear in the report.

Step 8: Advanced filter

Step 8 goes along with step 7 in that you decide whether you would like the previous filters defined to be OR, AND, or some combination. I chose OR because I need this report to display any customer with a missing start date OR missing sales rep. If you needed to see only customers with both missing fields, you would choose AND.

Step 9: Enable grouping

Enabling grouping is helpful when creating a report you’d like to group by certain fields. For example, if you were creating a custom aging report, you would want to group all invoices for a particular customer together and then show details as well as subtotal for that customer.

Helpful tip: If you enable grouping, Sage Intacct defaults to not show details which most commonly is not the preferred default. So, go to the step titled “Set Report and Date Title” and switch the radio button to details.

If you choose to enable grouping, you’ll have two additional steps added to the report writer to populate:

• Group data — this allows you to determine which fields to group the data by.
• Define chart output — when grouping, you can also create custom graphs that can be added to dashboards or run on demand.

Step 10: Set report and date title

This step will sometimes have additional information as mentioned in the step above, like enabling the default export to show details when grouping. At this point, you can run your report and preview what it will look like in your desired format without saving.

Step 11: Include additional prompts

This allows you to choose which fields you would like to appear when a user runs this report. For example, I chose Start Date. If a user runs this report and leaves this field blank, all customers with one of the two fields missing will display. If the user chooses 01/01/2019 – 01/31/2019, the report will generate a list of customers with missing Sales Reps or Product Specialists that had a start date in that range.

Step 12: Label and organize prompts

Here you can change the label of the prompt selected in step 11 as it will display to the user running the report. And just as importantly, this step allows you to enable any fields to be selected when memorized for recurring or delivered reports, which by default is not selected.

Step 13: Save and add to a menu

Lastly, title your report and give it a description. Selecting “Add to menu” and the application dropdown will add the custom report to the application area so it’s easily accessible there.

What’s next?

If you’ve made it this far, you’re likely well on your way to becoming a custom report writing wizard, but if you ever find yourself in need of additional report training, or low on time and needing a report built quickly, our CLA Sage Intacct team is here to help.