# Create a report from multiple sheets

The Smartsheet `POST /2.0/reports` endpoint allows you to create reports from multiple sheets with custom filtering, grouping, and sorting. This article demonstrates how to create a report that pulls data from two task sheets and organizes it by task assignee.

The following image shows a task report based on two project task sheets.

![](/assets/tasks-report.bbedf30d272ae3a0bc7359fd1f674c4ef35e0bab7bb1555a1b8e469da2d37d6b.7657d50c.png)

The report was created from a cURL call to the `POST /2.0/reports` operation. The cURL command (shown [later](#step-6-call-the-operation)), defines the report using the following request body payload. This article walks you through creating such a payload to create your own reports.

**Example payload:**


```javascript
{
  "name": "Tasks Report",
  "scope": [
    {
      "assetType": "sheet",
      "assetId": 123456
    },
    {
      "assetType": "sheet",
      "assetId": 7890
    }
  ],
  "columns": [
    {
      "index": 0,
      "type": "TEXT_NUMBER",
      "primary": true,
      "title": "Task ID"
    },
    {
      "index": 1,
      "type": "TEXT_NUMBER",
      "sheetNameColumn": true,
      "title": "Sheet Name"
    },
    {
      "index": 2,
      "type": "TEXT_NUMBER",
      "title": "Summary"
    },
    {
      "index": 3,
      "type": "PICKLIST",
      "title": "Status"
    },
    {
      "index": 4,
      "type": "CONTACT_LIST",
      "title": "Assigned To"
    },
    {
      "index": 5,
      "type": "DATETIME",
      "systemColumnType": "CREATED_DATE",
      "title": "Created Date"
    }
  ],
  "destination": {
    "destinationType": "workspace",
    "destinationId": 6789012345
  },
  "reportDefinition": {
    "filters": {
      "operator": "OR",
      "criteria": [
        {
          "column": {
            "type": "PICKLIST",
            "title": "Status"
          },
          "operator": "EQUAL",
          "values": ["Not started"]
        },
        {
          "column": {
            "type": "PICKLIST",
            "title": "Status"
          },
          "operator": "EQUAL",
          "values": ["In progress"]
        },
        {
          "column": {
            "type": "PICKLIST",
            "title": "Status"
          },
          "operator": "EQUAL",
          "values": ["In review"]
        }
      ]
    },
    "groupingCriteria": [
      {
        "column": {
          "type": "CONTACT_LIST",
          "title": "Assigned To"
        },
        "sortingDirection": "ASCENDING",
        "isExpanded": true
      }
    ],
    "summarizingCriteria": [
      {
        "column": {
          "type": "CONTACT_LIST",
          "title": "Assigned To"
        },
        "aggregationType": "COUNT"
      }
    ],
    "sortingCriteria": [
      {
        "column": {
          "type": "DATETIME",
          "systemColumnType": "CREATED_DATE"
        },
        "sortingDirection": "DESCENDING"
      }
    ]
  }
}
```

## Before You Begin

Here are the requirements:

- A valid Smartsheet API token for authentication
- The IDs for your source sheets. The example refers to these source sheets:
  - ABC tasks sheet ID: `123456`
  - XYZ tasks sheet ID: `7890`


## Step 1: Initialize the Request Body

Start by creating a JSON object for your report request. The request body requires the following properties:

- `name` — The name of the report (required, max 50 characters)
- `columns` — The columns to include in the report (required)
- `scope` — The source sheets to include (required)
- `destination` — Where to create the report (required)
- `reportDefinition` — Optional filters, grouping, and sorting
- `isSummaryReport` — Boolean indicating if this is a summary report (default: false)


Here's the basic structure:


```json
{
  "name": "Tasks Report",
  "destination": { },
  "scope": [ ],
  "columns": [ ],
  "reportDefinition": { }
}
```

Name your report.

## Step 2: Set the Destination

The `destination` property indicates where to create the report. You can create reports in folders or workspaces.


```json
  "destination": {
    "destinationType": "workspace",
    "destinationId": 6789012345
  },
```

The `destination` object requires:

- **destinationType**: Either `"folder"` or `"workspace"`
- **destinationId**: The numeric ID of the folder or workspace where the report will be created


## Step 3: Specify the Source Sheets (Scope)

The `scope` property defines which sheets the report draws data from. For each sheet, you'll create an asset object with an asset type and asset ID.

Add both of your source sheets to the scope array:


```json
  "scope": [
    {
      "assetType": "sheet",
      "assetId": 123456
    },
    {
      "assetType": "sheet",
      "assetId": 7890
    }
  ],
```

The `scope` array can contain:

- Individual sheets (using `assetType: "sheet"`)
- Entire workspaces (using `assetType: "workspace"`)


This report includes two specific sheets. Each sheet object requires:

- **assetType**: Set to `"sheet"` for a sheet or `"workspace"` for a workspace
- **assetId**: The numeric ID of the sheet or workspace


## Step 4: Define the Report Columns

The `columns` property specifies which columns appear in your report. Both source sheets share the same column structure, so you can combine columns from both sheets.

The example uses these columns from the source sheets:

| Column Title | Type | Description |
|  --- | --- | --- |
| Task ID | TEXT_NUMBER (Primary) | Unique task identifier |
| Sheet Name | TEXT_NUMBER (sheetNameColumn) | Source sheet declaration |
| Summary | TEXT_NUMBER | Brief task description |
| Status | PICKLIST | Task status (Not started, In progress, In review, Completed) |
| Assigned To | CONTACT_LIST | Person assigned to the task |
| Created Date | DATETIME (System) | When the task was created |


Add columns to your report by specifying them with a `type` and identifier. Here's an example that includes the primary column, status, and assignment:


```json
  "columns": [
    {
      "index": 0,
      "type": "TEXT_NUMBER",
      "primary": true,
      "title": "Task ID"
    },
    {
      "index": 1,
      "type": "TEXT_NUMBER",
      "sheetNameColumn": true,
      "title": "Sheet Name"
    },
    {
      "index": 2,
      "type": "TEXT_NUMBER",
      "title": "Summary"
    },
    {
      "index": 3,
      "type": "PICKLIST",
      "title": "Status"
    },
    {
      "index": 4,
      "type": "CONTACT_LIST",
      "title": "Assigned To"
    },
    {
      "index": 5,
      "type": "DATETIME",
      "systemColumnType": "CREATED_DATE",
      "title": "Created Date"
    }
  ],
```

### Column Matching Rules

When defining columns, the API matches source sheet columns based on their attributes:

- **Regular columns**: Matched by `type` and `title`
- **System columns**: Matched by `type` and `systemColumnType` (such as CREATED_DATE or MODIFIED_DATE)
- **Primary column**: Matched by `type: "TEXT_NUMBER"` and `primary: true`
- **Sheet name column**: Matched by `type=TEXT_NUMBER` and `sheetNameColumn=true`


**Important**:

- All column `index` values must be unique
- For row reports, exactly one column must have `primary: true`
- For summary reports, exactly one columns must have `sheetNameColumn: true`
- You can combine multiple columns of the same type from different sheets (e.g., multiple PICKLIST columns)
- However, you cannot combine CHECKBOX columns with PICKLIST columns


## Step 5: Add Filtering, Grouping, and Sorting (Optional)

For more advanced reporting, you can add a `reportDefinition` object with filters, grouping criteria, and sorting criteria.

### Example: Filter by Status and Group by Assignment

This example creates a report that shows only tasks in progress or in review, grouped by the assigned person:


```json
  "reportDefinition": {
    "filters": {
      "operator": "OR",
      "criteria": [
        {
          "column": {
            "type": "PICKLIST",
            "title": "Status"
          },
          "operator": "EQUAL",
          "values": ["Not started"]
        },
        {
          "column": {
            "type": "PICKLIST",
            "title": "Status"
          },
          "operator": "EQUAL",
          "values": ["In progress"]
        },
        {
          "column": {
            "type": "PICKLIST",
            "title": "Status"
          },
          "operator": "EQUAL",
          "values": ["In review"]
        }
      ]
    },
    "groupingCriteria": [
      {
        "column": {
          "type": "CONTACT_LIST",
          "title": "Assigned To"
        },
        "sortingDirection": "ASCENDING",
        "isExpanded": true
      }
    ],
    "summarizingCriteria": [
      {
        "column": {
          "type": "CONTACT_LIST",
          "title": "Assigned To"
        },
        "aggregationType": "COUNT"
      }
    ],
    "sortingCriteria": [
      {
        "column": {
          "type": "DATETIME",
          "systemColumnType": "CREATED_DATE"
        },
        "sortingDirection": "DESCENDING"
      }
    ]
  }
```

### Filter Options

The `reportDefinition.filters` object supports many operators:

- **Comparison**: `EQUAL`, `NOT_EQUAL`, `GREATER_THAN`, `LESS_THAN`, `BETWEEN`, `NOT_BETWEEN`
- **Text**: `CONTAINS`, `DOES_NOT_CONTAIN`
- **Date**: `TODAY`, `PAST`, `FUTURE`, `LAST_N_DAYS`, `NEXT_N_DAYS`
- **Checkbox**: `IS_CHECKED`, `IS_UNCHECKED`
- **Null checks**: `IS_BLANK`, `IS_NOT_BLANK`
- **Type checks**: `IS_NUMBER`, `IS_NOT_NUMBER`, `IS_DATE`, `IS_NOT_DATE`


### Grouping options

In this example, the `reportDefinition.groupingCriteria` array aggregates the rows based on the `Assigned To` value. It's a way of grouping tasks by person.

### Group summarizing options

The `reportDefinition.summarizingCriteria` adds a row count in each grouping row. It shows the person's number of open tasks.

### Sorting order

Here the example sorts the rows (in this case, each grouping's rows) by most recent `Created Date`.

## Step 6: Call the operation

Use cURL or your preferred HTTP client to POST your report request:


```bash
curl -i -X POST \
  https://api.smartsheet.com/2.0/reports \
  -H 'Authorization: Bearer YOUR_TOKEN_HERE' \
  -H 'Content-Type: application/json' \
  -H 'Smartsheet-Integration-Source: MyOrg,MyApp,v1' \
  -d '{
  "name": "Tasks Report",
  "scope": [
    {
      "assetType": "sheet",
      "assetId": 123456
    },
    {
      "assetType": "sheet",
      "assetId": 7890
    }
  ],
  "columns": [
    {
      "index": 0,
      "type": "TEXT_NUMBER",
      "primary": true,
      "title": "Task ID"
    },
    {
      "index": 1,
      "type": "TEXT_NUMBER",
      "sheetNameColumn": true,
      "title": "Sheet Name"
    },
    {
      "index": 2,
      "type": "TEXT_NUMBER",
      "title": "Summary"
    },
    {
      "index": 3,
      "type": "PICKLIST",
      "title": "Status"
    },
    {
      "index": 4,
      "type": "CONTACT_LIST",
      "title": "Assigned To"
    },
    {
      "index": 5,
      "type": "DATETIME",
      "systemColumnType": "CREATED_DATE",
      "title": "Created Date"
    }
  ],
  "destination": {
    "destinationType": "workspace",
    "destinationId": 6789012345
  },
  "reportDefinition": {
    "filters": {
      "operator": "OR",
      "criteria": [
        {
          "column": {
            "type": "PICKLIST",
            "title": "Status"
          },
          "operator": "EQUAL",
          "values": ["Not started"]
        },
        {
          "column": {
            "type": "PICKLIST",
            "title": "Status"
          },
          "operator": "EQUAL",
          "values": ["In progress"]
        },
        {
          "column": {
            "type": "PICKLIST",
            "title": "Status"
          },
          "operator": "EQUAL",
          "values": ["In review"]
        }
      ]
    },
    "groupingCriteria": [
      {
        "column": {
          "type": "CONTACT_LIST",
          "title": "Assigned To"
        },
        "sortingDirection": "ASCENDING",
        "isExpanded": true
      }
    ],
    "summarizingCriteria": [
      {
        "column": {
          "type": "CONTACT_LIST",
          "title": "Assigned To"
        },
        "aggregationType": "COUNT"
      }
    ],
    "sortingCriteria": [
      {
        "column": {
          "type": "DATETIME",
          "systemColumnType": "CREATED_DATE"
        },
        "sortingDirection": "DESCENDING"
      }
    ]
  }
}
```

Replace:

- `YOUR_TOKEN_HERE` with your Smartsheet API token
- `987654321` with the actual destination workspace ID
- Sheet IDs (`123456`, `7890`) if they differ from your environment


On success (HTTP 200), the API returns a report object like the one below.

**Example response:**


```json
{
  "message": "SUCCESS",
  "resultCode": 0,
  "result": {
    "id": 56789,
    "name": "Tasks Report",
    "accessLevel": "OWNER",
    "permalink": "https://app.test.smartsheet.com/reports/...",
    "isSummaryReport": false,
    "columns": [
      {
        "virtualId": 1000,
        "index": 0,
        "title": "Task ID",
        "type": "TEXT_NUMBER",
        "primary": true,
        "sheetNameColumn": false,
        "hidden": false,
        "version": 0,
        "width": 150,
        "validation": false
      },
      {
        "virtualId": 1001,
        "index": 1,
        "title": "Sheet Name",
        "type": "TEXT_NUMBER",
        "primary": false,
        "sheetNameColumn": true,
        "hidden": false,
        "version": 0,
        "width": 150,
        "validation": false
      },
      {
        "virtualId": 1002,
        "index": 2,
        "title": "Summary",
        "type": "TEXT_NUMBER",
        "primary": false,
        "sheetNameColumn": false,
        "hidden": false,
        "version": 0,
        "width": 150,
        "validation": false
      },
      {
        "virtualId": 1003,
        "index": 3,
        "title": "Status",
        "type": "PICKLIST",
        "primary": false,
        "sheetNameColumn": false,
        "hidden": false,
        "version": 0,
        "width": 150,
        "validation": false
      },
      {
        "virtualId": 1004,
        "index": 4,
        "title": "Assigned To",
        "type": "CONTACT_LIST",
        "primary": false,
        "sheetNameColumn": false,
        "hidden": false,
        "version": 0,
        "width": 150,
        "validation": false
      },
      {
        "virtualId": 1005,
        "index": 5,
        "title": "Created Date",
        "type": "DATETIME",
        "systemColumnType": "CREATED_DATE",
        "primary": false,
        "sheetNameColumn": false,
        "hidden": false,
        "version": 0,
        "width": 150,
        "validation": false
      }
    ]
  }
}
```

Key fields in the response:

- **id**: The unique identifier for your newly created report
- **permalink**: The URL to view the report in Smartsheet
- **columns**: The report's column definitions with virtual IDs
- **isSummaryReport**: Whether this is a summary report (false for row reports)


## Related content

- [Reports API reference](/api/smartsheet/openapi/reports)
- [Column schema reference](/api/smartsheet/openapi/columns/column)