Creating Custom Reports with Excel

You can create any number of custom reports using content and user data from Policy & Procedure Management combined with the rich data display functionality of Microsoft Excel.

Important: It is not within the scope of this topic to teach how to use Excel features. The topic assumes that your knowledge of and skills in using Excel are at an intermediate or advanced level.

When you create an Excel report using Policy & Procedure Management data, you are, in effect, creating a template. If you are an administrator or have been assigned the template role, you can create the report as a Policy & Procedure Management template. Otherwise, you can create the template as a stand-alone Excel file.

Consider the following when creating reports with Excel:

Control

  • Reports (documents) created using the Policy & Procedure Management template are stored in the application's database with version control.
  • Policy & Procedure Management saves copies, or snapshots, of each report whenever both the content and status of the report document change.
  • You can set up email reminders for reading the report and reviewing it to ensure it is up to date.

Security and Access

  • Only users with assigned permissions can view or edit the reports. Document security options range from allowing the public to view the document down to only allowing administrators to view it.
  • The report can be viewed or edited from wherever Policy & Procedure Management users have Internet access. If a user needs to access or edit the report offline, the report can be downloaded and then imported back into Policy & Procedure Management so that any changes made offline are preserved.

Functionality

  • The full Excel feature set is available.
  • You can edit the Excel template in Policy & Procedure Management (in a browser window) or in undocked mode, where you edit the template within the Excel application for Windows®.

Notes:

  • When working in Excel in undocked mode, each time the worksheet is saved a copy of it is automatically uploaded to Policy & Procedure Management.
  • Editing an Excel document in a Policy & Procedure Management browser window may require a double-click to activate a cell or use shortcut keys, such as Ctrl+C and Ctrl+V.
  • Some Excel functionality, such as protected mode, may disable certain Policy & Procedure Management editing tools, such as inserting document properties.

Create an Excel report template

  1. Do one of the following:
    • Add an Excel template in Policy & Procedure Management and assign its properties. Be sure to select Excel Spreadsheet as the document type.
    • Create a new workbook in the Excel application.
  2. Generate a report, and in the template's Edit Document window or in the stand-alone workbook, copy and paste the exported report data into a template worksheet. (You may want to copy and paste only the report headings into the template.)

    Important: You may want to designate one worksheet for storing only the Policy & Procedure Management report data and then format the report on a different worksheet, with cell references to the report data. This will preserve the ability to quickly copy and paste Policy & Procedure Management report data.

  3. Add report content, such as headings, formulas, graphics, charts, and tables, and then save and close the template or stand-alone workbook.
  4. Save and close the template.

Generate an Excel report

Now that the template is in place, you can create the actual report.

  1. (Optional) If you created the report template in Policy & Procedure Management, create an Excel document as you normally would and assign the report template to the document.
  2. Run the report or reports containing the data the report template needs, and then copy and paste the data into the report.
  3. Save the report document (workbook). For a Policy & Procedure Management document, keep it in a draft status unless you want the report document reviewed and approved.
  4. The next time you want to run the report, do one of the following:
    • If you don't care about losing the data from the last time you ran the report, simply copy and paste the new data over the old.
    • If you created the report document in Policy & Procedure Management and are an administrator, set the document as approved. If you are not an administrator, send the document through the review and approval process until it is approved. In the approved document, create a new version, and then copy and paste the new data over the old.
    • If you created the report in stand-alone Excel, copy and paste the new data over the old, and then use Save As to save the workbook with a different file name.

Sample report

In the simple report below, the table was created from the data of a Document Assignments by Title report, and the data was copied directly into the table. Notice that Policy & Procedure Management sorts the rows alphabetically ascending—first by title, and then by assignment type. You can change which column is the first sorting key by clicking the down arrow in the column heading and then selecting a sort order. However, since the data is copied directly into this report to create it, you wouldn't want to change the column arrangement so you could copy and paste new data directly over the old.

Note: If you create a table from Policy & Procedure Management report data, you may need to adjust the table size each time you copy and paste new data into the report.

In the report below, the data was copied into a worksheet separate from the report worksheet, and cell references were used to populate the table. Notice that the column arrangement has been changed to show the key sorting column (Title) first. Also, the Version column has not been included, because the version number is already included with the document title. Changing the column arrangement does not disturb data entry, because the report data is in another place.