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 standalone server or on the same server as your core NexJ CRM instance. NexJ recommends the standalone setup so that running ad hoc reports does not impact the performance of the core NexJ CRM instance.

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 standalone (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 instance, and Reporting database refers to the database used by the DefaultRelationalDatabase data source on the Reporting instance.

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

Configuration	NexJ CRM	NexJ Reporting
Reporting disabled: **
meta.bireport.enabled	False	Not applicable
meta.bireport.isStandAlone	False	Not applicable
Operational:
meta.bireport.enabled	True	Not applicable
meta.bireport.isStandAlone	False	Not applicable
Standalone:
meta.bireport.enabled	False	True
meta.bireport.isStandAlone	False	True

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

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

You can set up a standalone instance where ad hoc reports would run on a separate server than NexJ CRM. After setting up the instance, you must periodically synchronize it with the NexJ CRM instance, or you can use NexJ Report Adapter for real-time synchronization updates.

To set up a standalone Reporting instance:

Back up the NexJ CRM database.
Configure the NexJ CRM instance 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 instance 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.
2. Set the meta.bireport.isStandAlone property to false, as follows. This, together with value set for this attribute in the Reporting instance, 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.
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>
```
Configure the Reporting instance by making the following modifications to the reporting instance's environment file:
1. Modify all the data sources to use new databases specific to the Reporting instance.
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 instance 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. Remove the BI_1_BI_Database data source from the data source connections list.
6. Set the meta.bireport.isStandAlone property to true, as follows:
```
meta.bireport.isStandAlone="true"
```
7. If running the Reporting instance on the same server as the NexJ CRM instance, 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 instance, avoid using the same ports for the reporting instance.
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. 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 "drop" and "create" scripts 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.

Ad hoc reporting is set up as a standalone instance. 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 databases of both instances for real-time updates.

Synchronizing the standalone server by using NexJ Report Adapter

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

Stop the NexJ CRM instance (the Model Server for NexJ CRM that does not host ad hoc reporting).
Back up the NexJ CRM database and restore the NexJ CRM database backup on to the Reporting database. This step updates the Reporting instance to match the NexJ CRM instance 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.
Add the Reporting database as a replication fragment in the CRM environment file for the NexJ CRM instance:
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.
If not started, start the Reporting instance (the Model Server that hosts ad hoc reporting).
Start the NexJ CRM instance (the Model Server for NexJ CRM that does not host ad hoc reporting).

New reports created from the standalone Reporting instance will now display the same data updates as the NexJ CRM instance. 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 instance, 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 instances.

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

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.

Add the bireport.enabled property, in the following format:meta.bireport.enabled="true"
Add the meta.bireport.isStandAlone property, in the following format:
meta.bireport.isStandAlone="false"
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.
Configure the data source connections for the bireport:DefaultRelational database.
Set up the bireport:DefaultRelational data source.
Recreate data for the bireport:DefaultRelational data source.
Reseed the database.
Upgrade the DefaultRelationalDatabase on the NexJ CRM instance.
Restart the server.

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