Reports

Locating the Reports feature

Anyone in a Role with the View Shared Reports privilege can edit Reports by opening the ADMIN menu and clicking on Reports:

How to Set Up Detail Reports

PowerClerk offers detailed reporting for administrators via a self-service report editor. Using the report editor, the administrator can define the program data which should be contained in a report. Reports are uniquely named by the administrator and fields are added onto a report design surface, like the Form editor.

When new Data Fields are created in the Form editor, a field name is assigned to that field by the administrator.
Field names are used to create reports in the Report setup user interface. Data Fields are added to the “Report Columns” section by either checking the box next to a specific data field or dragging-and-dropping a specific data field row onto the right hand panel. When compound fields such as Addresses are selected (or dragged-and-dropped onto the report), each element of the field appears individually on the report. Alternatively, the user may place individual elements on the report, such as Installer Company, as shown in the figure above.

The report editor includes a Search function and Filters (to aid administrators in locating fields to add to the report).

The filter area on the left part of the screen includes:

• Hidden Fields: to remove/add archived fields and/or fields not currently referenced on a form.
• Data Field Types: to filter by type of data field.
  Form Elements are related to specific types; for example, Boolean (T/F) fields are associated with check boxes, Choice fields are associated with Drop-down List and Multiple-Choice form field types, etc. Each unique Data Field within Form Elements comprised of more than one field (i.e., a Data Field Group) has its own type as well (e.g., First Name and Last Name, which comprise the Form Element Name, are each a String field).
• Data Field Group Types: grouped data fields, created collectively when their Form Element is added to a Form.
  Examples include the Contact and Address Form Elements as well as Special elements like the PV System and Energy Storage System.
• Other Types: reportable Fields not directly related to a Form Element.
  These include Built-In fields as well as Fields that report on the meta data affiliated with Attachment Fields, Communications and Deadlines: 
  • Built-In: Project metadata such as Program, Project Landing Page URL, Project Owner information, Assignee information, Queue Position and Timestamps and Durations relating to Project Statuses.
  • Attachment data: eSignature Status, File Name, Upload Timestamp, and Status (i.e., Approved or Rejected) for a given Attachment Field
  • Communication data: First Send Timestamp, Message Status and Template Modified for all instances of a given Communication Template
  • Deadline: due date for pending instances of a given Deadline

Please note: If you do not see the Data Fields you expect, more than likely the configured filters are hiding those Data Fields, or you need to check one of the two options:

1. Show fields not referenced on current forms
2. Show archived fields

Limiting a Detail Report

Sometimes an administrator may want to report only on projects from a particular timeframe, such as the previous calendar month or a particular year. Administrators also have the option to limit a report only to projects that are available to a certain Access Group. This type of filtered report is useful in programs that use Access Groups to review and report on projects assigned to specific departments or operating companies.
 
Both types of filters can be set in the Edit Report screen. To limit the Report to projects available to an Access Group, select the name of the group in the Options section at the top of the Edit Detail Report screen. This section only appears if Access Groups have been set up.
 
To limit a Detail Report by timeframe, add at least one Date Field to the report by clicking or dragging its name to the right-hand column. This field can be a Data Field from a Form, or a piece of metadata found in the Built-In category under Other.
 
In the right-hand column, expand the Date field’s tile and toggle on Filter this column?. Select the timeframe to include. Options include Last 30 Days, Previous Calendar Month, Current Week/Month/Year, or a Custom Date Range. Click the question mark symbol for more details on what each timeframe includes.
 
A black filter icon will appear before the name of any Report Column that has been filtered in this way.
 

Roll-Up Reports

Roll-Up Reports provides administrators the ability to define reports which aggregate, average, and count values in program Data Fields. Using the familiar Report editor user interface, the administrator can define Roll-Up Reports including, but not limited to the following:

• Count of projects by City, State, Zip Code, or County
• Count of projects by Current Status
• Count of projects by Company
• Count of projects by Account Number (could be used to check for duplicates)
• Sum of PV System Nameplate Capacity by City, State, Zip Code, or County
• Average PV System Nameplate Capacity by City, State, Zip Code, County, etc.

Similar to Detail Reports, Roll-Up Reports will download in .csv format and are easily then converted into other common file types including .xlsx and .pdf.

Multi-Instance Reports

