You can export dashboard components to Microsoft Excel. The first sheet of the file contains details about the dashboard. Subsequent sheets contain the dashboard data, one sheet for each report. All report data is presented in list format. If you find that you repeatedly export dashboard data into an Excel file that you then customize, you can use a template with the dashboard export to automate formatting and any other post-export manipulations you do on a regular basis.
To export a dashboard, run the dashboard and then choose Actions > Export to Excel.
You can also export to Excel from published dashboards (see below).
When exporting without a template, PPM Pro creates an Excel file for you; the filename is prefixed with the dashboard ID (found in the URL of the dashboard), then an underscore, then "dashboard.xlsx". For example, 64837743_dashboard.xlsx.
Dashboard component details are inserted onto the first sheet in the file, which is named IN_Dashboard_Details.
Data is inserted into one sheet per component, starting with sheet 2 (details are on sheet 1). The sheet naming convention is: the component name (up to 22 chars), underscore, the three letter output type (col, lis, pie, bbl, and xtl) and the component number. For example, myDashboard_col001, myDashboard_lis002, myDashboard_xtl003. Note that if any report name contains the following characters, those characters will be removed from the sheet name: : \ / ? * [ or ].
Regardless of component type, data is exported in list format.
Sample exported file:
To help automate your reporting process, you can upload an Excel template to a dashboard to be used each time the dashboard is exported. This allows you to automate tasks such as adding boilerplate text, applying macros, or another other kind of formatting that is required each time you prepare a new Excel-based report.
Only the dashboard owner can update or delete a template. Any user that has permission to view a dashboard can download the template.
There is a column in the Dashboards List page called "Excel." An Excel icon is displayed in this column if you have uploaded a template to a dashboard.
To create a template, we recommend creating the dashboard and then exporting it (without yet uploading a template) to create the starting point for your template. Then edit this file to create your template, keeping in mind the naming and positioning details described above. After completing your template you can upload it to the dashboard. (Note: Be sure to delete the dashboard data before you upload the template.) Each time you export, PPM Pro will overwrite the data pages, so be sure to make your customizations in your own custom sheets.
You should not make any changes in the sheets where report output is written. Data in these sheets is cleared before new report output is written. The sheet names will be based on the report names and will not change unless the report name change (as described above in Spreadsheet format and naming conventions).
You will create additional sheets for creating references to the data in the PPM Pro generated sheets; any formatting, formulas, and so on, should reside in a user-created output sheet that refers to the data sheets. You can name these sheets anything you'd like, but avoid using the default naming convention, because upon export PPM Pro looks for sheets using this convention and overwrites them with current data. If a sheet with that name does not exist, a new one is created. Your custom sheets will never be overwritten (provided there is no naming overlap).
You can create macros to process data in report sheets after the Excel file is opened (OnOpen) or by manually running a macro. Note that templates that contain macros should use the .xlxm suffix in their filename.
To upload/download a template
- Navigate to the Dashboards view and right-click on the dashboard you wish to upload the excel template to and choose Manage Excel Template.
- If there is no file associated with the dashboard, the dialog will look like the one below. If a template has previously been uploaded, the template name will appear. Subsequent uploads will overwrite existing templates.
- Click the Browse button and select a template to upload, and then click Upload. You'll now see the Excel icon in the Excel column for the dashboard.
- If you double-click the Excel icon in the Dashboards list, you will have the option to Download or Delete the template.
You can configure published dashboards to allow users to export data to Excel. This gives users access to the data without having to be logged into PPM Pro.
- Run the Dashboard and choose Actions > Publish Options.
- Enable Allow viewers to export dashboard data to Excel and click Save.