Setting up ad hoc reporting

NexJ CRM's ad hoc reporting capabilities are powered by NexJ Reporting, which provides runtime-configurable reporting capabilities. Users can dynamically define reports by selecting relevant data across CRM subject areas and summarizing the selected data into tabular or graphical representations. They can also organize the reports into easy-to-read dashboards.

You can deploy the ad hoc reporting functionality on a separate dedicated server or directly on your CRM server. NexJ recommends the dedicated server setup so that running ad hoc reports does not impact the performance of your CRM server. 

The process of setting up ad hoc reporting involves adding a Hosts tag to the environment file to enable the Reports workspace in NexJ CRM. If the ad hoc reporting server is dedicated (specific to reporting), the Hosts tag also ensures that the ad hoc reporting server recognizes users set up on the NexJ CRM server. Each server has an environment file to configure a host.

Throughout this topic, NexJ CRM database refers to the database used by the DefaultRelationalDatabase data source on the NexJ CRM database server, and Reporting database refers to the database used by the DefaultRelationalDatabase data source on the Reporting database server.

The following table shows a summary of the required settings for each of the supported configurations.

** If you have previously deployed the application with the meta.bireport.enabled variable set to true in the finance environment file, re-deploying with this variable set to false will require the following cleanup steps:

1. Without this step, you will encounter unknown class errors when the system does not find the metaclasses of the bireport batch jobs that were previously in the bireport metadata.
   

   Before performing this step, consider the following:

   • The data augments associated with bireport will not be deleted by the script in this step. 
   • If you want to set meta.bireport.enabled to true subsequent to running the script in this step, you must reseed bireport to ensure that reports are properly calculated and maintained. By reseeding bireport, required timers for batch jobs are reset for execution (for example, bireport:ScheduleReportBatchJob).

   This cleanup step is to run the meta/libraries/debug/debugScripts.scm scheme script called debug:cleanupReportBatchJobs in the finance library. 
   
   

2. To prevent UI test suite validation failure, remove the following unit tests: 

   • report:UI
   • report:UIDashboard

   
   

After you set up ad hoc reporting, see Ad hoc reports development to extend and further customize ad hoc reporting.

Enabling ad hoc reporting on a dedicated server

You can set up a dedicated server so that ad hoc reports will run on a server that is separate from NexJ CRM. After setting up the server, you must periodically synchronize it with the NexJ CRM database server, or you can use NexJ Report Adapter for real-time synchronization updates.

To set up a dedicated server for NexJ Reporting:

1. Back up the NexJ CRM database.

2. Restore the NexJ CRM database backup onto the Reporting database.

   1. When performing the restore, ensure that Overwrite the existing database (WITH REPLACE) is selected for MSSQL servers under Options.
   2. Truncate the NJNode table.

3. Configure the NexJ CRM database server by making the following modifications to the NexJ CRM environment file:

   1. Ensure the Hosts tag exists, and the URL is linked to the Reporting database server as shown in the following example:

      XML

      <Hosts>
          <Host name="dai" trusted="true" url="<report.httpURL>"/>
      </Hosts>

      

      The Hosts tag must be immediately after the environment's root tag, mixins tag, or PKIKeyPairs tag, whichever comes last. Hosts tags are case sensitive.

      If you need to modify the URL for the Hosts tag after the initial server startup (for example, to change http to https), change the URL value in the Hosts tag, and reseed the portal for the change to be persistent, or make the update using an SQL query directly, and then restart the server nodes.

      

      The Hosts tag should be used instead of the httpReportURL : string tag that does not apply to ad hoc reports.

   2. Set the meta.bireport.isStandAlone property to false, as follows. This, together with value set for this attribute in the Reporting database server, ensures batch jobs run as usual with the reporting metadata mixed in.

      CODE

      meta.bireport.isStandAlone="false"

   3. Set the meta.bireport.enabled property to false, as follows:

      CODE

      meta.bireport.enabled="false"

   4. Add the reportadapter mixin. The following example shows how the mixin displays in the source:

      XML

      <Mixins>
          ...
          <Mixin namespace="nexj:model:dai:reportadapter" version="0.10.0+"/>
          ...
      </Mixins>

   5. Add the rptadapter:ReportingInstanceSender channel to the environment file for NexJ CRM; for example:

      TEXT

      <HTTPConnection channel="rptadapter:ReportingInstanceSender" connectionTimeout="100" password="${crm.reporting.http.receiver.passwd:}" url="${URL to Reporting Server/channel/bireport:ReportingInstanceSync/NexJ}" user="${crm.reporting.http.receiver.user:nexjsa}"/>

