Ad hoc reports are run-time configurable reports that include data from one or more associated NexJ CRM subject areas.

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.

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

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 synch it with the NexJ CRM instance.

To set up a standalone Reporting instance:

1. Back up the NexJ CRM database.

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

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

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

Synching the standalone Reporting instance with NexJ CRM

Use SQL replication so the data in the standalone Reporting instance is updated every few minutes and reflects recent changes made in the NexJ CRM instance.

To synch changes from the NexJ CRM instance to the reporting instance:

1. Stop the NexJ CRM instance.

2. Create a publication for the NexJ CRM database:

   For more information about creating publications, see https://docs.microsoft.com/en-us/sql/relational-databases/replication/publish/create-a-publication?view=sql-server-2017.

   1. Set the type of replication to transactional.

   2. Select all database tables for replication, except the following:

      BPVersion	NJAPFactOppMonthly	NJBISyncTargetLock	NJBI_1_BIHolding	NJBI_1_BITouchType	RTActionSet
      CDCVersion	NJAPFactOppQrtrly	NJBIVersion	NJBI_1_BIHoldingX01	NJBI_1_BITypeLocalizatin	RTAssociatedAction
      CIVersion	NJAPFactOppWeekly	NJBI_1_BIAccount	NJBI_1_BIHoldingX02	NJBI_1_BIUPApprStep	RTCondition
      CLVersion	NJAPFactOppYearly	NJBI_1_BIAccountX02	NJBI_1_BIHoldingX_EN	NJBI_1_BIUPApproval	RTConditionExpr
      CNVVersion	NJAPFactProdOpp	NJBI_1_BIAct	NJBI_1_BIHoldingX_FR	NJBI_1_BIUPRSMeet	RTExprObjVal
      LGAttributeAuditLog	NJAPFactResrchDaily	NJBI_1_BIActACL	NJBI_1_BIHousehold	NJBI_1_BIUserField	RTMessageState
      LGAuditLog	NJAPFactRevSumDaily	NJBI_1_BIActACLApprStep	NJBI_1_BIHouseholdACL	NJBI_1_BIUserPerson	RTRule
      LGAuthenticationAuditLog	NJAPFamily	NJBI_1_BIActACLApproval	NJBI_1_BIHouseholdCF	NJBI_1_BIUserPersonACL	RTRuleSet
      LGProcessLogX01	NJAPFamilyAttribute	NJBI_1_BIActTouchType	NJBI_1_BIHouseholdXAddr	NJBI_1_BIUserPersonCF	RTRuleSetText
      LGReportAuditLog	NJAPOREntCov	NJBI_1_BIApprStep	NJBI_1_BIInboundCall	NJBI_1_BIUserPersonXAddr	RTRuleSetX01
      LGVersion	NJAPORExchangeRate	NJBI_1_BIApproval	NJBI_1_BILead	NJBI_1_BIUserPrincipal	RTRuleText
      MSGVersion	NJAPOROppCoverage	NJBI_1_BICFType	NJBI_1_BILeadACL	NJBatchProcessConfig	RTScheduledTrigger
      NJAPDimCampaign	NJAPORRankedTier	NJBI_1_BICampaign	NJBI_1_BIList	NJBillingAccount	RTScheduledTriggerXErrMsg
      NJAPDimCompany	NJAPORUserPersonACL	NJBI_1_BICampaignUser	NJBI_1_BIListMember	NJDashboardPage	RTTimeTrigger
      NJAPDimCompanyXAddr	NJApplication	NJBI_1_BICompany	NJBI_1_BIOppHist	NJDashboardPagePortlet	RTTypeLocalization
      NJAPDimContact	NJApplicationPortletRef	NJBI_1_BICompanyACL	NJBI_1_BIOpportunity	NJNode	RTUserUnsubscrAction
      NJAPDimContactXAddr	NJApplicationWks	NJBI_1_BICompanyCF	NJBI_1_BIOpportunityACL	NJNodeStat	SSOSecurityContex
      NJAPDimCurrency	NJArchivedData	NJBI_1_BICompanyXAddr	NJBI_1_BIPrincipal	NJProcess	SSOSecurityContextConfig
      NJAPDimHousehold	NJBIAttribute	NJBI_1_BIContact	NJBI_1_BIProdTypeOpp	NJProcessLogX01	SSOVersion
      NJAPDimHouseholdXAddr	NJBIAttributeAssoc	NJBI_1_BIContactACL	NJBI_1_BIProduct	NJProcessStatusChange	UIComponent
      NJAPDimOpportunity	NJBIBIEntParticipat	NJBI_1_BIContactCF	NJBI_1_BIProductOpprtnty	NJSession	UIComponentInterface
      NJAPDimProduct	NJBIBIOpportunCove	NJBI_1_BIContactXAddr	NJBI_1_BIRSCoverage	NJTimer	UIField
      NJAPDimTime	NJBIBIUsParticipati	NJBI_1_BICurrency	NJBI_1_BIRSLeg	NJVersion	UITypeLocalization
      NJAPDimUserPerson	NJBICampaignParticipat	NJBI_1_BIDocument	NJBI_1_BIRSLegCOrig	OQMessage	URArtifact
      NJAPDimUserPersonXAdd	NJBIClass	NJBI_1_BIEPApprStep	NJBI_1_BIRSMeeting	OQMessageXDAi	URComponentDef
      NJAPFact	NJBIClassFilter	NJBI_1_BIEPApproval	NJBI_1_BIRSMtACL	OQMessageXNotify	URComponentInst
      NJAPFactActDaily	NJBIClassRelAggr	NJBI_1_BIEPRSMeet	NJBI_1_BIRankedTier	OQObjectQueue	URComponentInstSvc
      NJAPFactAttribute	NJBIClassRelAttrMapping	NJBI_1_BIEntityCategory	NJBI_1_BIRoadshow	OQObjectQueueDispatcher	UREnterpriseRole
      NJAPFactMeetDaily	NJBIClassRelBIClass	NJBI_1_BIEntityCoverage	NJBI_1_BIRoadshowACL	OQQueue	UREnterpriseRoleUser
      NJAPFactOppAcc	NJBIClassRelationship	NJBI_1_BIEntityOpp	NJBI_1_BIRoadshowCOrig	OQRecChunkIterMsg	UREnterpriseUser
      NJAPFactOppDaily	NJBIContext	NJBI_1_BIEnumDisplay	NJBI_1_BIServiceModel	OQVersion	URPortlet
      NJAPFactOppFQrtrly	NJBIServer	NJBI_1_BIExchangeRate	NJBI_1_BIServiceRequest	RTAction	URPortletGroup
      NJAPFactOppFYearly	NJBISyncFinancial	NJBI_1_BIFolder	NJBI_1_BITouchStatus	RTActionExpr	URTypeLocalization
      					URVersion

