In my previous blog posts, I’ve highlighted NetSuite’s saved search functionality and shown how you can use them to create dynamic reports to improve visibility into all business areas. While you can create highly-customized saved searches that use joins to other records, formulas to perform automatic calculations, and even include SQL statements for advanced requirements, there are some instances where saved searches don’t entirely fill an organization’s reporting needs. In those cases, you can leverage NetSuite’s custom report capabilities in order to bridge the gap. In this post, I’ll share the basics of report customization in NetSuite and show how standard reports that are already available in your environment can be transformed to align with your specific needs.
Report Customization Basics
The best way to create a custom report in NetSuite is to start from whichever standard report is closest to your desired results. Unlike with saved searches, where all of the report criteria are visible and editable, a portion of the criteria that defines each standard report is actually built into the report and cannot be viewed or modified. Because of this, it’s recommended to always start from a standard report. Even as an experienced user, I always start from a standard report when I can – not only does it provide users with a clearer understanding of how the data will be structured to determine if it will meet their needs, but it’s also much faster and easier than starting from scratch. There’s no need to reinvent the wheel when NetSuite has already done most of the hard work for you!
To customize a standard report, click the Customize button on the lower left corner of the report:
The ability to customize reports is controlled by permissions, so you (or your administrator) will need to review the settings on your role if this option is not available to you. If you are attempting to customize a report that has already been customized by another user in your organization, you may not have the ability to customize it depending on how the owner of that report has configured it. We’ll discuss this in more detail later in this post.
Once the Report Builder interface has loaded, you’ll see four tabs that will provide you with the available customization options:
Let’s review each of these tabs in detail to understand how the options contained within each setting will impact the appearance, content, and accessibility of your data.
Edit Columns
The Edit Columns subtab is akin to the Results subtab on saved searches, since it allows you to define what columns and rows will be displayed in your report. Unlike the Results subtab on saved searches, the Edit Columns subtab also includes a visual representation of the report structure under the Report Preview section:
Within the Report Preview is a list of rows and columns representing the data points that will be displayed and shows how it will be formatted. One of the unique capabilities offered by reports is the ability to present hierarchical data that can be expanded or collapsed. In the screenshot above, the list of items is presented grouped by Item Type, meaning that you can view the total purchase quantity and amount both by Item Type (i.e. assembly, inventory, service, etc.) and by individual item in the same report. Below is a sample of how that data would appear when collapsed:
Now, let’s add an additional field to this report showing the vendor from which each item was purchased. Use the list of fields on the left to locate the field that you are looking for. The structure of fields in reports is different from that within saved searches, so I find that I have to do a little manual searching in order to identify the correct field that yields the data that I’m looking for. In this case, I will need to add the Purchases > Vendor > Company Name field to my results. Once I’ve added it, I can drag and drop it to the desired location in the results and adjust the name using the Column Label section:
Instead of Vendor: Company Name, I’ve adjusted the label of this field so it displays as simply “Vendor.” Let’s say that you wanted to group by vendor instead of item type. To accomplish this grouping with a field, I’ll need to first remove the item type column by clicking on it within the report preview so that it is highlighted in green and then click the “Remove Column” button.
Next, I’ll click on the item column and check the Group With Previous Column preference, which will group the items and all following columns as a sublist underneath the vendor name.
Here’s how that change impacts the report results:
There are additional options when working with numeric fields that allow you to change the display of decimals and negative values, or add summarized values, grand totals, percentage of total, and alternate date ranges. Since we’re focusing on the basics of report customization in this post, I’ll save a detailed description of the impact of those options for a future blog, along with the ability to add calculated values into reports using formulas.
Filters
As with saved searches, reports allow for the addition of filtering criteria that optionally be exposed to those viewing the report results. When starting from a standard report, some filtering criteria will already be built in for you. Much of this criteria can be edited, but just be careful when making changes to the filters that are already configured as they may have unexpected impacts on the results.
To add a new filter, use the Add Fields menu on the left (the same that was used to add the vendor column) to select the field. Continuing with the example above, let’s add a filter to only show inventory purchased for a specific location (Purchases > Inventory Location > Name). Within the filter, I limited the results to only those for the 01 US – West Coast warehouse. I also checked the “Show in Filter Region” checkbox so that the filter displays below the report results for on-the-fly adjustments:
Here’s where that filter appears on the report:
Sorting
The Sorting subtab allows define the sorting order for displayed results. You can adjust the sorting order by dragging rows in the list (higher rows take precedence), by checking/unchecking the Descending checkbox, or by adding new fields using the Add Fields menu.
More Options
The More Options subtab is where you configure report visibility/accessibility along with the drill down report (if the report you are customizing is a summary report), the number of levels that are expanded by default, whether zeros are displayed, and a few other options. For the purposes of a basic introduction to customizing reports, I’ll focus only on the Audience and Access subtabs.
When creating a custom report, you have the ability to identify which users should be able to view and/or edit the report by role, subsidiary, employee name, department, group, or partner. You can choose a single field to define your audience or stack values from multiple fields to further refine it. Just be aware that selecting both a department and a subsidiary (for example) will cause the report to only be accessible for users who belong to BOTH the selected department and subsidiary.
The Audience subtab identifies which users should be able to view, but not edit, your custom report. The Access subtab indicates which users should be able to make changes to your custom report. If you add a user with permission to customize reports to the Audience subtab but not the Access subtab, the user will be able to create a new custom report based on your report but will not be able to overwrite your report.
Now that you understand the basics of customizing reports in NetSuite, you can quickly modify standard reports to provide you with more detailed, segmented, and ultimately more intelligent data to drive decision-making within your business.
Want to learn more about creating custom reports in NetSuite? Contact Concentrus today to discuss your needs.
