= Purpose= The ''Bright Pattern Contact Center Custom Reporting Tutorial'' explains how you can create custom report templates for your Bright Pattern Contact Center solution. The first two sections of this tutorial describe how to configure the TIBCO Jaspersoft Studio application to customize report templates. The last section describes an alternative way to export custom data sets from the Reporting Database in CSV format using BPXML templates. [[File:Jaspersoft-studio.png|800px|thumbnail|center|Customizing report templates in Jaspersoft Studio]] == Additional Information == The following resources are recommended as a starting point for learning the Jasper Reports syntax and the Jaspersoft Studio visual editor: http://community.jaspersoft.com/wiki/jaspersoft-studio-tutorials-archive '''Note:''' Jaspersoft has two different tools for report development: iReport Designer and Jaspersoft Studio. Only Jaspersoft Studio is supported as the tool for the creation of custom report templates for your Bright Pattern Contact Center solution. = Audience= The ''Bright Pattern Contact Center Custom Reporting Tutorial'' is intended primarily for professionals responsible for the design, development, and testing of custom applications and reports in your contact center. Readers are expected to be have experience with relational databases and Jaspersoft Studio, as well as a solid understanding of contact center operations and resources that are involved in such operations. = Prerequisites= Download and install the latest version of TIBCO [http://community.jaspersoft.com/ Jaspersoft Studio]. After installation is finished, create a new workspace that will be used for your custom report templates. [[File:Jaspersoft-Workspace.png|800px|thumbnail|center|Creating a new workspace in Jaspersoft Studio]] = Downloading Libraries and Localization Resources= To reuse Bright Pattern report templates in Jaspersoft Studio, follow these steps: # Download the [http://www.brightpattern.com/assets/report-template-libs.zip report-template-libs.zip] package containing libraries and localization resources that facilitate the reuse of Bright Pattern Contact Center report templates in Jaspersoft Studio.

# Extract the files to your local computer, preserving the internal folder structure. = Importing Libraries= After you have downloaded libraries and localization resources, the next step is to import the libraries. Note that if you have just installed Jaspersoft Studio on your computer, your workspace may not contain any folders or projects in the ''Project Explorer'' tab, and you may have to add a new folder called ''MyReports'' before you can proceed with the following steps. == Procedure == === Step 1: Add libraries to ''MyReports'' project === # In Jaspersoft Studio, navigate to the ''Project'' tab in the left-hand part of the screen.

# Select '''MyReports'''.

# Right-click '''MyReports''' and then select '''New > Folder'''.

[[File:Jaspersoft-MyProjects-New-Folder.png|650px|thumbnail|center|How to create a new folder]]

# In the dialog box that pops up, select parent folder '''MyReports''' and name the new folder ''libs''. This is the folder that will contain your libraries.

# Click '''Finish'''.

[[File:Name-New-Folder.png|650px|thumbnail|center|Project > MyReports > New > Folder]] === Step 2: Import files === # Right-click the '''libs''' folder and select '''Import'''.

[[File:Libs-Import.png|450px|thumbnail|center|libs > Import]]

# In the dialog window that pops up, select '''File System'''.

# Click '''Next'''.

[[File:Libs-Import-File-System.png|450px|thumbnail|center|libs > Import > File System]] === Step 3: Add files from the report template library for use in Jaspersoft Studio === # Click '''Browse''' to find the ''report-template-libs'' folder of the package that you previously downloaded and extracted.

# Select the ''report-template-libs'' folder and click '''OK'''.

# Select these files:

## '''report-formatter.jar''' (to provide scriplet functions for output formatting)

## '''mariadb-java-client-1.1.6.jar''' (for database connection)

## '''icu4j-4.8.1.jar''' (for Unicode and localization support)

## '''report-fonts.jar''' (for Unicode fonts)

# Click '''Finish'''.

[[File:Import-File-System-Finish.png|650px|thumbnail|center|Libs > Import > File System > report-template-libs]] === Step 4: Build paths to the files within the project folder === # Select these .''jar'' files in the Project Explorer. You can select all four as a batch.

# Right-click the selection and then select '''Build Path > Add to Build Path'''. Doing so will add a path for each .''jar'' file to the root folder of the project.

