(2024.2) Custom Reports

About Custom Reports

Overview

Custom Reports is a built-in HTML Template Engine that uses HTML Coding and external frameworks to allow Users to write their unique Reports using PCR-360 data and format them however their Organization desires. Before writing a Report, a User should be familiar with HTML programming and should first read the external documentation for Smarty and Bootstrap 4.0.

Custom Reports empower users to customize their organization's customer experience. Your organization can make the Reports available on CustomerCenter, in a backend reports grid or directly on specific backend application grids and forms. PCR has structured the Custom Report creation process to mimic best the workflow needed to build the Report.

Additionally, Custom Reports allow Users to run a Report and save the output directly to the server. The saved report can be shared with others without re-running it or worrying about the data within the Report changing.

An example Custom Report is available at the bottom of this article.

Writing a Custom Report

The Admin > Reports > Custom Reports menu option brings the User to the Custom Reports grid. From this grid, a User can Add , Edit Selected , and Delete Selected Custom Reports. For convenience, PCR-360 also offers the ability to Import and Export Custom Reports for easy migration of Custom Reports from your Organization’s Test Systems to their Production Systems without having to re-create the Report manually.

Creating a New Report

A Custom Report is composed of multiple components. Every Custom Report has a unique Identifier, a Report Name, and Roles assigned to access it.

Custom Reports consist of Logic (for querying system Data), Parameters (for determining Report content), a Template for formatting the Report, Options (for adding a Report Title, Footer, and additional CSS), Attachments (for external files to be used within the Report) and details to define it for Batch Processing.

Custom Reports also maintain a short-term log of any Debug information, which is useful for refining the report's Logic and checking for possible errors within that Logic.

Logic Tab

Custom Reports utilizes the internal PCR-360 Custom Logic functionality that is also available to Custom Events, Custom API and Custom Validations.

Custom Report Functions

assign: Assigns a variable from the Logic to be used in the Template

Example

/**
 * @param string $name - name of the assigned variable in the Smarty Template
 * @param array $value - value of the assigned variable in the Smarty Template
 * @return void
 */
$this->assign('variableName', $variableValue);

loadReport: Loads another Report based on the Report Identifier

Example

/**
 * @param string $identifier - another report's identifier
 * @param array $params - array of parameters used to render the other report
 * @return string - the Rendered Report
 */
$report = $this->loadReport('customReportIdentifier', ['paramName' => $parameterValue]);

barcode: Generates an HTML IMG tag of a barcode

Example

/**
 * @param string $code - string text to encode
 * @return string - barcode IMG tag
 */
$barcodeImgTag = $this->barcode('test test');

addError: Forces the Report to render an Error Message instead of the Report

Example

/**
 * @param string $message - string error message to display instead of rendering report
 * @return void
 */
$this->addError('Some error in the Report Logic');

The Logic Tab is used for querying PCR-360 data. The Logic section has two parameters available for use in the Custom Logic: $params and $user.

$params

A key/value paired array of parameters that were passed into the Report are available within this array. Any changes to this array are also assigned to be accessed from the Smarty HTML Template.

if(isset($params["reportParameter"]) && $params["reportParameter"] === true)

$user

A key/value paired array of User information about the currently logged in User. The example below shows the options that are available within this array. This data enables Custom Reports to be specifically tailored to the User viewing the Report.

[
    "isSysAdmin" => true,
    "isCoordinator" => true,
    "isCustomerCenterOnly" => false,
    "attributes" => [
        "USERS_RECID" => "1204",
        "USERID" => "demo",
        "CONTACTS_RECID" => "215962",
        "NAME" => "demo PCR",
        "FIRST_NAME" => "demo",
        "LAST_NAME" => "PCR",
        "DEPT_HIERARCHY_RECID" => null,
        "TENANTS_RECID" => "0",
        "EMAIL" => "demo@mailinator.com"
    ],
    "permittedGlas" => [
        "29",
        "30",
        "31"
    ]
]

Debug Tab

Only data sent to the debug() function is displayed in the Debug section. Debug information only exists for 24 hours before it is automatically cleared out by the system. For more information on the debug() function, please see our article on Syntax.

Parameters Tab

A User can click the [Add] button to define each Parameter for the Report.

Parameters require a Name to be accessed within the Report Logic, as well as a Description to allow the User to know what they are entering. Parameters must also be defined as a specific Input Type, much like User-Defined Fields are. Parameters can also be assigned an Order value to determine the order in which they are presented to be populated by the Report generator. Finally, all Parameters have a Display checkbox to determine if that Parameter value will be shown in the Report's Preview.

