Custom Reports

About Custom Reports

Overview

Custom Reports is a built-in HTML Templating Engine using HTML Coding and external frameworks to allow Users to write their unique Reports using PCR-360 data and format them however your 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 was created within PCR-360 to empower users to customize their organization's customer experience. Your organization can make the Reports available on CustomerCenter or directly on specific backend application forms. PCR has structured the Custom Report creation process to best mimic the workflow needed to build the Report.

Additionally, Custom Reports allow Users to run a Report and save the output directly to the server so that that report can be shared with others without having to re-run the Report and not have to worry 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.

Creating a New Report

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

Custom Reports are also comprised 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 maintains a short term log of any Debug information, which is useful for refining the Logic of the Report 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

XML

/**
 * @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

XML

/**
 * @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

XML

/**
 * @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

XML

/**
 * @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.

PHP

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.

PHP

$user = [
     'isSysAdmin' => TRUE
     'isCoordinator' => FALSE
     'isCustomerCenterOnly' => FALSE
     'attributes' => [
		'USERS_RECID' => 494,
		'USERID': "demo",
        'CONTACTS_RECID' => 1
        'NAME' => 'Pcr Demo'
        'FIRST_NAME' => 'Pcr'
        'LAST_NAME' => 'Demo'
        'DEPT_HIERARCHY_RECID' => NULL
        'TENANTS_RECID' => 0
        'EMAIL' => 'demo@pcr.com'
    ]
]

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 will need to 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 Record 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 it's desired finished product. A User should be familiar with HTML programming and should first read the external documentation for Smarty and Bootstrap 4.0 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.

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 using the image to know the path to the file name.

Uploaded files can be set to have Availability to be displayed on other Report's Attachment Tabs by setting that File to All Reports when it is uploaded. Setting the File to be available for Only this Report will cause the File to not be displayed as such, even though it can still be accessed.

It should be warned, 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. Additionally, if a File is removed from the Attachment Tab, any other Reports that use that File will become broken, as the File will no longer exist on the Server.

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

Batch Processing Tab

The Batch Processing Tab displays information about Scheduled Batches, and allows a User to edit or delete pending 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.

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.

Reporting Available Reports grid example

Report Preview

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

When a Report is run, Users can share the Report through the 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 click on

to set a Schedule for a Report to run on.

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 be used to generate a Custom Invoice.

Invoice - Logic

Invoice - Logic

XML

$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

XML

<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