[[File:Build-Paths.png|650px|thumbnail|center|Build Path > Add to Build Path]] === Step 5: Add remaining ''report-template-libs'' files to the project === Add the files ''report_logo_bar.png'' and ''report_logo.png'' to the root folder of the project by following step 2 and step 3 of this procedure. Instead of selecting the .''jar'' files, select the .''png'' files from the ''report-template-libs'' folder. = Importing Localization Resources= Localization resources are imported to Jaspersoft Studio so that reports are displayed in multiple languages. These resources are found in the ''com'' subfolder of the ''report-template-libs'' folder in the package that you previously downloaded and extracted. == How to Import == To import the localization resources, follow these steps. === Step 1: In the ''MyReports'' project, create a folder called ''resources'' === * In Jaspersoft Studio, navigate to the ''Project'' tab in the left-hand part of the screen.

* Select '''MyReports'''.

* Right-click ''MyReports'' and then select '''New > Folder'''.

* In the dialog box that pops up, select parent folder '''MyReports''' and name the new folder ''resources''. This is the folder that will contain your localization resources.

* Click '''Finish'''. === Step 2: Import files === * Right-click the '''resources''' folder and select '''Import…'''.

* In the dialog window that pops up, select '''File System'''.

* Click '''Next'''.

* Open the ''report-template-libs'' folder of the previously downloaded and extracted package and click '''OK'''.

