Skip to main content
Appia® Knowledge Center

Custom reporting with Excel

 

In this article:

Related resources:

The Appia® Custom Reporting Excel workbook is a solution for users to export project data through the Appia API into a pre-built, customizable Excel workbook for standardized (or DOT or Custom) reporting.

Click to expand the following sections and learn more.

Download the workbook

Click here to download the Excel workbook.

Set up the workbook

Expand the following sections to learn how to set up and use the workbook.

Check your access and permissions

To import project data into the workbook, you must have the following permissions:

  1. API access, as granted by a System Administrator on the My Business page.
  2. Project access for the project you want to export data from.

To check your API access, go to SYSTEM MGT > My Business. Refer to the API Access column and make sure that your setting is ON.

apiaccessboxed.png

If you're missing the appropriate access, contact your business's System Administrator.

Create a Personal Access Token (PAT)

To access the data, you need a Personal Access Token (PAT). If you don't have one already, follow these steps to create one.

  1. From the My Projects page, click SYSTEM MGT > API.
  2. Click Go to Personal Access Tokens.
  3. Click Generate new token.
  4. On the new page, enter a token name and select your desired scopes.
    • Note: You must select the Query builder scope to use the workbook.
    • queryscope.png
  5. Click Generate new token.

Be sure to copy and paste your PAT to a secure location. Once you close the window, the token can't be retrieved.

systmgtapikeys.png
Find the project ID

The project ID used to import data to the workbook is found within the URL of the project when you're viewing the Project Overview page. Note that it's an alphanumeric value generated by Appia, not the project ID title entered by the user when the project was created.

  1. From the My Projects page, click on the Project ID (title) of a project.
  2. From your browser's address bar, highlight the alphanumeric portion found at the end of the URL.
  3. Copy and paste the project ID to a secure location.
urlhighlight.png
Enter your PAT and project ID into the workbook

The instructions for where to enter your PAT and project ID differ slightly between Windows and Mac devices.

Windows devices

  1. Open the workbook file in Excel.
  2. In the Security Warning banner, click Enable Content.
  3. Select the Data tab.
  4. Click Queries & Connections. A sidebar panel will appear.
  5. Hover or right-click over the Personal Access Token option and select Edit.
  6. Select the text that says ENTER_PERSONAL_ACCESS_TOKEN_HERE and paste your PAT to replace it.
  7. Repeat steps 5 and 6 for your project ID.
  8. Close the editor window. A confirmation window will ask if you want to keep your changes.
  9. Click Keep. You'll return to the workbook.
  10. From the Data menu, click Refresh All to retrieve project data from Appia. The workbook will be populated with the appropriate data for each tab.

Mac devices

  1. Open the workbook file in Excel.
  2. In the Security Warning banner, click Enable Content.
  3. Select the Data tab.
  4. Click the arrow on the Get Data (Power Query) option to expand the menu.
  5. Select Launch Power Query Editor. A new window will appear.
  6. From the Queries list, select Personal Access Token.
  7. In the Current value field, paste your PAT. Click Apply.
  8. From the Queries list, select Project ID.
  9. In the Current value field, paste your project ID. Click Apply.
  10. Close the Power Query Editor window. A confirmation window will ask if you want to keep your changes.
  11. Click Keep. You'll return to the workbook.
  12. From the Data menu, click Refresh All to retrieve project data from Appia. The workbook will be populated with the appropriate data for each tab.
Use the workbook

Once your project data has been successfully imported into the workbook, create a new tab in the workbook to begin building your custom report using Excel functions and formulas.

See the Excel help and learning page for assistance.

Troubleshoot

In this section you'll find common errors and their fixes.

Tip: if you're encountering errors, try refreshing a single query rather than refreshing all until the issue is resolved. This way you don't get an error message for each query.

Important notes

  • Projects must be in the Construction phase to export data into the workbook.
  • You may encounter other Excel errors not covered here. Refer to Microsoft's Excel help and learning page for additional troubleshooting.
Exception error

If you receive the following error when refreshing data, it means your PAT doesn't have access to the given project.

error.png

Check the following to resolve:

  • Your PAT has the Query builder scope selected.
  • Your Appia user account has the API Access flag set to ON in the business that owns the project.
    • Contact the business's System Administrator for help.
  • Your user account has access to the project in Appia. Project-level security might need to be enabled for your user.
Privacy error

If you receive a privacy error, update your privacy settings.

Windows devices

  1. Click to expand the Get Data menu.
  2. Select Query Options.
  3. Under Current Workbook, select Privacy.
  4. Select the Ignore the Privacy Levels option.
  5. Click OK.

Mac devices

  1. Click to expand the Get Data menu.
  2. Select Launch Power Query Editor.
  3. From the Home tab, click on the Options icon.
  4. Under Project, select Privacy.
  5. Select the Allow combining data from multiple sources option.
  6. Click OK.
  7. Close the editor window and click Keep to save your changes.