PT 8.61/8.62

Randall Groncki

Summary

I’ve encountered two issues with dates in PeopleSoft Insights Dashboards:

• Null dates in data tables
• Displaying the correct date rather than the UTC date

Insights Data tables have issues displaying null dates.  Most likely, the entire rows with a null date will not display on your Insights Data Table. 

Another issue with dates in insights is the UTC date displaying in Visualizations rather than the actual field date.   Depending on the situation, the date displayed can be off by a day.

We’re going to discuss work arounds for these two issues using scripted fields.

Scope

The scope of this tutorial is only to demonstrate specific date issues in Insights Visualizations.  How to create data, visualizations and dashboards is beyond this scope.  This demonstration assumes the user understands the basic process and navigation of configuring Insights dashboards both in the PeopleTools and Insights environments.

Null Dates and Data Tables

Not all date fields in PeopleSoft are required.  Date fields not populated with a value are assigned as a null on the database table.   The PeopleSoft application can handle these just fine: Null is an expected valid value.  But Insights Visualizations are expecting a non-null value.

This is a screenshot of an Insights Data table with five rows.   Two of those rows have null Position Entry Date fields.   These rows are not shown on the table

The quick fix to display the rows with null dates is to create a scripted field displaying the date as a string.  If the date is null, we put a value such as a dash or blank character in the field, so the displayed field is no longer null.

This is that same table showing the Position Entry Date Scripted into a string value

Scripting Date Fields to Strings

We’re going to use this same scripting solution for both our issues discussed in this post. 

Edit the Index Pattern

In the Insights Control Panel, Choose the Stack Management section.  Find and edit your Index Pattern.

I find it a good idea to copy the target field name to the clip board.

Add the Scripted Field

Click on the Scripted Fields tab and add a scripted field.

Set the Field Properties

Add the Painless Script

Test Your Script

Use Insights Test Facility to test out your script.

Click on the “Get help with the syntax and preview the results of your script” link at the bottom of the text block.

A popup window appears on the left side of your workspace.  Click on the “Preview Results” tab to see your script working (or not).

We can see from the returned Json document that the script is working correctly and returning a string representation of the populated and null dates.

Save (Create Field)

Remember to save your work by clicking on the “Create Field” button at the bottom of the popup window.

Use Field in Visualization

Now we can go back to our visualization and use our new scripted field in our data table. 

Add a split row and use our new scripted field for the column.

Click Update and we can see our new column on the data table. 

*As a note, see that I also turned off the Original Position Entry Date field so the rows with the null Position Entry Dates will appear.

UTC Dates and Field Dates

I noticed the dates in my visualizations and the dates in PeopleSoft were sometimes off by one day.

Here is an example from HCXM PUM 51

Employee KU0106 current JOB EFFDT field value is 5/28/1999

Looking at this employee’s job row, we see the same value of 5/28/1999.

 However, looking at that same date field sent to OpenSearch for use in a Insights Data Table shows 5/27/1999

In Insights Discover Data tool, we can see the EFFDT for employee KU0015 is 2/1/1999 @ 19:00 as opposed to their EFFDT from the table above being 2/2/1999.

The difference is that Insights is storing and using the UTC Date value rather than our localized date value.  

Our users want to see the same dates on our dashboards as they see in the application.  The difference in the day can cause real problems with analysis and the resulting actions.

Showing the Application Date in Insights

We can show our localized date in the application using the same scripting as for the null dates:

• Create a Scripted Field
• Format the scripted field into a String field using Painless Date formatting functions
• Display the scripted field rather than the source field

Script

if (doc['Effective Date'].size() == 0) {
  return "-";
} else {
  ZonedDateTime zdt = doc['Effective Date'].value;
  return zdt.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
}

Using this scripted field in our data table with a side by side comparison to the original field, we see that scripted fields are one day later, but reflect the values in the PeopleSoft application.