4. Configure the Reporting database server by making the following modifications to the Reporting environment file:

   1. Modify all the data sources to use new databases specific to the Reporting database server.
   2. Ensure the bireport:DefaultRelational data source exists in the data source connections list. The bireport:DefaultRelational and ObjectQueueDatabase data sources must be set to use a different database than other data sources.

   3. Add the meta.bireport.enabled property, in the following format:


      CODE

      meta.bireport.enabled="true"

   4. Ensure the Hosts tag exists, and the URL links to the NexJ CRM database server as shown in the following example:

      XML

      <Hosts>
          <Host name="crm" trusted="true" url="<crm.httpURL>"/>  
      </Hosts>
      

      

      The Hosts tag must be immediately after the environment's root tag, mixins tag, or PKIKeyPairs tag, whichever comes last.

   5. Add the following channel:

      CODE

      <HTTPConnection channel="report:RPCHTTPSender" password="${reportcrm.http.instanceSync.passwd:}" url="${crm.httpURL}/text" user="${reportcrm.http.instanceSync.user:nexjsa}"/>

   6. Add the following channel:

      TEXT

      <HTTPConnection channel="bireport:ReportingAttributeRecalculationReceiver"/>

   7. Remove the BI_1_BI_Database data source from the data source connections list.

   8. Set the meta.bireport.isStandAlone property to true, as follows: 

      CODE

      meta.bireport.isStandAlone="true"

   9. Add the following channel:

      TEXT

      <HTTPConnection channel="bireport:ReportingInstanceSync" connectionTimeout="100" password="${reportcrm.http.instanceSync.passwd:}" url="${URL to Reporting Server/channel/rptadapter:ReportingSyncRequestReciever/NexJ}" user="${reportcrm.http.instanceSync.user:nexjsa}"/>

   10. If both the Reporting and CRM servers are deployed on the same VM or physical machine, ensure there are no port conflicts for the httpURL, ajp.port, and pushRedirectorURL properties. For example, if you are using the default ports 7080 for httpURL, 8009 for ajp.port, and 5080 for pushRedirectorURL for the NexJ CRM database server, avoid using the same ports for the Reporting database server.

Ad hoc reporting is set up as a dedicated server. When NexJ CRM is refreshed, the Reports workspace displays, and users with appropriate privileges can view and create ad hoc reports. You can use the NexJ Report Adapter to synchronize the servers for real-time updates. 

Synchronizing the dedicated server by using NexJ Report Adapter

The NexJ Report Adapter synchronizes the dedicated Reporting server with the NexJ CRM server through database fragment replication. This replication sends updates to both the database of the NexJ CRM database server and the database of the dedicated Reporting server simultaneously. The following steps set up NexJ Report Adapter to synchronize the databases:

1. Stop the NexJ CRM database server (the Model Server for NexJ CRM that does not host ad hoc reporting).

2. Back up the NexJ CRM database and restore the NexJ CRM database backup on to the Reporting database. This step updates the Reporting database server to match the NexJ CRM database server to remove any inconsistencies in data sets.

   1. When performing the restore, ensure that Overwrite the existing database (WITH REPLACE) is selected for MSSQL servers under Options.
   2. Truncate the NJNode table.
   3. Upgrade the DefaultRelationalDatabase data source, ensuring Ignore Upgradable Flag is selected. Also, upgrade any other data sources that have been extended to include persisted reporting attributes.

   4. Recreate data for the bireport:DefaultRelational and ObjectQueueDatabase data sources. Alternatively, generate scripts to drop and create data for both the bireport:DefaultRelational and the ObjectQueueDatabase data sources. If the bireport:DefaultRelational tables previously existed, drop them first.

      

      Earlier versions of the bireport:DefaultRelational data source tables may exist in a different RelationalDatabaseConnection than desired.

      Next, run the "create" script for the bireport:DefaultRelational data source. Repeat the drop and create steps for the ObjectQueueDatabase data source.

