You are here: Topics by User Interface Area > For Document Control Administrators > Reports > Creating Custom Reports with Excel

Creating Custom Reports with Excel

You can create any number of custom reports using document and user data from PolicyTech 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.

The Excel Report Template

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

We recommend creating the template inside PolicyTech for the following reasons:

Control

Reports (documents) created using the PolicyTech template are stored in the PolicyTech database with version control.
PolicyTech 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 document control administrators to view it.
The report can be viewed or edited from wherever PolicyTech 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 PolicyTech so that any changes made offline are preserved.

Functionality

The full Excel feature set is available.
You can edit the Excel template in PolicyTech (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 PolicyTech.
Editing an Excel document in a PolicyTech 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 PolicyTech editing tools, such as inserting document properties.

Creating an Excel Report Template

Do one of the following:

Add an Excel template in PolicyTech and assign its properties. Be sure to select Excel Spreadsheet as the document type. (See Creating a Template for detailed instructions.)

Note: If you prefer to work with Excel undocked from the PolicyTech browser window, see Working with Word or Excel Undocked.

Create a new workbook in the Excel application.

Generate one or more PolicyTech management reports with Microsoft Excel Raw selected as the output format (see Management Reports).

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 PolicyTech 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 PolicyTech report data.

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

Generating an Excel Report

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

If you created the report template in PolicyTech, create an Excel document as you normally would (see Creating and Working with Draft Documents) and assign the report template to the document. If not, skip this step.
Run the report or reports containing the data the report template needs, and then copy and paste the data into the report.
Save the report document (workbook).

Note about PolicyTech documents: Unless you want the report document reviewed and approved, you should probably keep it in draft status.

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.
To preserve the report with its previous data, do one of the following:

If you created the report document in PolicyTech and are a document control administrator (DCA), set the document as approved (see Setting a Document as Approved). If you're not a DCA, send the document through the review and approval process until it is approved (see Submitting a Document to Writers, Reviewers, or Approvers). In the approved document, create a new version (see Doing a Periodic Review and Creating 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 Reports

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 PolicyTech 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 PolicyTech 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.

The report below includes a pivot table created from the same data as the previous two reports.