The report wizard step by step

The report wizard turns a SuiteQL query into a report that other people can open. Only authors see it. Start it with New Report in the top bar of the catalogue, or with Edit Report, in the gear menu, on an existing report. The wizard has five steps: Query, Columns, Filters, Details and Audience.

#1. Query

1. Write the SuiteQL statement.
2. Choose Preview. The wizard runs the statement and shows up to 100 rows. You cannot continue until Preview succeeds.

The app accepts one statement that starts with SELECT or WITH, up to 20,000 characters. It rejects a second statement, INTO, and unbalanced quotes or parentheses with a readable message. Give every column a unique alias. For queries that stay correct and fast, see Writing SuiteQL that performs.

#2. Columns

The step lists one row for each column the query returns, in the order the report shows them. The arrows in the Order column move a column up or down without touching the SQL. The order survives a new preview, and the wizard adds a column that is new to the query after the ordered ones.

For each column, set:

• Label: the heading viewers see.
• Format: text, number, currency, percent, date, datetime, month (for a YYYY-MM column), boolean, or dropdown (for a checklist filter).
• Formatting (optional): a link under the format of a number, currency, percent, date or datetime column. See "A column's own formatting" below.
• Visibility: hide helper columns, such as the internal id behind a link.
• Link (optional): pick a record type, then the hidden column that holds the record's internal id. The value then opens that NetSuite record. Administrators maintain the list of record types under Manage, Configuration, Record Types.
• Another report (drill-through) (optional): the same choice can make the column open a detail report. See Drill-through.

#A column's own formatting

A column follows the account's formatting settings unless you give it its own. Choose Formatting under the column's format to open it:

• Decimals, Compact (1.2M) and Negatives for a number or a currency column, and a Currency code for a currency column.
• Decimals for a percent column, and what the query returns for it: Values are fractions (0.31 is 31%) or Values are already percentages (31 is 31%).
• Date style for a date or a datetime column.

Under the choices, the step writes the first values of that column with what you chose. For a percent column it writes them both ways, so you see which reading is right. When most values are above 1.5, a note suggests the other reading. The app never changes it by itself.

A column you give the percent format takes the account's reading for new percent columns. A percent column already saved keeps its own.

See Formatting numbers, currencies, percentages and dates.

#Date or month

A column's format decides how charts and dashboards can group it:

• A date or datetime column can be shown by day, week, month, quarter, half year and year.
• A month column (YYYY-MM) can be shown by month, quarter, half year and year. It holds months, not days, so it cannot be shown by day or by week.
• Both can be shown by fiscal quarter, fiscal half year and fiscal year in an account whose fiscal year does not start in January.

The step says so under the format when you choose month. If readers will want weeks or days, return a regular date from the query and set the column to date.

#One report for month, quarter and year

If the report is stored by month, tick Month / Quarter / Year on the month column and choose the grain it opens on. The list also has Half year, and the fiscal grains in an account whose fiscal year does not start in January. Viewers then get Group by. Give each number, currency or percent column a Roll-up:

• Sum: the default for numbers and money. It also fits counts of things that each belong to one month.
• Ratio of two columns: for a rate that is one column over another, such as margin over revenue. The app recalculates it from the two sums and never averages it.
• Last month of the period: for cash, receivables, stock on hand and any other position at a month end. A quarter or a year shows the value of its last month, never the sum, and a totals row offers Closing value in place of Sum. See Fiscal periods, year to date and balances.
• Month only: blank at quarter and year. Use it for a median, a min or max, or a count of things that span months. The app treats a percent column with no rule as Month only.

#3. Filters

This step sets the filters the report opens with. Each visible column shows its type and a list of operators that suit it:

• Text operators accept several values, with a switch between Any of these (OR) and All of these (AND). A sentence under each filter spells out what it does.
• Is any of and Is none of show a checklist of the values in the stored result. After a refresh, use Reload values.
• Filters on different columns combine with AND.
• They run in the browser after the query, and viewers can change or clear them.
• An import keeps these filters. See Import and export.

#4. Details

• Name and Description: the catalogue shows them, and search reads them. Say what the report is for and who it is for.
• Categories: where the report sits in the sidebar. Pick as many as apply: the report is listed under each one, and with none it is Uncategorized. Admins can create a category here without leaving the step, and it is added to the picks.
• Rows per page: the default page size. Leave it on Account default, or choose All rows.
• Refresh interval: how often the report runs on its own. The choices come from Configuration.
• Refresh mode: Full, or Partial for a large result where little changes between runs. See Refresh and scheduling.

When editing, this step also lists the stored result files, with their sizes, links to their File Cabinet records, and downloads.

#5. Audience

• Tick Public to make the report visible to every employee.
• Otherwise, pick any mix of Roles, Employees, Departments and Subsidiaries from searchable lists. Anyone who matches at least one pick can open the report.
• Departments and Subsidiaries appear only when the account has those features.

See Categories, audience and rights.

#Saving

• For a new report, Create Report on the last step saves it.
• When editing, Save at the top right saves from any step, and Cancel returns to the report. Cancel asks first only if you changed something.
• View in NetSuite opens the report's own record in a new tab.
• Delete Report asks for confirmation. It removes the report, its result files and its NetSuite dashboard portlets permanently. To retire a report reversibly, archive it instead.

#Making a copy

To start a new report from an existing one:

1. Choose Make a Copy in the gear menu of the report's page, or Copy next to Edit in the catalogue. In selection mode, select one report and choose Make a Copy. Authors see these on the reports they can open.
2. The copy opens in the wizard on Details, named Copy of and the original's name. Rename it. A name already in use gets a number, such as Copy of Sales (2).
3. Set its audience on step 5, then choose Save.

What the copy takes, and what it does not:

• The copy takes the description, categories, SQL, columns, layout, starting filters, rows per page, refresh interval and refresh mode.
• The copy does not take the audience. Only you and admins can see it until you set one in step 5.
• The copy gets its own export key, so Import / Export and drill-through links treat it as a different report. Portlets and dashboard widgets stay on the original.
• The copy starts with no result. The app requests its first refresh when it creates the copy.

The original does not change.

#If something looks wrong

Related: Fiscal periods, year to date and balances · Writing SuiteQL that performs · Refresh and scheduling · Charts on a report · Drill-through · Formatting numbers, currencies, percentages and dates