Troubleshooting

When working with Advanced Reporting, you may encounter the following issues:

Domain is Corrupted

Symptom

The Advanced Reporting module is not working as designed when custom fields are added to domain. The problems include unable to open existing reports and dashboards.

Solution

• Restore the backup security and domain design files.
• If you did not back up the security and domain design files, contact Support to restore default settings. All the data such as custom fields and Ad Hoc views that you created are lost when you restore default settings.

Error While Adding Fields to Ad Hoc View

Symptom

Error While Saving Ad Hoc View or Report

Symptom

While saving the ad hoc view or report with a long name, the following errors are displayed:

• • For Ad Hoc View - Server Error: We apologize but something's gone wrong.
  • For Reports - An unexpected error prevented JasperReports from saving the report. Contact your administrator.

Solution

Ensure that the ad hoc view or report name does not exceed more than 94 characters.

Error While Opening Report

Symptom

Solution

Domain Security File Is Invalid

Symptom

When I click Submit in the Edit Domain page after updating the domain, I receive an error stating that the domain security file is invalid.

Solution

This error occurs because of the mismatch of data set names with their corresponding Join Trees in the security file and domain. In the domain, the data sets are available under Display, Resources, Join Trees. 

To fix this issue, download and open the security file in your local computer. Compare the data set in the Join Trees between the security file and Resources pane. If the data sets are not matching with Join Trees, update the Join Trees in the security file to match with the Resources pane.

Follow these steps:

1. Click and download the security file custom_cloud_sm_security.xml from the Edit Domain, Optional Information, Security File to your local computer.
2. Click Edit with Domain Designer and access Domain Designer, Display tab. The Display tab opens with the list of Join Trees on the left pane under Resources.
3. Open the downloaded security file in a text editor such as WordPad.

4. Expand the Join Trees under Resources pane. Verify that the table names in all the Join Trees section and in the security file are same.
   For example, consider the following security file code:

   <resourceAccessGrantList id="Tasks_FilterList" label="Tasks Filter List" resourceId="JoinTree_1">
   <resourceAccessGrants>
   <resourceAccessGrant id="TaskSliceFilter"><principalExpression>(authentication.getPrincipal().getAttributes().any{
   it.getAttrName() in ['nsd_slice']})</principalExpression> 
   <filterExpression>(testProfileAttribute(dbo_VITPM_PROJECT_TASKS.slice,'nsd_slice'))</filterExpression> 
   </resourceAccessGrant>
   <resourceAccessGrant id="TaskMemberFilter">
   <principalExpression>(authentication.getPrincipal().getAttributes().any{it.getAttrName() in ['nsd_UserId']})</principalExpression> 
   <filterExpression>((groovy('authentication.getPrincipal().getRoles().any{it.getRoleName()=="ROLE_CSM_ADMINISTRATOR"}') == 'true') or
   CHARINDEX(dbo_VITPM_PROJECT_TASKS.members,(groovy('authentication.getPrincipal().getAttributes().find{it.getAttrName()=="nsd_UserId"}.attrValue')))>=0)</filterExpression> 
   </resourceAccessGrant>
   <resourceAccessGrant id="Task_OBSFilter">
   <principalExpression>
   <![CDATA[authentication.getPrincipal().getAttributes().any{it.getAttrName() in ['nsd_OBSEnabled'] && it.getAttrValue() in ['True']}]]> 
   </principalExpression>
   <filterExpression>(OBS_PRJ_TASKS.org_id in (dbo_VITPM_PROJECT_TASKS.org_id) and testProfileAttribute(OBS_PRJ_TASKS.user_id,'nsd_UserId'))</filterExpression> 
   </resourceAccessGrant>
   </resourceAccessGrants>
   </resourceAccessGrantList>

   The resourceId specifies the Joint Tree name as JoinTree_1 and the filterExpression specifies the table name as dbo_VITPM_PROJECT_TASKS. Verify that the table name dbo_VITPM_PROJECT_TASKS matches the table name in JointTree_1 under Resources pane.

5. If the table names of the security file do not match with the Join Trees in the Resource pane, edit the security file accordingly.
   For example, consider the table name dbo_VITPM_PROJECT_TASKS is in the JoinTree_1 of the security file. In the Resources panel of the Display tab, the dbo_VITPM_PROJECT_TASKS table is available in JoinTree_2. To match the information, edit the security file and rename the resourceId value from JoinTree_1 to JoinTree_2.
   Change all the resourceId values in the security file, accordingly.  

6. Save your changes and close the security file.

7. Click Cancel to navigate back to the Edit Domain page.

8. In the Edit Domain page, click Change corresponding to the security file. Browse and select the security file from your local computer.

9. Click Submit. The old security file is replaced with the updated file and issue of invalid domain security file is resolved.

Requester's Organization Field Is Not Displaying Data

Symptom

When I add the Requester's Organization field to a report, the subsequently generated report does not display data under Requester's Organization field.

Solution

This error occurs when the Requester's Organization field is not mapped properly to the custom domain schema that is shipped with a prior release. To resolve this error, do the following:

1. From the Advanced Reporting workspace, click Action, Edit Domain.
   The Edit Domain page opens in a new page.
2. Click Edit with Domain Designer.
3. Delete the existing Requester's Organization field from the display set:
   1. Go to the Display tab and expand the Tickets set from Sets and Items panel.
   2. Select the field Requester’s Organization and click Delete Item button.
      If this field is already in use, a confirmation message is displayed.
   3. Click OK.
      The Requester's Organization field is successfully deleted from the display set.
