Ad hoc reports development

You can seed in public reports, customize report template names and descriptions, and create and extend subject areas for ad hoc reports.

You can extend a model to persist calculated attributes so they are available for selection when generating ad hoc reports.

When you are extending a reporting project, you must check that the meta.bireport.enabled flag is set to true for successful execution. For example, report template seeding would require the enabling of this flag. Use the following script to check whether this flag is set to false or not:

SCHEME

(get-config-property-flag "meta.bireport.enabled" "boolean" #f)

Seeding public reports

The term reporting interface in this topic refers either to the Reports workspace or the standalone reporting application, depending on your configuration.

You can seed public reports into the reporting interface so they are visible to all users accessing the application. Business users can create reports, export the report definitions as JSON files, and share them with you so you can seed them in as public reports. These reports serve as templates, which users with appropriate privileges can clone and customize as needed.

To seed reports:

1. Seed the JSON report definitions into the project's meta\datasources\report-public folder.

2. Ensure the project has a SysVersion metaclass with the required persistence mappings and has a Version table as a data source, as shown in the following example:

   CODE

   <Class base="SysVersion" description="System upgrade version information" visibility="protected">
      <PersistenceMapping dataSource="testproduct:RelationalDatabase" fragmentReplication="broadcast">
         <RelationalMapping primaryTable="Version">
            <AttributeMappings>
               <AttributeMapping column="namespace" name="namespace"/>
               <AttributeMapping column="version" name="version"/>
               <AttributeMapping column="step" name="step"/>
               <AttributeMapping column="upgradable" name="upgradable"/>
               <AttributeMapping column="test" name="test"/>
               <AttributeMapping column="loaded" name="loaded"/>
            </AttributeMappings>
         </RelationalMapping>
      </PersistenceMapping>
   </Class>

   Also, ensure this data source is included in your environment file.

3. Perform a Load step in the Main.upgrade file, incrementing the module version as appropriate.

4. Using the Data Load tool, upgrade the database, selecting the data source that has the Version table for SysVersion metaclass. Alternatively, you can upgrade all data sources by selecting * as the data source.

   

   Report names should be unique. If duplicate report names exist in the folder, an error displays.

The reports are seeded in. Users will see the added reports when they reload the application.

If you remove a report definition from the report-public folder, you must also remove the seeded report from the system by running an SQL script such as the following:
delete from RPTReport where name = {Name Of Report}

Customizing report template details

You can override the default names and descriptions of ad hoc report templates by modifying the corresponding string IDs.

The first time you want to customize report template details, you will need to create a string file for the overrides with string IDs for each detail you want to change. For any further updates, you can edit the string IDs in this file as needed. This procedure assumes this is the first time you are editing these details.

To customize report template details:

1. Create a string file for the report overrides you want to create. In NexJ Studio, strings are created in the Resources layer. Give the file a meaningful name, for example, reportoverrides.

2. In the string file, create one or more of the following string IDs, depending on what you want to do:

   Action	String ID name	String ID value
   Rename a report template	ids.report.override.<TemplateName>.caption	<new_template_name>
   Change the description of a report template	ids.report.override.<T emplateName >.description	<new_template_description>

   

   In the string ID name, enter the original, seeded-in name of the report template without spaces and in the original case. In the value, enter the new value with spaces and in the desired case. For example, if the seeded template name is Scheduled activity breakdown report, in the string ID name, replace <TemplateName> with ScheduledActivityBreakdownReport, and in the value, enter the new template name you want to use, for example, Upcoming activity tracking.

   

   If the new values are considerably different from the seeded-in names, we recommend that you maintain a list of the seeded names and current values outside of NexJ Studio. This will help you easily identify the string associated with any template or subject area that you see in the reporting interface.

3. Save your changes.
4. Upgrade the database to reseed the template name or description. For more information, see Upgrading databases.
5. Restart the server for the string IDs to be added or updated.

The report template is updated. When users reload the application and open the Create Report dialog, the updated template details display.

Creating subject areas

You can create additional subject areas for ad hoc reports. Subject areas are defined in individual metadata files with the .sarea extension.

The first time you want to add subject areas, you will need to create a string file with a separate string ID for each subject area you want to add. You will also need to create a subdirectory in your project's meta directory for the .sarea metadata files for the subject areas you add. This procedure assumes this is the first time you are adding subject areas.

To add a new subject area:

1. Create a string file that will contain the captions for the subject areas you want to add. In NexJ Studio, strings are created in the Resources layer. Give the file a meaningful name, such as ids.subjectArea.en.strings.
2. In the string file, create a string ID for the subject area you want to create. For example, to add the Activities subject area, create a string ID ids.subjectArea.Activities with the value Activities. You will use this string ID in your subject area metadata file to indicate the caption (name) that should display for the subject area in the reporting interface.
3. In your project's meta folder, create a subdirectory for subject areas. Give it a meaningful name, for example, subjectareas.
4. In the subject areas folder, create a file with the extension .sarea. Give it a meaningful name, for example, Activities.sarea. This file is in the <project>/meta/subjectareas/ directory in this example.

5. Define the subject area properties as shown in the following example. The full metadata name for this example is <moduleName>:subjectareas:Activities.

   
   

   CODE

   <SubjectArea caption="ids.subjectArea.Activities" class="Act" icon="icon:\uf137" iconColorIndex="1">
      <Associations>
            <Association name="Author" caption="Author" path="(@ author)"/>
            <Association name="Creator" caption="Creator" path="(@ creator)"/>
            <Association name="RelatedUsers" caption="Related Users" path="(@ userParticipants entity)"/>
            <Association name="RelatedEntities" caption="Related Entities" path="(@ entityParticipants entity)"/>
            <Association name="RelatedLead" caption="Related Leads" path="(@ lead)"/>
            <Association name="RelatedOpportunity" "path="(@ opportunity)"/>
            <Association name="RelatedServiceRequest" caption="Related Service Requests" path="(@ serviceRequest)"/>
            <Association name="Template" caption="Template" path="(@ template)"/>
      </Associations>
   </SubjectArea>

   In this snippet:

   • caption is the predefined string ID associated with the subject area, whose value displays as the subject area name in the reporting interface. If the specified string ID is not found, the text value that you specify, in this case, ids.subjectArea.Activities, directly displays. If this property is not specified, the value is derived from the metadata name, for example, <module_name>: Subject Areas: Activities.
   • class is the metaclass the subject area is based on. All the attributes of the specified metaclass are available as fields for the subject area. This value is required.
   • icon is the material design icon displayed for the subject area in the reporting interface. You can use any of the icons available in the maticons or mdicons font files the   < AFL_project> /mod/fonts folder. This value is required. For more information about icons, see Adding and updating icons.

   • iconColorIndex is the color code for the subject area icon. The following table lists the supported colors. If this property is not specified, the default color indicated in the table is used.

     Color	Icon color index value	Hex code
     	1 (default)	#26A69A
     	2	#AB47BC
     	3	#80D8FF
     	4	#FF9100
     	5	#2962FF
     	6	#F06292
     	7	#80CBC4
     	8	#CE93D8
     	9	#B3E5FC
     	10	#FFB74D
     	11	#64B5F6
     	12	#F48FB1

   • Associations are additional subject areas that you want the subject area to be able to access. The following properties are specified for associations:
     • name is a unique name to identify this association in the context of the subject area. This value is required.
     • caption is the name of the association (field) to be displayed in the reporting interface. If not specified, the default value is derived from the name. For example, the association RelatedOpportunity in the above code will display as Related Opportunity in the reporting interface since a caption is not specified.
     • path defines how to get to the association from the subject area's metaclass. It is defined in the format (@ attr1 attr2). For example, the association RelatedEntities is from the act class > entityParticipants attribute > entity attribute.
6. Save your changes.

7. Restart the server for the subject areas to be added to the reporting interface. If a required value is missing or if duplicate subject area captions are detected in the environment, an error displays during metadata loading.

If no errors are detected, the subject area is added to the reporting interface.

You can follow the same steps to add additional subject areas. At any time, you can edit or delete the subject areas that you add. 

Sortable parameter for subject area attributes

When building reports, users can select the field that will apply the sorting to the report in the Details tab in the Report Builder.

You can set a subject area attribute as sortable="true" and the attribute will be sortable in the preview table in the Report Builder. If you have set sortable=”false” for an attribute, the attribute will not be sortable in the Report Builder, will not display in the list of attributes to order by in the Report Builder, and its column header will not be clickable in the preview table.

For performance reasons, string attributes of indeterminate length, or exceeding 32767 characters, are automatically not sortable.

Navigable parameter for subject area attributes

Users can open detail pages in NexJ CRM by clicking on links for associated objects in the preview table in the Details tab on the Reports workspace that have detail data (for example, people, companies, service requests, deals, and opportunities). For example, when a user is viewing a deals report that uses the Owner field, the user can click on an owner's name in the Details tab to open the owner's detail page on the Contacts workspace.

A reporting class can be used to provide a link from the Details tab to the corresponding record in the detail page for the OID in the subject area when the following information applies:

• An attribute for the reporting class (for example, first name, last name, full name, title, or description), is set to navigable=”true”.
• The subject area for the report has a navigation path available, as it has a detail page in the portal associated with it that can be linked to.

When an association is chosen as a field by the user and it is a metaclass that has a detail page, it is navigable by default.

Filtering attributes using the whereClause parameter

You can specify additional filtering criteria that will always be applied in a report (for example, to exclude Users from a Contacts report) using a whereClause parameter. The following example restricts the subject area by class code so that only Person records are shown (and are not derived metaclasses like UserPerson):

<SubjectArea caption="ids.subjectArea.Contacts" class="Person" icon="icon:person" iconColorIndex="2" whereClause="(= (@ classCode) &quot;PSN&quot;))">

Filtering associations in instance selection dialogs using the pickerWhere parameter

You can specify additional filtering criteria using the pickerWhere parameter that will be applied when a selection dialog is displayed for filtering using specific association instances in the Filters tab. For example, you may want to exclude Users from the list of For participants when a selection dialog is used. For the following example, only those attributes that partially match the full name of "lamont" will be shown in the Enter (Specific) For filter selection dialog (shown below):

<Association name="For" path="(@ entityParticipants entity)" base="report:Contacts" pickerWhere="(like? (@ fullName) &quot;lamont&quot;)"/>

Configuring the sort order for associations in instance selection dialogs using the sortOrder parameter

You can configure the sort order for associations using the sortOrder parameter that will be applied when a selection dialog is displayed for filtering using specific association instances (for example, the Enter (Specific) For filter dialog) in the Filters tab. In the following example, the Activities For association is sorted by tier, and a Tier column will appear in the selection dialog.

<Association name="For" path="(@ entityParticipants entity)" base="report:Contacts" isIntersection="true" sortOrder="tier"/>

The isInteraction parameter shown above in the example enables an association to be seen as a collection even though the path resolves to a one-to-one association. It is useful if the collection before it in the path isn't actually added in the association, and the parameter will usually be used for intersection classes. For example, you could set the parameter to true when you don't want to have (@ entityParticipants) as an association, but you do want (@ entityParticipants entity), and you still want to represent the collection from entityParticipants.

Configuring attributes displayed in "specific" filter selection dialogs

For associations, you can display the name attribute in filter selection dialogs for association instances by using the pickerAttrs parameter for those associations. In addition, use this parameter to provide a comma-separated list of attributes that will appear in the filter selection dialogs as table columns for these instances. These columns will help the user to select an instance by displaying information that is unique to each instance. For example, you can configure the parameter to display the opportunity name and the expected close date for each instance in the filter selection dialog, or the activity description, and the start time, and end time for each instance.

<Association name="Activities" path="(@ entityParticipations act)" base="report:Activities" isIntersection="true" pickerAttrs="activityStartTime,activityEndTime"/>

Hiding the nested list of basic or association attributes

You can hide the nested list of basic or association attributes that would otherwise be visible under an association. For example, in an Activities report, the "For" association has other attributes in its hierarchy, which include Full Name and Last Name. To hide this underlying hierarchy for an association in the Add Fields dialog or in the Add Filters section in the Filters tab, you can use the hideHierarchy=true subject area flag on an association. For the following example, no hierarchy will be shown for this association, as shown below. Only the association itself will be available in selection dialogs ("specific" filters, association instance with name attribute elsewhere).

<Association name="Clients" path="(@ clients entity)" base="report:Contacts" hideHierarchy="true"/>

Excluding attributes from subject areas

The following example shows how to exclude attributes from subject areas:

<Attributes>
<Attribute name="createAuthorAlias" excluded="true"/>
<Attribute name="editAuthorAlias" excluded="true"/>
<Attribute name="sysCreateTime" excluded="true"/>
<Attribute name="sysCreateUser" excluded="true"/>
<Attribute name="sysEditUser" excluded="true"/>
<Attribute name="sysEditTime" excluded="true"/>
</Attributes>

Restricting the visibility of subject areas using privilege names

As of NexJ CRM 22.03, you can control which reports can be viewed by users in the UI, and which subject areas are available for selection when users are creating reports in the UI, by assigning privilege names to subject areas. You can use any privilege name that is valid in your CRM security model. If a user doesn't have the assigned privilege, the user will not be able to select the subject area in the UI or view all associated reports.

The following code example restricts the Contacts subject area to the rpt:ReportAdmin privilege, which means that if a user does not have the rpt:ReportAdmin privilege, the user will not be able to create a report for the Contacts subject area in the UI, or view any Contacts reports.

<SubjectArea caption="ids.subjectArea.Contacts" class="Person" icon="icon:person" iconColorIndex="2" privilege="rpt:ReportAdmin">

Filtering model attribute timestamps as dates

For model attributes of type   timestamp   that are surfaced in subject area definitions, ad hoc reports leverage a   unit="date"   parameter. This means that despite the attribute being defined as a   timestamp   type in the model, ad hoc reports filter on it as if it's a   date   type. An example of this is the Date of Birth attribute for contacts. If this parameter isn't set, then the filtering logic will filter on this attribute based on what the model attribute type is defined as.

Extending subject areas through augments

You can extend the default subject areas provided by NexJ by using subject area augments. Subject area augments are individual metadata files with the .sarea extension. Create subject area augments in an augments subdirectory within your subject areas subdirectory in your project's meta directory.

The following is the source for an example subject area augment CallRecordsForAugTest.sarea, which extends the Call Records subject area. The augment is seeded into the <project>/meta/subjectareas/augments/ directory.

<Augment for="<moduleName>:CallRecords">
    <SubjectArea caption="Call Recordings" icon="icon:call" iconColorIndex="10">
       <Associations>
          <Association name="Creator" caption="Created by" path="(@ creator)"/>
          <Association name="Author" caption="Authored by"/>
       </Associations>
    </SubjectArea>
</Augment>

In this snippet:

• for defines the subject area you want to augment. You must enter the full metadata name, as shown in the example.

• caption is the new caption for the subject area.
• icon is the new Material Design icon for the subject area.
• iconColorIndex is the new color code for the subject area icon.
• Associations define the association updates that you want to make to the subject area.
  
  • name is a unique name to identity the association in the context of the subject area. If an association with this name exists, the existing association is modified. If an association with this name does not exist, the new association is added.
  • caption is the field name that will display in the reporting interface.
  • path defines how to get to the association from the subject area's metaclass. Specify the path if you are adding a new association. Do not enter a path for an existing association. Modifying an existing association's path will result in an error during metadata loading.

For more information about each of these elements, see Creating subject areas.

After making your changes, save your work and restart the server.

Augments can also be used to remove subject areas from ad hoc reports. For example:

<Augment for="report:CallRecords">
    <SubjectArea enabled="false"/>
</Augment>

Extending a model to display calculated attributes

Ad hoc reports only display persisted attributes. By default, they do not display calculated attributes. You can extend the metaclasses in your model to add persistence to calculated attributes so such attributes are also available for selection in ad hoc reports.

Metaclasses are extended by creating class aspects in the Financereport project. For more information about class aspects, see Managing model behavior using aspects and augments .

The Financereport project includes extensions to the NexJ CRM model that are required for ad hoc reporting. You can use this project to extend your NexJ CRM model.

To extend a metaclass:

1. If you already have a class aspect that pointcuts the metaclass you want to add attributes to, skip to step 2. Otherwise, create a new class aspect.
   • Define the aspect attributes, ensuring that all the attributes:
     
     • Are prefixed with report:.
     • Have a value defined.
     • Are not required or static.
     • Have a caption defined. The caption displays in the reporting interface as the field name.
   • Persist the aspects, keeping the following in mind:
     • The data source must be the same as that of the class you are extending.
     • The primary table must be an aspect table. It must be in an aspect data source that pointcuts the data source of the original class.
     • If a data source aspect that pointcuts the class already exists, you can reuse it. Otherwise, create a new data source aspect.
     • If a table aspect already exists, you can just reuse it and add the new columns to this aspect. Otherwise, create a new table aspect.
   • Add the bireport:RecalculateReportAttributes facet to the aspect. For information, see Using facets to enhance metadata.

2. Create the following upgrade steps:

   

   Upgrade steps are created in the Persistence layer and Upgrades tab in NexJ Studio.

   1. If you added a new data source aspect, create a corresponding data source augment. The data source augment does not require any configuration.
   2. If you added a new table aspect, perform an Apply Table Aspect step ( Table Upgrade Step > ApplyTableAspect ). When doing so:
      • Select the data source of the class you are extending.
      • In the Name field, enter the name of the aspect table.
      • In the Pointcut field, specify the name of the table that the aspect pointcuts.
   3. If you added new columns to a table aspect, perform a Create Column step ( Column Upgrade Step > CreateColumn ) for each column that you added. When doing so:
      

      • Select the source datasource for the class you are extending.

      • In the Name field, enter the name of the aspect table.

   4. Set up recalculation of the added attributes and then trigger the recaculation. To do this, perform an Execute Upgrade step ( Misc Upgrade Step > Exec ) twice, selecting bireport:DefaultRelational as the data source and using the following scripts in sequence.

      1. This script schedules the class attributes to be recalculated upon server seeding. Create a separate insert statement for each class being extended.

         CODE

         insert into ${table:RecalculateReportAttribute} (id, className, attributeNames, statusCode) values(${guid()}, '<metaclass>', '<attributeName1,attributeName2>', 'UPGRADE');
         

      2. This scripts sets the bireport loaded preperty to false so that the database is reseeded upon server restart.

         CODE

         update ${table:Version} set loaded = 0 where namespace = <nexj:model:dai:bireport>;

3. Stop the server.
4. Update the model, and upgrade the NexJ CRM and bireport databases.

5. If you have added attributes that need to be periodically recalculated independent of changes to the model, add a bireport:cron facet to the attribute. An example of such an attribute is Open Days, which indicates the number of days an opportunity has been open and needs to be calculated on a daily basis.
   The following table shows the time units that you can use in the facet value. Recalculation occurs at the default values if the value is left unspecified and if a value for a larger time unit is specified.

   Time unit	Description	Valid values	Default value
   minute	The minute value at which the recalculation must occur.	0 to 59	0
   hour	The hour value at which the recalculation must occur.	0 to 23	0
   day	The day of the month value at which the recalculation must occur.	1 to 31	1
   month	The month of the year value at which the recalculation must occur.	1 to 12
   dayOfWeek	The day of the week value at which the recalculation must occur.	0 to 6

   The values are specified as shown in the following examples:

   • To recalculate the attribute daily at midnight, add the facet (bireport:cron ((minute . 0) (hour . 0)))

   • To recalculate the attribute every Saturday at midnight, add the facet (bireport:cron ((minute . 0) (hour . 0) (dayOfWeek . 6))) 

   • To recalculate the attribute at the beginning of every month, add the facet (bireport:cron ((minute . 0) (hour . 0) (day . 1))) 

   • To recalculate the attribute at the beginning of every year, add the facet (bireport:cron ((minute . 0) (hour . 0) (day . 1) (month . 1))) 

6. Start the server. A message similar to the following displays in the log.

   CODE

   Started recalculating reporting attributes for <Entity> : #(report:<fullName> report:<entityStatus>)
   Completed recalculating reporting attributes for <Entity> : #(report:<fullName> report:<entityStatus>)

The calculated attributes in the model are persisted and will now display in the reporting interface.

Enabling reporting on entitlements

Data entitlements (referred to as entitlements) constrain the NexJ CRM data a user can view based on their assignments in the organizational hierarchy. Entitlements vary based on the implementation of the hierarchical access model in an organization, and typically include rep codes, branches, divisions, and subfirms. For more information about the Hierarchical Access Model, see  System and object level security.

The Entitlements fields available in various ad hoc reporting subject areas enable you to view entitlement information. For users, entitlement information refers to the information they are entitled to view. For other entities, such as contacts, entitlement information refers to who is entitled to view the entity's information.

In the default hierarchical access model provided with NexJ CRM, the ability to report on entitlements is pre-configured and the entitlements data is set up to be rebuilt at 2 AM each day. However, if you are using a custom hierarchical access model, you must convert the hierarchy data and its associations into a reportable structure and create a custom process to populate and update the data. 

Use the following example to enable reporting on entitlements when using a custom hierarchical access model:

1. Create a reportable hierarchy class with attributes for each level in the hierarchy. For example, if your hierarchy contains the sub firms, divisions, branches, and rep codes, the hierarchy class should contain the attributes subfirm, division, branch, and repCode.
2. Associate the hierarchy class with the appropriate classes. This will involve adding each class to which the hierarchy applies as an attribute to the hierarchy class. For example, if you want entitlements to apply to users and contacts, add the users and contacts attributes.  For more information about associated classes, see  Associated classes.
3. Implement a custom process to ensure the data is populated and kept up-to-date. You can set up the process to suit your requirement, for example, at a specific time each day, every few minutes or hours, or each time a contact is added or deleted.
4. Extend the relevant subject areas to include the hierarchy class as an associated subject area. This ensures that each level in the Hierarchical Access  Model is selectable as a field within the relevant subject areas. For information on extending subject areas, see Extending subject areas through augments.

Users can now report on entitlements using the Entitlements fields in the relevant subject areas.

Extending how long report data is available

After a user runs a report, temporary data is stored for the report. By default, this data is available for a maximum of 30 days. Therefore, users can view a report directly or through shared report links for a maximum of 30 days before they have to re-run the report to view data in the report. To modify this default setting, update the reportExpiryAfterDays attribute in the bireport:ReportExpiryBatchJob class, which sets a time to live (TTL) limit after which the data is deleted.

Configuring currency conversion

To allow for monetary value conversions to different currencies on the system, see Configuring currency conversion.