Multi-Instance Reports allow administrators to report across key project audits, like communication, status and attachment histories:

The following multi-instance fields in PowerClerk can be reported upon to collate this data outside of PowerClerk to increase your reporting capabilities and provide new avenues for Workflow analyses and program-wide optimizations:

• Attachment History: Report current and historical attachment metadata such as attachment name, uploader and approval status.
• Communication History: Report Communication Template names sent per project.
• Project Notes: Report Project Notes entered by administrative staff.
• Status History: Report data related to project status changes and by whom the change was initiated (see example shown below).

 
Please note: Multi-instance Reports also can be scheduled, shared, or integrated within Excel the same way regular reports can be handled.

Sharing Reports

When creating a Report in PowerClerk you can decide to share your Report with other users in Roles with the “View Shared Reports” privilege with the Share Report button and unshare the Report as well. To ease maintaining a shared Report in a group, additional PowerClerk user accounts can be granted save/edit access to a shared Report with button Grant Report Access:
 

 
Using this button will allow to enter the email address of another PowerClerk user account to grant edit/save access to the Report:
 
The Grantee of such Report access will see on their Shared With You tab the editable Reports available to them in column “Editable” as shown below:

 
Report History
Previously run reports will appear in the Report History for 30 days. Each historical report will display the date and time of its creation along with a link to download the report in Excel. If the report is shared and has run at least once in the past 30 days, the top of this page will include a ‘Download Link’ for use in integrations- see details below.

Integrate scheduled Reports with Microsoft Excel and Power BI

A report that is generated by a Scheduled Automation Trigger and associated “Run Report” Action Rule can be refreshed directly in an Excel workbook without having to log on to PowerClerk by referencing the report’s “Download Link” provided in the Report History:

 
To connect directly to the latest iteration of the report within Excel click on the menu item Data >> From Web and paste the download link in the subsequent dialog as shown in Figure 10 and 10b:
 

 
When Excel prompts you for a User name and Password enter your PowerClerk credentials. These will be stored on your Windows system:
 

 
Please note: The connected Excel report may be shared, however, in order to refresh the report’s PowerClerk connection, the recipient must also have access to the shared PowerClerk report and provide their PowerClerk credentials within Excel to authenticate.
 
Synchronization of scheduled Reports within Excel
Once you have established the direct connection to the PowerClerk report via the “Download Link” option described above, you can refresh your Excel workbook by navigating to menu item “Data” and clicking on “Refresh All”. Please note that only the latest iteration of the PowerClerk report’s data is being retrieved. A refresh in Excel *does not* re-run the PowerClerk report, which is why this functionality is particularly useful for scheduled Reports where PowerClerk automatically ran the report.
 
Accessing the Report via the API
To retrieve a report programmatically, the report’s download link can be used as the API endpoint. Basic authentication (username and password) is required to access the report.

Exporting Reports via SFTP

A report can be set up to export from PowerClerk to an SFTP site on a daily, weekly or monthly cadence using the following process:

Step 1: Establish an SFTP site to receive the report file(s).

Please note that the SFTP site must be owned and managed outside of PowerClerk. Unsecure FTP servers are not supported; the receiving site must be an SFTP transfer server (such as FileZilla, Files.com, etc). PowerClerk will securely drop the Report file into the SFTP site folder at the desired cadence, but Clean Power Research does not manage the folder in any other way.

Step 2: Create the Report in your Program.

If you haven’t already, create the Report you want to send via SFTP. Share the report.

Step 3: Communicate your transfer info and desired cadence with CPR Support.

Log into the PowerClerk Support Center and submit a ticket request with the following information:

• Transfer Cadence: Options are: Daily, Weekly or Monthly. If daily, please choose a time of day. If weekly, please choose the day of the week and time of day. If monthly, please choose the day of the month.
• Timestamp or No Timestamp: You have the option to have each exported report include a timestamp appended to the filename.
• Support Email Alias: This is the point of contact for the SFTP location. They will receive any error messages.
• Username / Password: username and password associated with the location where the files are being sent. (For example, if the files are to be sent to a files.com host, please set up an account for PowerClerk/CPR to utilize). It may be best to provide this sensitive information via a secure OneDrive share file: Include the link to the file in your ticket.
• File Suffix: what type of file is expected to export (currently, PowerClerk only supports .csv)
• HostName: the overarching host to receive the file (example, if a report is being sent to a Files.Com host, the overarching host would be app.files.com)
• Path: this is the exact path the file should be sent to after the .com, including the folder(s). You can send the entire URL of the folder location, and the CPR PowerClerk Support Team will grab the path.
• Port: SFTPs typically use port 22.

