In the Smart Rules post (here), we went over how to create Smart Rules to ensure we maintain the integrity of the data we are basing our reports on. The reason I even stumbled upon learning Smart Rules and the need for them was from the creation of a certain report which lead to a shocking revelation of how much data was missing. Of course Smart Rules have taken over and become the proactive solution for this problem now, but the report was the first step in identifying the problem. This post will cover how to build this customized report through the Report Building Wizard.
The report will allow you to see a list of customers with missing sales representatives, product specialists, or start dates. Although this example is very specific, the process is the same in building any reports within this module. So if you can follow building this report, you’ll be able to build your own reports for whatever you need! (For example I used the same report building wizard to create an Expiring Credit Cards report, Refund/Credit Memo Report, etc.). So let’s get started! Bring on the pictures!
Finding the Wizard:
Customization Services> Custom Reports> Add
Step By Step:
1. Tabular vs. Summary –
2. Data Type –
This is a drop down menu which allows you to choose where you would like the data to come from. In this report we’ll be choosing “Customer”.
3. Columns –
This is the trickiest part of the Intacct Custom Report Wizard. The information you want to include on your report can be in multiple places on this step. The Customer Name for example can appear once at the Invoice level, and a second time within the line item. If you choose the Customer Name within the line item and some invoices don’t have that information there, you’ll be missing customer names in your report. I have often chosen columns to include in a report, then when I go to preview it there is no information in that column or some Customer Names are missing. *frustrating*. What I would suggest here is including multiple places you find what you want (if you don’t know where exactly it is), then running the report and eliminating excess columns. It’s better to have more information than not enough!
In this example I chose to include the following information on my report: Customer ID, Customer Name, Customer Type ID, Customer Rep, Product Specialist, Username, and Start Date.
4. Calculated Columns –
I can’t say that I have really used this step very much. The times that I’ve wanted to, the functions available are very limited. In this step you can create a column that adds, subtracts, divides, or multiplies your other columns. I *really* wish this step allowed for Excel type functions. IF statements would be so useful!
5. Sort Results –
This is where you select how your results are sorted. In my example I chose to sort by Start Date “Descending” meaning blanks will show up at the top and then newest customers to oldest.
6. Select Filters –
This is my favorite step! And I love that Intacct built this in. This is where you can filter the data you want on your report. For example when I run this report I don’t want to have to see all the customers we have in our database every time (that would take an eternity to load each time! as much as I wish we had nap time at work sometimes -while I’m building reports is not the time). I set my filters so that any Customer with a blank (“field equals null” in Intacct language) Start Date, Customer Rep, or Product Specialist will appear in the report.
7. Advanced Filter –
I like this step too! It’s just choosing whether you want the previous filters you defined to be “OR”, “AND”, or some combination. I chose OR because I want the report to bring up any customer that has a missing start date, OR missing sales rep, OR missing product specialist. If you wanted to see only customers that have all 3 missing fields you would choose AND. (Easy, right?).
8. Summary Information-
This step allows you to choose what you would like to summarize/count at the very bottom of the report. For example if you wanted to know the count of customers who are missing information in this report, you would choose the “Count” checkbox next to “Customer ID” or “Customer Name”.
9. Column Sequence –
This is just how you want your report to be organized. Intacct is asking do you want Customer ID’s all the way to the left, then their Name, then their Start Date? It’s just an organizational step (I like it but not everyone cares how their reports are set up even though it drives me nuts personally).
10. Run Report –
Just what you want the default name of your report to be each time it is run.
11. Runtime Parameter Fields –
This allows you to choose which fields you would like to show up when someone goes to run your report. For example I chose Start Date here. If someone runs this report and leaves this field blank all customers with one of the 3 fields missing will show up. If the user chooses 01/01/2015 – 01/31/2015, the report will generate a list of customers with missing Sales Reps or Product Specialists that had a start date in that range.
12. Runtime Parameter Options-
This is simply the order of which runtime parameter you specified in the last step you want to appear first.
13. Describe your Report-
You made it to the very last step! This is where you title your report and give it a description. If you’re just testing and still in the process of building your report I wouldn’t choose “Deploy”. Once you’ve finalized it and it looks good you can hit “Deploy” so the rest of the company can use your fancy report!