* Select the '''com''' subfolder and click '''Finish'''. [[File:reporting-db-spec-image3.png|thumb|600px|center|]] ''MyReports'' now contains folder ''resources'' and subfolder ''com''. [[File:Resources-Com.png|350px|thumbnail|center|MyReports > Resources > Com]] === Step 3: Build paths to the files within the project folder === Right-click '''resources''' and select '''Build Path > Use As Source Folder'''. Doing so makes ''resources'' a source folder available at the top of ''MyReports''. [[File:Build-Path-Use-As.png|450px|thumbnail|center|Making ''resources'' a source folder within the project folder]] = Downloading Report Templates= Bright Pattern's Contact Center Administrator application provides a number of report templates. Report templates provide an easy launch point to create custom reports in Jaspersoft Studio. Note that report templates are available in either ''.jrxml'' or ''.bpxml'' format. Only CSV reports have the ''.bpxml'' file extension. The capability to export a large volume of data as a CSV report (''.bpxml'') is currently provided for two report types only: Agent Activity (CSV) and Call Detail (CSV). == How to Download Report Templates == # From Contact Center Administrator's ''Configuration'' menu, navigate to ''Reporting > [[#topic_contact-center-administrator-guide/ReportTemplates|Report Templates]]''. # Select the template(s) that you wish to modify.

# In the Properties tab, beside the report template file name, click '''download'''.

[[File:Report-Templates.png|800px|thumbnail|center|Contact Center Administrator > Reporting > Report Templates]]

# When the file is opened in Jaspersoft Studio, the report template appears as shown.

[[File:Report-Template-Jaspersoft.png|800px|thumbnail|center|A .jrxml report template file in Jaspersoft Studio]] = Importing and Modifying Report Templates= To work with and customize report templates in ''.jrxml'' format, you must first import them to Jaspersoft Studio using the following steps. == How to Import Report Templates == === Step 1: Create a folder to store your reports === # In Jaspersoft Studio, navigate to the ''Project'' tab in the left-hand part of the screen.

# Select '''MyReports'''.

# Right-click '''MyReports''' and then select '''New > Folder'''.

# In the dialog box that pops up, select parent folder '''MyReports''' and name the new folder ''reports''. This is the folder that will contain your report templates.

# Click '''Finish'''. === Step 2: Import report template files to the new ''reports'' folder === # Right-click the '''reports''' folder and select '''Import'''.

# In the dialog window that pops up, select '''File System'''.

# Click '''Next'''.

# Choose the folder where you have stored the report template files that you previously downloaded from the Contact Center Administrator application. These files will have ''.jrxml'' file extensions in their names.

# Select these files and click '''Finish'''. [[File:Import-Report-Templates.png|450px|thumbnail|center|Select the files to add to the reports folder]] == Example: How to Modify .jrxml Report Templates == Once you have downloaded a report template, you can use the visual editor of the Jaspersoft Studio to import and modify ''.jrxml'' report templates (e.g., remove some of the existing metrics or add new ones). Report templates in the ''.jrxml'' format that are imported to Jaspersoft Studio will look as shown: [[File:reporting-db-spec-image5.png|thumb|800px|center|]] What follows is an example of how one might modify a ''.jrxml'' report template. In this example, the column ''Case'' is removed from the out-of-the-box [[#topic_reporting-reference-guide/AgentActivityReport|Agent Activity Report]] and the formatting/visual appearance of the report is changed. === Step 1: Open file agent_activity.jrxml === # In Jaspersoft Studio, navigate to ''My Reports > reports''.

# Find and double-click the ''agent_activity.jrxml'' file to open it. === Step 2: Select the elements you wish you change === # Select elements ''$R{col_case_id}'' in the group header frame, and ''$F{case_number}'' in the detail frame of the last column. You can select both elements at the same time.

# Right-click the selected elements and select '''Delete''' to remove these elements. [[File:Modifying-Columns.png|650px|thumbnail|center|Selecting columns to delete]] === Step 3: Adjust the presentation of report elements as desired === # Select the report frames and reduce their widths by dragging the elements to the right position. You can use the grid lines to help you find the edges of each element.

[[File:Column-Width.png|650px|thumbnail|center|Frame width is adjusted]]

# To reduce the width of the report, click into the design view (the gray part of the screen, below the report) of the report in focus. That will bring up the ''Properties'' pane on the right-hand side of the screen.

[[File:Report-Properties-Detail.png|450px|thumbnail|center|Report template properties]]

# At the bottom of ''Properties'', select '''Edit Page Format'''. In the window that pops up, you can reduce the width of the report, as well as make other adjustments as needed.

[[File:Page-Format.png|450px|thumbnail|center|Properties > Page Format]] = Previewing and Saving Modified Report Templates= When modifying report templates, you may wish to see how the changes you make in the ''Design'' view actually look on the report template. The Jaspersoft Studio ''Preview'' tab generates and displays the report as-is, providing a glimpse of the report template in its current state. [[File:reporting-db-spec-image8.png|thumb|600px|center|]] == How to Preview == To preview a modified report, follow these steps: # Enter/edit all parameters in ''Design'' view (e.g., ''Timeframe (start_time:Timeframe Start)'').

# Select the ''Preview'' tab.

# Select the data source.

# Enter the desired report format (i.e., Java, HTML, PDF, DOCx, etc.) with which to view the report.

# Click the '''Run''' button [[File:Jaspersoft-Run-Button.png|26px]]. Your modified report will be displayed in the Preview area. If necessary, return to the Design view for further editing. Otherwise, save the modified report template with a new name by clicking ''File > Save As'' and entering a new file name. Doing a ''Save As'' only saves the modified report template in the Jaspersoft Studio ''reports'' folder. If you wish to upload the modified report template to Contact Center Administrator, you must also export the new report template and save it to the appropriate folder. == Notes == * If you see an exception regarding JRAbstractScriplet, restart the Jaspersoft Studio and repeat these steps. * When you click the ''Preview'' tab, a report will be run automatically. You must wait for it to be complete before you can select the desired data source and click '''Run'''. * If you get a compilation error, try reformatting the report template, and repeat the above Preview steps. = Uploading Modified Report Templates= Once you have finished customizing your report template, you can upload it to the Contact Center Administrator application and put it to use. Revised report templates are available for use as soon as they are uploaded. In the Contact Center Administrator application, # Navigate to [[#topic_contact-center-administrator-guide/ReportTemplates|Report Templates]].

# Click '''+''' to add a new report template.

# Specify the report name and category, and click '''upload'''.

# Select the template you saved previously.

# Click the '''Upload''' button. [[File:reporting-db-spec-image9.png|thumb|800px|center|]] = Overview= Bright Pattern Contact Center version 3.16 and later provides the capability to export a large volume of data as a CSV report. This capability is generally used for detailed tables such as agent_activity and call_detail (see our out-of-the-box CSV reports: [[5.0:reporting-reference-guide/AgentActivity(CSV)Report|Agent Activity (CSV)]] and [[5.0:reporting-reference-guide/CallDetail(CSV)Report|Call Detail (CSV)]]). These report templates are in a new format called BPXML, and such files have the file extension ''.bpxml''. You can create CSV reports for any report template type, not just the out-of-the-box report templates described above. Unlike report templates in JRXML format, report templates in BPXML format are not customized in Jaspersoft Studio. Designed to be simpler to edit, BPXML report templates are worked in the plain text editor of your choice. Essentially, a BPXML report template is an XML file with special headers and SQL statements that retrieve the specified data and place it in the CSV file. [[File:Agent-Activity-BPXML.PNG|650px|thumbnail|center|Agent Activity (CSV) report template]] In the sections that follow, you will learn how to download and modify BPXML files to create custom CSV reports. See the ''Contact Center Administrator Guide'' for more information on [[#topic_contact-center-administrator-guide/ReportTemplates|report templates]]. = BPXML Syntax and Samples= You can create a custom BPXML report template and use it to export CSV data. This section explains the syntax and structure of BPXML report templates. == Syntax == The top level "BPcsvReport" element contains optional "parameters" and "queryString" sub-elements. "queryString" is a common SQL statement, and it can contain some parameters. === Parameter Types === The following is the full set of supported parameters: * '''"agent"''' – agent selector * '''"agentlist"''' – agent multiple selector * '''"end_time"''' * '''"service"''' – service selector * '''"servicelist"''' – service multiple selector * '''"start_time"''' * '''"team”''' – team selector * '''"teamlist"''' – team multiple selector * '''"timeframe"''' subtypes: ** '''"start"''' ** '''"end"''' (only one pair per report) – start and end dates selector ** '''"string"''' - an arbitrary string value == Sample BPXML Report == $P{start_time} AND login_id=$P{login_id} ]]> == Examples == The following are examples of various types of BPXML reports. Values that differ from the Sample BPXML Report given above are shown in pink. === "agentlist" Usage Sample === In this sample, parameter ''agentlist'' is being used to add agent data to the Agent Performance report. agentlist" /> $P{start_time} AND login_id IN ($P{login_id})
]]> === "call_detail" Usage Sample === In this sample, metrics ''hold_time'', ''held'', and ''max_hold'' are being used to add hold time data to the Call Detail report. hold_time, held, max_hold, team_name FROM call_detail WHERE start_time > $P{start_time} AND login_id=$P{login_id} ]]> = How to Modify and Use BPXML Report Templates= # Either copy the [[custom-reporting-tutorial/BPXMLSyntaxandSamples#Sample_BPXML_Report|Sample BPXML Report]] XML, or [[contact-center-administrator-guide/ReportTemplates#Download|download]] a BPXML report template from the Contact Center Administrator application.

