System Roles

Administrator

Process Designer

Creating Excel Report Templates Using Queries

Note:  Although the Report - Excel <version>.xltx base template is still available, Sopheon recommends creating online reports available from Workspace > My Workspace > Charts & Reports, or using the Accolade Office Extensions add-in.

If you are comfortable writing SQL queries, or if you need to create a report that requires an advanced database query, add a workbook to an Excel file that is populated with data from queries created within Accolade. To use queries within an Excel report, use the Report - Excel <version>.xltx base template available on the Base Templates Reference Page. This file is set with the workbooks and structure required for creating Excel reports that contain queries.

You can create a file based on Report - Excel <version>.xltx and save it to the Template Library to use as a base template for similar reports. However, in doing so:

  • Do not delete, insert, or rearrange the data columns on the data worksheets. The column structure on these worksheets must match that of their queries. Rewrite queries to match the data worksheets.
  • Do not add Excel pivot tables or other sets of data to the data worksheets.
  • Do not add formulas to the columns of the data tables on the data worksheets. You can add formulas to the columns to the right of the rightmost query-populated column. The import automatically propagates the formulas across the imported data.
  • Include only numbers, letters, underscores, and periods in the heading of a formula column. Other characters cause an "Invalid Name" error when the workbook is opened in Accolade.

The information below describes how to add queries to an Excel file. For information about the Accolade query infrastructure, see Queries Overview.

Note:  The queries that are available for selection are based on your access group permissions as defined in your user profile. Only queries that the user has "Can Edit" access for will be available for selection. Additionally, access group settings for the query must match the user permissions of other Process Designers in order to display for them. For an overview of how to use access groups to restrict configuration components, see Restricting Configuration via Access Groups Overview.

To create an MS Excel report using a query:

Note:  The following procedure assumes that you have created the appropriate queries for the report and that you are familiar with basic Excel functionality.

  1. Add an Excel report to Accolade.

Select the queries to use in the report and select either the Report - Excel <version>.xltx file or a template based on it as the template file.

  1. Click Preview to populate Accolade data into the file and save the preview file to your computer.

Save the file using a name that identifies the nature and purpose of the report. This is the file that becomes the template that is added back to the Template Library.

  1. Check the first data worksheet to confirm that the data is what you want and to identify the cell range it occupies.

For the workbook to refresh correctly, the worksheet must contain at least two rows of data. If the query only retrieved one row, add a second row of data manually on the data worksheet and identify both rows in data ranges.

  1. (Optional) To manipulate the data with formulas, add the formulas to the right or left of the table.
  2. Continue to format the workbook by inspecting the next data worksheet, adding a new worksheet, and creating a report. You can create one or more reports for each data worksheet in the workbook.
  3. Hide the data worksheets and any other worksheets that users of the report should not modify.
  4. Save the completed Excel workbook to your computer and add the template to the Template Library.
  5. From the System menu, select Content Sources > Charts & Reports Manager, and click the name of the report you created in step 1.
  6. In the Template field, select the MS Excel file you added to the Template Library in step 8.
  7. Click Update to save the changes to the report.

Notes:   

  • When a query refreshes the data set, it can retrieve more, or fewer rows, but it cannot retrieve a different number of columns from the database.
Learn More: