Export Process Report to Excel Smart Service

This is the new Export to Excel smart service. If you need to export to CSV, use the Export Process Report to CSV Smart Service.

Summary

The Export Process Report to Excel Smart Service allows designers to safely export large amounts of data from process reports.

It can be used to export data from Appian that can then be imported into other third-party applications. A designer may want to export all data or just updates made within the last day.

The Smart Service returns a document in an Excel format.

Appian strongly recommends customers to use this smart service in place of Export Portal Report to Excel (part of the Excel Tools Plug-in).

Properties

  • Category: Document Generation
  • Icon: alt text
  • Assignment Options: This smart service can be either attended or unattended (default).

Node Inputs

Name Type Description Required Multiple
Process Report Process Report The process report to query. Y N
Filters Any Type A set of conditions to apply to the queried data. Can use either a list of a!queryFilter() or a single a!queryLogicalExpression(). When using a list of a!queryFilter(), all filters are combined using the AND operator. Filters saved to the process report are applied before these are applied. N Y
Report Context Any Type Contains an array of either Group, User, Number (Integer), or Process Model to pass to the report context. N N
New Document Name Text The name of the new document that is to be created by this smart service activity. N N
New Document Description Text A brief description of the document. N N
Save in Folder Folder The folder where the newly created document is to be stored. Applicable and required when creating a new document. N N
Document to Update Document The existing document to update with the exported data. This can be useful when using the smart service multiple times in the same process model. Note: A new version of this document will be created. N N
Include header Boolean Outputs the label of each field in the first row of the document if set to true. Default: true N N
Sheet Name Text The name of the sheet where data will be added as a result of executing this smart service. The following characters will be replaced with underscores: [ ] / \ ? * N N
Sheet Number Number (Integer) The sheet number where data will be added as a result of executing this smart service. Default is 1, the first sheet. N N
Starting Cell Text The cell where the output will begin. Default: "A1". N N
Custom Cell Positions Text Array of cell positions where custom cell values should be added. N Y
Custom Cell Values Text Array of values to be added on the specified custom cell position. N Y

Node Outputs

Name Data Type Description
New Document Document The document generated as a result of executing this smart service.

a!exportProcessReportToExcel()

Exports data from a process report to Excel. This function will only execute inside a saveInto or a Web API.

Syntax

a!exportProcessReportToExcel( report, filters, context, documentName, documentDescription, saveInFolder, documentToUpdate, includeHeader, sheetName, sheetNumber, startingCell, customCellPositions, customCellValues, onSuccess, onError)

  • report (Document): The process report to query.
  • filters (Any Type): (Optional) A set of conditions to apply to the queried data. Can use either a list of a!queryFilter() or a single a!queryLogicalExpression(). When using a list of a!queryFilter(), all filters are combined using the AND operator. User filters saved to the process report are applied before these are.
  • context (Any Type): Contains an array of either Group, User, Number (Integer), or Process Model to pass to the report context.
  • documentName (Text): The name of the new document that is to be created by this smart service.
  • documentDescription (Text): (Optional) A brief description of the document.
  • saveInFolder (Folder): The folder where the newly created document is to be stored. Applicable and required when creating a new document.
  • documentToUpdate (Document): The existing document to update with the exported data. Using this parameter will append new contents at the end of the file. Note: A new version of this document will be created.
  • includeHeader (Boolean): (Optional) Outputs the field's label in the first row of the document if set to true. Default: true
  • sheetName (Text): (Optional) The name of the sheet where data will be added as a result of executing this smart service. The following characters will be replaced with underscores: [ ] / \ ? *
  • sheetNumber (Number (Integer)): (Optional) The sheet number where data will be added as a result of executing this smart service. Default is 1, the first sheet.
  • startingCell (Text): (Optional) The cell where the output will begin. Default: A1.
  • customCellPositions (List of Text String): (Optional) Array of cell positions where custom cell values should be added.
  • customCellValues (List of Text String): (Optional) Array of values to be added on the specified custom cell position.
  • onSuccess (Any Type): A list of saves or an HTTP response to execute after the smart service executes successfully. Created with a!save() or a!httpResponse(). The document ID of the created/updated document is returned in the fv!newDocument function variable.
  • onError (Any Type): A list of saves or an HTTP response to execute when the smart service does not execute successfully. Created with a!save() or a!httpResponse().

Returns

Document

Example

Copy and paste an example into the INTERFACE DEFINITION in EXPRESSION MODE to see how this works. You will need to provide a constant to replace both REPORT and TARGET_FOLDER

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
load(
  local!exportId,
  {
    a!linkField(
      labelPosition: "COLLAPSED",
      links: a!dynamicLink(
        label: "Render Excel File",
        saveInto: {
          a!exportProcessReportToExcel(
            report: cons!REPORT,
            documentName: "Report " & now(),
            saveInFolder: cons!TARGET_FOLDER,
            onSuccess: a!save(
              local!exportId,
              fv!newDocument
            )
          )
        }
      )
    )
  }
)

Additional Details

The following configurations and expected behavior apply when using the Export Process Report to Excel smart service from the process modeler:

  • To create multiple tabs in the same Excel spreadsheet with data from different process reports, use multiple nodes of this smart service. You can use the Document to Update and Sheet Number parameters when exporting a second process report.

  • You may specify either a LogicalExpression or a list of Filters in the Filters parameter but not both. If you wish to specify only one condition, specify a Filter. If you wish to specify more than one condition, use LogicalExpression or use a list of Filters.
  • Due to performance considerations, there is a max row limit of 10,000 rows and a max column limit of 50 columns that can be exported.
  • When using fields of type Date or Date and Time in a filter and the filter's value uses either the todate(), now() or today() functions, make sure to cast their value using the appropriate type. For example: cast('type!{http://www.appian.com/ae/types/2009}Date', today()), or cast('type!{http://www.appian.com/ae/types/2009}Datetime', now())

Data Types:

See Also

FEEDBACK