Input Types accepted:

- Checkbox
  - This is the only type of Input that can not be defined as required, as checkboxes are either Checked or Not Checked.
- Currency
- Date
- Date/Time
- Decimal
- Drop Down
  - When a Drop-down Input is assigned, the User must define the Value and Text for each option within the Drop-down menu.
- Number
- Picker
  - When an Input is assigned to the Picker Type, the User must also define what type of Picker it is. Almost all types of Records within PCR-360 can be accessed by a Picker
- Text

When a User runs a Custom Report, the related Parameters will be available to be set in the panel on the left side of the screen.

Template Tab

The Template Tab is where the bulk of the Report is made. The User can write HTML code here to access the information obtained in the Logic Tab, and format it into its desired finished product. A User should be familiar with HTML programming and should first read the external documentation for Smarty and Bootstrap 5.1 before attempting to develop the Template for a Report.

Options Tab

The Options Tab is used for creating the Report's Title (either a fixed Title, or generated based on information obtained when the Report was run, with a 100-character limit), as well as adding in additional Footer and other general CSS formatting. The Report's Footer is limited to 500 characters or less. A User can also define specific options for when the Report is requested as a PDF file, such as Header and Footer details. For assistance in using these PDF settings, please review the Snappy wkHTMLtoPDF Documentation .

PDF Options Example:

PDF Options

{
	"footer-center": "{$pdfFooter}",
    "footer-right" : "[page]/[topage]",
    "footer-spacing" : 1
}

Logic

$this->assign('pdfFooter', 'This is the FOOTER');

PDF Footer Result

Attachments Tab

Before Files can be uploaded to a Custom Report, the Custom Report must first be saved.

Unlike other Attachment Tabs within PCR-360, files Attached to a Custom Report are uploaded to the /media/custom/ directory on the Server. This makes ALL Attachments to ANY Custom Report available to all other Custom Reports, as long as the User knows the path to the file name.

Attachments can be uploaded from the User’s PC using the Upload File(s) button, or from among any file previously loaded onto the /media/custom/ directory by using Attach from Directory button.

When using Users have the option to Attach a file from the Server, Download the file from the Server to Delete the file from the Server. When a File is Deleted from the Server, it will break any Report that is using that file.

Warning: because all files are easily accessed using the same basic File Path, Uploading a File with the same name as a previously uploaded File will overwrite that File on the Server. When a Custom Report is exported, only Attachments on the Report will be exported.

Note: It is highly recommended NOT to attach JavaScript files to Custom Reports as that can be a severe security risk

Batch Processing Tab

The Batch Processing Tab displays information about Scheduled Batches, and allows a User to , Edit Selected , and Delete Selected Batches. For more information on the Batch Process, please see the section of this article for Automatically creating the Report.

Updating an Existing Report

To edit a Custom Report, a User simply needs to select the desired Report from the Custom Reports grid and click on the [Edit Selected] button. This will open the Report and the User can change any Logic, Parameters, Options, Attachments Batches, or the Template as needed.

Importing/Exporting a Custom Report

For convenience, PCR-360 has built in functionality to Import and Export Custom Reports. This is particularly useful when building a report in a Test Environment, and then loading it into your Organization’s Production Environment without building the report fresh.

Exporting a Custom Report

To Export a Custom Report, the User must select the desired report in the Admin > Reports > Custom Reports grid. Then the User can click on the button to save a JSON Source File of the report onto their local computer.

Importing a Custom Report

To Import a Custom Report, the User must click on the button on the Admin > Reports > Custom Reports grid. The User can then upload a JSON Source File from their local computer into PCR-360.

Linking a Custom Report

Reports can be linked to Data-Entry forms or Data Grids. When opening a Custom Report that has been linked a single parameter called “recid” is passed. To process this parameter the report requires that the “recid” parameter is added to the Parameters tab in the Report Builder.

Running a Custom Report

Back Office generating the Report

Back office users can generate Custom Reports from multiple places in PCR-360: Admin, the Reporting menu, CustomerCenter, Form → Reports menus, and Grid → Reports menus.

Admin: Custom Reports

From the Custom Reports grid Users can click on [Open Report] to generate a Report.

Reporting: Available Reports

Users can generate new Reports from the Main > Reporting > Custom Reports > Available Reports menu option. However, when accessed from the Main menu, the Grid is limited based on the logged in User's assigned Roles, based on what Reports are assigned to those Roles.