Step 4: CPR will set up a scheduled automation to run your report.

A CPR-Admin will add an automation trigger to run your report at your desired cadence and will set up the SFTP destination.

Step 5: Tell your Report to go to the SFTP site.
Once the automation is live, go to your Report, “Edit Report” and under “Options” select “SFTP Transfer.”

Now the Report will automatically run at the specified cadence and drop into your SFTP site. CPR does not manage the files after they leave PowerClerk.

Cross-Program Reports

Cross Program Reporting allows a user to run reports across multiple PowerClerk programs within one agency. In order to set up programs for cross-program reporting, please contact Clean Power Research to set up and enable this feature. Please state the program names. In addition, the roles intended to run this kind of report need to have the Admin Privilege Edit Cross-Program Report Mapping enabled.

The next step in this process is to configure Cross-Program Report mapping. Any role with the “Edit Cross-Program Report Mapping” admin permission can access the Cross-Program Report mapping dialog. This relates the data fields from one program to the other programs as shown in the figure below:

Click the Save button to finalize the mapping of data fields.

Please note: To run a Cross-Program Report, the user must have access to the other programs’ data. This can be achieved by either having an Admin Role in all included programs or by having a Role with broad access and being granted access to the Report.

Handling Sensitive Data in Reports

When creating a Report that includes sensitive data fields, those fields must have the box “Reveal plaintext value of sensitive data” toggled on. To do so, click on the arrow next to the associated data field under the section “Report Columns” to expose additional settings.
 

 

Figure 13: Revealing Sensitive Data

 
Once downloaded, PowerClerk will create a password-protected and encrypted (AES-256) zip file; this file may require additional file decryption software to open.
 
Please note: Windows does not natively support AES-256 encryption. It may be necessary to utilize another method to open the file; PowerClerk recommends 7-Zip.
 
When accessing a Report with sensitive data, the user must be in a role with the “View Sensitive Data” permission enabled, as well as have Multi-Factor Authentication (MFA) enabled.
 
The “View Sensitive Data” permission is enabled by a CPR-Admin so if you would like this turned on in your program, please reach out to support@powerclerk.com

Viewing Report Failures

You can now view report failures by navigating to the ADMIN menu and clicking on Operation Status. From there, you will be able to view the tab “Failed Reports”.
 

 

Figure 14: Operation Status

 
This view will provide you with the following information for each failed report:

• Report Start Timestamp
• Report Name
• Reason for report failure
• Whether the report has been shared and is available to others
• Report type

Maximum Number of Columns Allowed in a Report

The maximum number of columns a report can contain is 500. However, PowerClerk’s multi-instance fields, such as the PV System Specifications, can bypass this. When the number is accidentally exceeded, the report will fail. You can confirm this by navigating to Admin >> Operation Status >> Failed Reports and reviewing the error message associated with the failed report.
 

 

Figure 15: Operation Status Error Message

 
For the failed report to run, the multi-instance fields can be removed, or split into more than one report, to decrease the total number of columns.

Video guides

Setting up Roll-up Reports

The following video demonstrates how to setup a Roll-up Report:

FAQs

Q: How can I suppress a status from being reported?

A: To do so please navigate to a project currently in the status you would like to suppress in your Reports and click on "View Status History" as shown below:

Expand the status row for the status you wish to suppress reporting for this single project by clicking button Change Status Reporting and select “Do not report” in the subsequent dialogue and acknowledge your selection:

Q: Can I unshare a report created by a different PowerClerk user?

A: Yes, you can unshare someone else's shared report if your role has privileges View Shared Reports and Edit Users activated. Please note that privilege Edit Users needs to be activated by a Clean Power Research administrator.

Have additional questions? Contact us to nominate your FAQ and help others find answers to your own questions concerning this feature.

Create A Support Ticket

Not finding your answer here?  Submit a question to our support team at the PowerClerk Ticket System and leverage the PowerClerk team’s expertise.