# Paste the XML into a plain text editing application, and edit the [[custom-reporting-tutorial/BPXMLSyntaxandSamples#Parameter_Types|parameters]] to obtain the desired data in CSV.

# Save the file with file extension ''.bpxml''. This is your customized report template.

# In Contact Center Administrator, [[contact-center-administrator-guide/ReportTemplates#Upload|upload]] your customized report template. The new report template will appear in the list of available templates. = Creating a Data Adapter= Data adapters allow Jaspersoft Studio to use SQL queries to get data from your Reporting Database. In the following procedure, you will create a data adapter that uses the Java Database Connectivity (JDBC) API for the programming language Java, which defines how a database can be accessed by a client. JDBC provides methods to query and update data in a database. '''Note that creating a data adapter is not required unless you have direct database access; direct database access is a licensed fee.''' [[File:reporting-db-spec-image2.png|thumb|600px|center|]] == Procedure == To create a data adapter, navigate to the Repository Explorer tab in the left-hand corner of the screen, and proceed through the following steps. === Step 1: Create a data adapter === # In ''Repository Explorer'', select and right-click '''Data Adapters'''.

# Then select '''Create Data Adapter'''. [[File:Create-Data-Adapter.png|450px|thumbnail|center|Repository > Create Data Adapter]] === Step 2: Choose your connection === * In the window that pops up, select '''Database JDBC Connection''' and click '''Next'''. [[File:Database-JDBC.png|450px|thumbnail|center|Create Data Adapter > Database JDBC Connection]] === Step 3: Specify JDBC properties === You will be defining the properties (i.e., driver, database server host, authentication) for the Java Database connection. In the window that pops up, specify the following: * '''Name:''' Name of the data adapter; note that "New Data Adapter" is the default name but it may be renamed * '''JDBC Driver:''' ''org.mariadb.jdbc.Driver'' (Note: copy and paste this driver name into the field, or use the pull-down menu to select this driver.) * '''JDBC Url:''' ''jdbc:mariadb://<your database server host name>:3306/< your reporting database name>'' * '''Username:''' Username for access to your Reporting Database * '''Password:''' Password for access to your Reporting Database Then click '''Finish'''. === Notes === * For database server host name, database name, and username, open the Contact Center Administrator application and navigate to [[#topic_contact-center-administrator-guide/ReportingSettings|Reporting Settings]]. * In the ''JDBC Url'' field, do not include "<" or ">" symbols. Replace "''''" and "''''" with your respective host and database names.