To view a Report, a User must select the Report from the grid and click on [View Report]. For User convenience, a User can also [Bookmark Selected] Reports to access more quickly through the PCR-360 Footer.

Report Preview

On the Report Preview page, the User must fill in any required parameters and then click on the [Open Report] button to open the report in a new tab. After entering the required parameters, a preview of the report may be run by clicking the "Refresh" button.

When a Report is run, Users can share the Report using the functions provided.

	The [Copy Link] button will copy a link to the Report, including the parameters, to the User's clipboard so that they can share it as a Web URL.
	The [Save a Copy] button will save the Report so that it becomes available from the Saved Reports grid.
	The [Download PDF] button will download a PDF Copy of the Report to the User's local system.

Saved Reports

Alternatively, Users can view previously saved Reports from the Main > Reporting > Custom Reports > Saved Reports menu option.

From this grid, the User can click on [View Report] to open the saved Report directly or click on [Delete Selected] to delete the saved Report.

Admin Users can also click on [Bulk Assign] to have a Report display for other Users.

CustomerCenter providing the Report

Custom Reports are available in CustomerCenter. Just like the Main > Reporting > Custom Reports > Available Reports menu option, the page is limited based on the logged in User's assigned Roles, based on what Reports are assigned to those Roles.

Automatically running the Report

From the Custom Reports grid, Users can open a Report, and click the button on the “Batch Processing” tab of that report.

Report Batch Process

The Report Batch Process allows an Admin User to apply SQL Filters, define a Notification Message when the Report Batch is generated, and set a specific Schedule for the Report to run automatically.

When the Batch executes, each Record from the User defined SQL Filter Query will be fed into the Report's Logic as the parameters. Each unique row of the Filter Query will generate a particular Report. The CONTACTS_RECID of each record will determine which Contacts are assigned to the individual Reports.

Users can define a custom Subject Line for an automatic notification, as well as provide a message for the Notification. Users can also opt to attach the PDF of the Report to the Notification automatically.

Users can have a Batch run Immediately or set a Frequency for the Report to execute on, as well as set a date for when to run the Report next.

The Frequency can be set for:

Run Once
Daily
Weekdays
Weekly
Monthly
Quarterly
Yearly

Example Custom Report: Invoice

Below is an example of how Custom Reports can generate a Custom Invoice.

Invoice - Logic

Invoice - Logic

$invoice = $this->query(<<<SQL
    SELECT I.*, B.BILL_DATE, INV_UDF.VARCHAR_VALUE as INV_NUMBER, V_GLA.ACCOUNTNUMBER, GLA.DESCRIPTION
    FROM INVOICES I
    INNER JOIN BILLS B ON B.RECID = I.BILLS_RECID
    INNER JOIN V_GLA_COMP_COMB_ADMIN V_GLA ON I.GLA_RECID = V_GLA.RECID
    INNER JOIN GLA ON GLA.RECID = I.GLA_RECID
    LEFT JOIN USER_DEFINED_FIELDS_VALS INV_UDF ON INV_UDF.TABLE_NAME = 'GLA'
         AND INV_UDF.TABLE_RECID = GLA.RECID
         AND INV_UDF.UDF_RECID = 261
    WHERE I.INVOICE_NUMBER = :invoice
SQL, [':invoice' => $params['invoice']]);
$invoice = $invoice[0];

$this->assign('invoice', $invoice);
$this->assign('fmtBillDate', (new DateTime($invoice['BILL_DATE']))->format('M y'));
$this->assign('gla', $invoice['ACCOUNTNUMBER']);
$this->assign('invoiceTotal', number_format($invoice['BILLED_AMOUNT'], 2));
$this->assign('invoiceDate', (new DateTime($invoice['INVOICE_DATE']))->format('m/d/Y'));

$billingAddress = $this->query(<<<SQL
    SELECT * FROM GLA_ADDRESSES GA
    INNER JOIN ADDRESSES ADDR
    ON GA.ADDRESSES_RECID = ADDR.RECID
    INNER JOIN LISTS L
    ON L.RECID = GA.ADDRESS_TYPE_LISTS_RECID
    WHERE GA.GLA_RECID = :glaRecid
    AND L.CODE = 'BILLING'
SQL, [':glaRecid' => $invoice['GLA_RECID']]);
$billingAddress = $billingAddress[0];
$this->assign('billingAddress', $billingAddress);