4. Add the proper Requester's Organization field to the display set:
   1. From the Resources panel, expand the join tree with dbo_VAPP_ITEM table.
   2. Select and move the person1_org_name field to the Tickets set in Sets and Items panel.
   3. Select the added field and click Edit from Properties panel.
   4. Enter the following information and click Save.
      • Label: Requester's Organization
      • ID: person1_lvl1_org_name
      • Description: Requester's Organization
      • Label Key: set_person1_lvl1_org_name_lbl
      • Description Key: set_person1_lvl1_org_name_desc
5. Click OK, and then Submit.
   The updated Requester's Organization field is added to the display set. The Ad hoc views and reports that already using this field displays proper data.

Error While Converting Date Data Type

Symptom

When I create an Ad Hoc view, an error is displayed while adding the custom DateTime column.

Solution

The custom fields are saved as a string in the database while EPOCH_TO_UTC_DATETIME expression takes the input as an integer. Use the following expression to convert the custom DateTime field:

EPOCH_TO_UTC_DATETIME (str2int(dbo_VAPP_SERVICE_REQUEST_ATTRIBUTES_PIVOT.scheduling_date))

Unable to Add Custom Fields to Ad Hoc Views

Symptom

An error is displayed when I add custom fields that contains accented characters to Ad Hoc views.

Solution

Perform the following steps to resolve this issue:

1. From the Advanced Reporting workspace, click Action, Edit Domain.
   The Edit Domain page opens in a new page.
2. Click Edit with Domain Designer. The domain designer page opens.
3. Click Display tab. From the Sets and Items pane, expand the sets and select the field with accent characters.
   The details of the fields are displayed in the Properties pane.
4. From the Properties pane, click Edit and remove the accented characters from the ID field.
5. Save the changes. The field is ready to use on a view/report.

Duplicate Data Displayed in Reports for Analyst

Symptom

Duplicate records are displayed in the report when the organization-based security is enabled for the Analyst user.

Solution

To resolve this error, remove Joins with Organization views by adding a filter query to the security file.

Follow these steps:

1. Click Edit Domain, Optional Information, Security File and download the security file custom_cloud_sm_security.xml to your local computer.
2. Open the downloaded security file in a text or XML editor such as WordPad or XML Editor.
3. Perform the following steps in the security file:
   Note: If any of the search items such as Project_OBSFilter and TimeLog_OBSFilter are not available in the file, move to the next search item.

   1. Search for Ticket_OBSFilter. Go to the FilterExpression tag and replace the code with the following code:

      <filterExpression>AddSqlTemplateFunction('Exists(select 1 from vorg_contact_group_derived_relations v where v.org_id in (dbo_VAPP_ITEM.PERSON1_ORG_ID,dbo_VAPP_ITEM.PERSON2_ORG_ID) and v.user_id = LOGGED_IN_USER_ID)','authentication.getPrincipal().getAttributes().find{it.getAttrName()== "nsd_UserId"}.attrValue')</filterExpression>

   2. Search for CI_OBSFilter. Go to the FilterExpression tag and replace the code with the following code:

      <filterExpression>AddSqlTemplateFunction('Exists(select 1 from vorg_contact_group_derived_relations v where v.org_id = dbo_VSCR_CI.org_id and v.user_id = LOGGED_IN_USER_ID)','authentication.getPrincipal().getAttributes().find{it.getAttrName()== "nsd_UserId"}.attrValue')</filterExpression>

   3. Search for Asset_OBSFilter. Go to the FilterExpression tag and replace the code with the following code:

   4. Search for Project_OBSFilter. Go to the FilterExpression tag and replace the code with the following code:

      <filterExpression>AddSqlTemplateFunction('Exists(select 1 from vorg_contact_group_derived_relations v where v.org_id = dbo_VITPM_PROJECTS.org_id and v.user_id = LOGGED_IN_USER_ID)','authentication.getPrincipal().getAttributes().find{it.getAttrName()== "nsd_UserId"}.attrValue')</filterExpression>

   5. Search for Task_OBSFilter. Go to the FilterExpression tag and replace the code with the following code:

      <filterExpression>AddSqlTemplateFunction('Exists(select 1 from vorg_contact_group_derived_relations v where v.org_id = dbo_VITPM_PROJECT_TASKS.org_id and v.user_id = LOGGED_IN_USER_ID)','authentication.getPrincipal().getAttributes().find{it.getAttrName()== "nsd_UserId"}.attrValue')</filterExpression>

   6. Search for PA_OBSFilter. Go to the FilterExpression tag and replace the code with the following code:

      <filterExpression>AddSqlTemplateFunction('Exists(select 1 from vorg_contact_group_derived_relations v where v.org_id = dbo_VITPM_ASSOCIATIONS.org_id and v.user_id = LOGGED_IN_USER_ID)','authentication.getPrincipal().getAttributes().find{it.getAttrName()== "nsd_UserId"}.attrValue')</filterExpression>

   7. Search for TimeLog_OBSFilter. Go to the FilterExpression tag and replace the code with the following code:

      <filterExpression>AddSqlTemplateFunction('Exists(select 1 from vorg_contact_group_derived_relations v where v.org_id = dbo_VITPM_TASK_TIMELOG.org_id and v.user_id = LOGGED_IN_USER_ID)','authentication.getPrincipal().getAttributes().find{it.getAttrName()== "nsd_UserId"}.attrValue')</filterExpression>

4. Save your changes and close the security file.
5. In the Edit Domain page, click Change against the corresponding security file. Browse and select the security file from your local computer.
6. Click Submit.
   The old security file is replaced with the updated file and issue of duplicate records is removed for Analyst user.