3. Modify the replication publication settings to allow initialization from backup and to not replicate schema changes.
   

   For more information, see https://docs.microsoft.com/en-us/sql/relational-databases/replication/enable-initialization-with-backup-for-transactional-publications?view=sql-server-2017 and
   https://docs.microsoft.com/en-us/sql/relational-databases/replication/publish/replicate-schema-changes?view=sql-server-2017.

4. Create a replication subscription to the Reporting database, ensuring that the subscription is not initialized.

   For more information, see https://docs.microsoft.com/en-us/sql/relational-databases/replication/create-a-pull-subscription?view=sql-server-2017 and https://docs.microsoft.com/en-us/sql/relational-databases/replication/subscribe-to-publications?view=sql-server-2017.
5. If synchronization starts automatically, ensure it is stopped.
6. Create a backup of the NexJ CRM database.
7. Restore the backup to the Reporting database.
8. Verify that the backup restore has initialized the replication subscription.
9. Truncate the following tables in the Reporting database:
   • OQQueue
   • OQMessage
   • OQMessageXDAi
   • OQMessageXNotify
   • OQObjectQueue
   • OQObjectQueueDispatcher

10. Using the following script, create the appropriate replication stored procedures for the Reporting database:

    EXEC sp_scriptpublicationcustomprocs @publication='<publicationName>

11. Recreate data for the bireport:DefaultRelational data source on the Reporting instance. 

    When performing the recreate, you may receive errors, such as those related to missing columns. This happens because a recreate, even if performed for a single data source, attempts to seed values across all data sources. You can ignore such errors.

12. Reseed the DefaultRelationalDatabase data source on the Reporting instance. A reseed can be performed by running the following query:
    Update NJVersion set loaded = 0

13. Configure the Replication Distribution Agent to use the -SkipErrors argument. For more information about Replication Distribution Agent, see https://docs.microsoft.com/en-us/sql/relational-databases/replication/agents/replication-distribution-agent?view=sql-server-2017.

14. Start synchronization.
15. Start the NexJ CRM and Reporting instances.

New reports created from the standalone Reporting instance will now display the latest NexJ CRM data. To update existing reports, users must click the Update Report button  in the reporting instance.

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:

1. Add the financereport mixin to your environment file. The following example shows how the mixin displays in the source:

   <Mixins>
       ...
       <Mixin namespace="nexj:model:dai:financereport" version="0.0.12+"/>
       ...
   </Mixins>

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

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

5. Configure the data source connections for the bireport:DefaultRelational database.
6. Set up the bireport:DefaultRelational data source.
7. Recreate data for the bireport:DefaultRelational data source.
8. Upgrade the DefaultRelationalDatabase on the NexJ CRM instance.
9. 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.