$billingContact = $this->query(<<<SQL
    SELECT * FROM GLA_CONTACTS GC
    INNER JOIN CONTACTS C
    ON GC.CONTACTS_RECID = C.RECID
    WHERE GC.GLA_RECID = :glaRecid
    AND GC.PRIMARY = 1
SQL, [':glaRecid' => $invoice['GLA_RECID']]);
$billingContact = $billingContact[0];
$this->assign('billingContact', $billingContact);

$bdTableSuffix = '_' . (new DateTime($invoice['BILL_DATE']))->format('ym');
$tableExists = $this->query("SHOW TABLES LIKE 'BILL_DETAILS{$bdTableSuffix}'");
$this->debug($tableExists);
$bdTable = "BILL_DETAILS" . (empty($tableExists) ? '' : $bdTableSuffix);
$this->debug(<<<SQL
    SELECT BD.*, CONCAT('$',FORMAT(BD.COST,2,'en_US')) as FMT_COST
    FROM $bdTable BD
    WHERE INVOICES_RECID = :invoiceRecid
    LIMIT 100
SQL);

$billDetails = $this->query(<<<SQL
    SELECT BD.*, CONCAT('$',FORMAT(BD.COST,2,'en_US')) as FMT_COST
    FROM $bdTable BD
    WHERE INVOICES_RECID = :invoiceRecid
    LIMIT 100
SQL, [':invoiceRecid' => $invoice['RECID']]);
$this->assign('billDetails', $billDetails);

Invoice - Parameters

Invoice - Template

<div class="header">
    <div class="row">
    	<div class="col"><img src="/media/custom/logo.jpg"></div>
    	<div class="col text-right align-middle">
        	<h5>0000 Nowhere Street<br>
            	Nowhere Village, No State 00000</h5>
    	</div>
    </div>
    <div class="hdr-info row">
        <div class="col">
            <p><span class="bold">To Attention:</span><br>
                Account Number: {$gla}<br>
                {$billingContact.FIRST_NAME} {$billingContact.LAST_NAME}<br>
                {$billingAddress.STREET_ADDRESS} {$billingAddress.ADDRESS2}<br>
                {$billingAddress.CITY}, {$billingAddress.STATE} {$billingAddress.ZIP_CODE}
            </p>
        </div>
        <div class="col bold">
            <table>
                <tr><td>Account Number:</td><td>{$gla}</td></tr>
                <tr><td>Invoice Number:</td><td>{$invoice.INV_NUMBER}</td></tr>
                <tr><td>Invoice Period:</td><td>{$fmtBillDate}</td></tr>
                <tr><td></td><td></td></tr>
            </table>
        </div>
    </div>
</div>

<table class="table table-sm">
	<thead class="thead-light">
    	<tr>
      		<th scope="col">Invoice Date</th>
      		<th scope="col">Invoice Number</th>
        	<th scope="col">Invoice Amount</th>
    	</tr>
        <tr>
            <td>{$invoiceDate}</td>
            <td>{$params.invoice}</td>
            <td class="text-right">${$invoiceTotal}</td>
	</thead>
	<tbody>
        <tr><td colspan="3">
            
            <table class="table table-striped">
                <thead>
                    <tr><th>Charge Code</th>
                        <th>Charge Description</th>
                        <th>Owner</th>
                        <th>Cost</th>
                    </tr>
                </thead>
                <tbody>
                	{foreach $billDetails as $bd}
                    <tr class="">
                        <td>{$bd.CHRG_CATALOG_NAME|escape}</td>
                        <td>{$bd.CHRG_CATALOG_DESCRIPTION|escape}</td>
                        <td>{$bd.DEPT_HIERARCHY_PATH|escape}</td>
                        <td class="text-right">{$bd.FMT_COST|escape}</td>
                    </tr>
                    {foreachelse}
                    <tr><td colspan="4">No Transactions Found</td></tr>
                    {/foreach}
                </tbody>
            </table>
            
        </td></tr>
    </tbody>
    <tfoot class="thead-light bold">
        <tr><td></td>
            <td>Total Amount Due:</td>
            <td class="text-right">${$invoiceTotal}</td>
        </tr>
    </tfoot>
</table>


<div class="text-center">
    <h6 class="text-danger">Please include a copy of this invoice with payment or reference this invoice # on your check.</h6>
    <p>Amount Due - Upon Receipt</p>
    <p class="bold">Remit To: Office of the Black Voild<br>
        0000 Nowhere Street, Nowhere Village, No State 00000<br>
    	State ID # XXXX00000 00<br>
    	For All Billing Inquiries: 555-555-5555<br>
        <a href="mailto:noWhere@voidMail.com">noWhere@voidMail.com</a>
    </p>
</div>

Invoice - Options