Smartsheet Gov: Report on data from multiple sheets
With a report, you can compile information from multiple sheets and show only items that meet the criteria you specify.
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, that are labeled as high priority, and that are not complete, and if you want to see those tasks from multiple sheets, you can do that with a row report.
Similar to a sheet, a report can be sent, shared, and published. 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 account.
To make shared reports more flexible, you can specify Current user as one of the criteria for which rows are displayed in the report. Current user is the person viewing the sheet and is based on the email address that that person used to sign in to Smartsheet. You can use the Current user criterion, for example, to create a report that all of your team members can use to see their own assigned tasks without having to create multiple reports.
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:
- Click the Menu icon (upper-left corner of the Smartsheet window) > Browse.
- Click the Solution Center (plus) icon.
- In the left panel, click Create, and select the Report tile.
- Enter a name for your report, select “Row report,” and click 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?
Click 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.
TIP: 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?
Click the Who? button to select the people you’d like to see in the report (for example, Assigned To is Steve). Click 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.)
TIP: 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, click 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 clicking the column names.
Step 4: Run the report and view it
Click 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 clicking the Report Builder button at the top of the report.
To add or remove columns in the report, right-click 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.
Tips for working with report criteria
Delete report criteria
From an existing report, open the Report Builder by clicking the Report Builder button at the top of the report.
Click on an existing value to edit it.
To delete a report criterion, mouse over it and click the delete button (x) that appears to the right of the criterion.
Use multiple criteria
To add multiple criteria to any section, click 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. Click 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 click 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, click 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.