3. Add the Reporting database as a replication fragment in the CRM environment file for the NexJ CRM database server:
   1. In the environment file, select the Relational Database connection database connection.
   2. Click the Fragments tab.
   3. In the General tab, type the database information for the Reporting database that will be synchronized with NexJ CRM data.
   4. In the name field, type reportDefaultRelationalDatabase.
4. If not started, start the Reporting database server (the Model Server that hosts ad hoc reporting).
5. Start the NexJ CRM database server (the Model Server for NexJ CRM that does not host ad hoc reporting).

New reports created from the dedicated Reporting server will now display the same data updates as the NexJ CRM database server. To update existing reports to use recently synchronized data, users must click the Update Report button

Enabling ad hoc reporting on the NexJ CRM server

You can set up ad hoc reporting on the same server as the core NexJ CRM database server.

To enable ad hoc reporting on the NexJ CRM server:

If the bireport:DefaultRelational database already exists (for example, if ad hoc reporting has been previously enabled and disabled), steps 4 and 6 in this procedure are optional.

1. Add the bireport.enabled property, in the following format:
meta.bireport.enabled="true"

2. Add the meta.bireport.isStandAlone property, in the following format:
   meta.bireport.isStandAlone="false"

3. Ensure the Hosts tag exists, and the URL is set to the HTTP URL of the CRM application as shown in the following example:

   XML

   <Hosts> 
       <Host name="dai" trusted="true" url="<report.httpURL>"/> 
   </Hosts>

   

   The Hosts tag must be immediately after the environment's root tag, mixins tag, or PKIKeyPairs tag, whichever comes last.

4. Add the following channel information:

   1. Add the following channel to the NexJ CRM database server environment file:

      TEXT

      <HTTPConnection authentication="proactive" channel="rptadapter:ReportingAttributeRecalculationSender" connectionTimeout="100" description="Outgoing channel to the DAi instance to which NexJ-specific CDC messages are sent." password="${crm.reporting.http.receiver.passwd:nexj}" url="${crm.reporting.http.calcAttrSync.url:}" user="${crm.reporting.http.receiver.user:nexjsa}"/>

      

      In your properties file, $context-root should be replaced with the URL of the Reporting database server, as shown below:

      TEXT

      crm.reporting.http.calcAttrSync.url=http\://$context-root/nexj/channel/bireport\:ReportingAttributeRecalculationReceiver/NexJ

5. Configure the data source connections for the bireport:DefaultRelational database.

6. Set up the bireport:DefaultRelational data source.
7. Upgrade the DefaultRelationalDatabase on the NexJ CRM database server.
8. Recreate data for the bireport:DefaultRelational data source.
9. Reseed the database. 
10. Restart the server.

Ad hoc reporting is set up as integrated Reporting. When NexJ CRM is refreshed, the Reports workspace displays, and users with appropriate privileges can view and create ad hoc reports.

To run ad hoc reporting, add the following mixin to the project level environment file:

<Mixins>
    ...
    <Mixin checksum=”<jar checksum>” namespace="nexj:model:dai:financereport" version="<model version>"/>
    ...
</Mixins>

Disabling ad hoc reporting on the NexJ CRM server

To disable ad hoc reporting on the NexJ CRM server:

1. Modify the bireport.enabled property using the following format:
meta.bireport.enabled="false"

2. Modify the meta.bireport.isStandAlone property using the following format:
   meta.bireport.isStandAlone="false"

3. Ensure the Hosts tag are removed.

   XML

   <Hosts>
       <Host name="dai" trusted="true" url="<report.httpURL>"/>
   </Hosts>

4. Remove the following channel to the NexJ CRM database server environment file:

   TEXT

   <HTTPConnection authentication="proactive" channel="rptadapter:ReportingAttributeRecalculationSender" connectionTimeout="100" description="Outgoing channel to the DAi instance to which NexJ-specific CDC messages are sent." password="${crm.reporting.http.receiver.passwd:nexj}" url="${crm.reporting.http.calcAttrSync.url:}" user="${crm.reporting.http.receiver.user:nexjsa}"/>

5. Remove the data source connections for the bireport:DefaultRelational database.

6. Remove the following mixin to the project level environment file:

   XML

   <Mixins>
       ... 
       <Mixin checksum=”<jar checksum>” namespace="nexj:model:dai:financereport" version="<model version>"/> 
       ... 
   </Mixins>

7. Run the bireport/meta/scripts/ReportingUninstallScript.sql script against the NexJ CRM database.

Related topic