Smartsheet Gov: Report on data from multiple sheets

Overview

Row Reports allow you to aggregate row information from multiple sheets. For example, if you want to see all tasks that are assigned to you, labeled as high priority, and not completed, and if you want to see those tasks from multiple sheets, you can do that with a row report.

A report can be sent, shared, and published like a sheet. You can also edit and sort data in a report. Note that a report is different from a sheet because no information is stored within it. Because of this difference, reports don't count against the sheet limit for your plan.

The report Owner and Admins can edit the report criteria in the report builder.

• To edit data in the report cells, you need to be shared to the report and source sheets with Editor permissions or higher.
• To view data in the report's cells, you need to be shared to the report and source sheets with Viewer permissions or higher. 

Build a row report

Step 1: Create the report container

You'll create your report in the Report Builder. Here’s how to get started:

1. Select the Create solution (plus icon) tab on the left sidebar.
2. Select the Report tile and then select Row Report.
3. Enter a name for your report, select “Row report,” and select OK.

Your new report is created in the Sheets folder.

Step 2: Select criteria to include in the report

Build your report using the criteria options available in the Report Builder.

Where?

select the Where? button to select the sheets and workspaces that you want to include in the report. Sheets that you have created or that have been shared to you are available from this list.

To have the report automatically include new sheets, you can reference an entire workspace. When a new sheet is added to the workspace, the report will reference it automatically. To learn more about workspaces, see Managing items in a workspace.

Keep the following in mind:

• You must select at least one sheet, folder, or workspace from Where? before the other options (Who?, What?, and When?) become available in the Report Builder.
• If you select a folder, new sheets added to that folder after you've selected it won't be included in your report. Remove the folder from Where? then add it back to include newly added sheets.

Who?

select the Who? button to select the people you’d like to see in the report (for example, Assigned To is Steve). select the column name for the column that includes the people you want to include in the report. Columns with the Contact List column type are available for selection here. In Step 2 of 2, select the names of the people whose tasks you want to see. (For more information about column types, see Column types.)

You can make the report more versatile by selecting Current user as the criteria for Who. For example, if you plan to share the report and if you want to make it such that the person viewing the report will see tasks assigned to them, select the Assigned To column, and then select Current user.

What?

Select report criteria to base the results on (for example, % Complete is equal to 1—where 1 represents 100%​​​​​).

When?

Select a specific date or range of dates to display (for example, display rows where the Due Date is in the next 7 days). Only Date type columns are available to select from this list.

Step 3: Choose which information will appear in the report

To choose which sheet columns appear in your report, select Columns in the Report Builder form. To arrange columns, you can drag them to different locations on the sheet. By default, the columns that you use for the report criteria are automatically added to the report along with the Primary column and a Sheet Name column. You can opt not to display these default columns and you can add additional columns by selecting the column names.

Step 4: Run the report and view it

select Save to generate the report.

Note that running a report for the first time will generally take longer than subsequent runs.

Close the Report Builder to view the report.

The report will show 500 rows per page. If more than 500 rows meet the criteria of the report, use the Prev and Next buttons at the bottom of the report to move between pages.

TIP: If your report includes date columns, you can view the data in Gantt View and Calendar View. Use the Gantt View and Calendar View buttons on the left toolbar to switch between these views.

Modify an existing report

From an existing report, open the Report Builder by selecting the Report Builder button at the top of the report.

To add or remove columns in the report, right-select a column header and select Add Column or Remove Column.

Here are some things to keep in mind as you modify reports:

• The formatting (cell, font color, background color, bold text, and so on) that appears in a report is based on the formatting in the source sheet.
• Row hierarchies aren't displayed in reports.
• Cells containing formulas can't be modified from a report.
• If dependencies are enabled on a sheet:
  • The End Dates for rows in those sheets aren't editable from a report because they are calculated automatically based on the dependency settings. You can instead edit the Start Date and Duration in the row to automatically re-calculate the End Date.
  • The Start Date of any task that is driven by a predecessor won't be editable from a report. You can instead change the Start Date or Duration of the predecessor task to automatically re-calculate the dates associated with dependent tasks.

For more information, see the Help Center article Enable dependencies and use predecessors.

Tips for working with report criteria

Delete report criteria

From an existing report, open the Report Builder by selecting the Report Builder button at the top of the report.

select on an existing value to edit it.

To delete a report criterion, mouse over it and select the delete button (x) that appears to the right of the criterion.

Use multiple criteria

To add multiple criteria to any section, select Who, What, or When again. For example, in the What section, you might opt to set criteria such as Priority is High and Status is In Progress.

Use And and Or operators in sections with multiple criteria

By default, you'll see an And operator within a section if you have multiple criteria: this means a row will need to meet all of the criteria for it to be displayed in the report. select any and operator to change the operators to or operators. In this case, as long as a row meets one of the criteria, it will appear in the report. There isn't a way to have a mix of And and Or operators within the same section.

Use And and Or operators across sections

By default, And operators occur between the Who, What, and When sections of the Report Builder. You can select on any and operator to change them all to Or, but you can't have a mix of both And and Or operators between sections.

Create an “Is Not” condition

If you want to exclude information from a report, use the Exclude selected items checkbox. For example, if you want to display rows for which an address column is not blank, select the is blank condition, and check the Exclude selected items checkbox.

Troubleshoot issues with reports

Who field shows wrong number in parenthesis

The numbers in parentheses that appear next to the contact names refer to the number of times that contact appears in Contact List columns across the sheets that are selected. If this number doesn't match the number of rows generated by the report, it may be because the contact's name was manually typed into cells in sheets as opposed to being selected from the Smartsheet contact list.

To correct this, open the source sheet and mouse over the name of the team member in each cell. If an "Add Contact Info" message appears, select the link and add an email address to the contact name. When you re-run the report, the number in parenthesis should display the expected number of rows.

Shared report is appearing blank

Reports will appear as blank, or appear to have less information, when someone else looks at the report and they're not shared to the underlying sheets that the report is looking at. A report doesn't give access to sheets that haven't already been shared to someone (For more information see Share sheets, reports, and dashboards).

If they don't need to edit data on the report, you can publish the report to display it in a read-only format. When published, the underlying sheets don't need to be shared for the data to be visible on the published version. See Publish a report.

Columns in reports appear blank

This can happen if columns used in the Who, What, or When sections of the Report Builder have had their names changed in the underlying sheet. You’ll see [Column Name] - (column not found) if a column setup in the Report Builder isn’t present in the sheet.

To correct this, update the Report Builder to point to updated column name, and remove the old column name.