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.

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

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:

      <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.

      meta.bireport.isStandAlone="false"

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

      meta.bireport.enabled="false"

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

      <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:

      <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:


      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:

      <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:

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

   6. Add the following channel:

      <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: 

      meta.bireport.isStandAlone="true"

   9. Add the following channel:

      <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 . To test replication, add a new contact in the NexJ CRM database server, and then manually query the NJEntity table on both databases to confirm that the data synchronized for the new contact is the same between both servers. 

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:

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:

   <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:

      <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:

      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.

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

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

   <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:

   <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

• Setting up the ad hoc reports export and dashboards export