PT 8.61/8.62

Randall Groncki

Summary

Insights dashboards use data tables to show the detailed data of the visualizations.  Being a visualization themselves, they respond to user filtering and selections on the dashboard. 

But they are not a built-in spreadsheet: they are not unlimited, or even big.   Depending on your data size, number of columns displayed and system configuration, you may see only the first few thousand rows (or less) in the data table.   Your filter may show 50,000 rows of data analyzed in the dashboard, but your data table shows way less than that.  

We quickly realized that not all the dashboard data rows are in that data grid.

Real estate on the grid is also a problem.  The more columns defined, the more the columns get pushed together with data and labels truncated and offset.

The users always want more, but it’s not pretty. And sometimes, not even feasible.

But we do have a possible solution: put a PSQuery link in the data grid that will show whatever data you choose to the user using keys from that data row

Scope

The scope of this tutorial is only to demonstrate how to add a PSQuery link in an Insights Data Table Visualization.  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.

Setting the Stage

For this demonstration, we already have all our dashboard objects working correctly.  We are going to use our existing dataset query to create a new user query. 

Here is our dashboard before any changes.

Create our new Query to be displayed by the Query Link

For this example, I copied the query used in the Search Definition.  This means the query link will show the same data as the data set.  More than likely, there are more fields in your data set query than displayed on the grid.

Remember that this is a link to Query Viewer.  The query doesn’t have to be the same data as the data set.  It can be whatever serves the business needs.   It does help if the query data is somehow related to the grid’s data.

Since I’m using a copy of that Search Definition Query:

• Remove the prompts for Last Update Time
• Remove the drilling URL

Add a prompt to the query to link it to the grid’s data row

I want my query to show the data for that grid rows employee ID, so I’m going to add a prompt on the EMPLID field.

In the Query we use to load data into Insights, add a new expression as a drilling URL

Going back to the Search Definition Query, add a new expression.

• Expression Type Drilling URL
• Choose “Query URL” from the list of options

Populate the fields with your new query.  Link the keys to the prompt.

Save your changes

Undeploy your Search Category

Before we can update our search definition, we need to Undeploy the Search  Category

Add your new Query field to the Search Definition

Navigate to your search definition and go to the “Map Search Attributes” page.

Find your new Query field and select it for indexing

Update your Search Category

Go to the Search Category and select the “Advanced Search Fields” page and click the “Save” button.  Your new Query field should appear in the list.

If it does not appear in the list, uncheck the “Auto detect common fields” and recheck it again and save again.  The field should now be on the list.

Save Again.

Redeploy your Search Category

Redeploy your Search Category so OpenSearch will understand the new field.

Build your Search Index

Send your data with your new Query link to OpenSearch

Update your Index Pattern for the Query Link

In Insights, Open your Index Pattern and click the Refresh icon.  This should add your new Query field to the Index Pattern

Find your new field in the Index Pattern and edit its attributes.

• Change the format to “URL”
• Change the type to “Link”
• Update the label to something descriptive like “Query”

Save your field.

Add your link as a new column in the data grid

Now back to your Data Table Visualization.

• Add a column to your grid (Split Rows)
• Aggregation in Terms
• Field is your new Query field
• Buckets = 1

Test

Test your Data table to ensure it works correctly.

Here is our new Query field in the Data Table

Clicking on the Query link opens a new browser tab where the query has run just for that employee in the data row.

What’s Next?

What Insights dashboards to you have in production right now that can benefit from embeding a PSQuery in a data grid to bring additional clarity and context to your users?