HasDataPoint (PQL)

Returns the numeric expression value if the current datapoint contains the provided datapoint, otherwise returns null.

  • Returned Output: Numeric
  • Library: PQL \ Semantic \ Logical
  • Version: 2020.20.000
  • Compatibility: Pyramid Query Language (PQL) data sources

Syntax

HasDataPoint( <Data Point> , OPTIONAL <Data Point> )

* Click on the function's arguments above for more details on the input values.

Comments
  • The function is heavily used in setting up Conditional Formatting logic that is targeted at specific cell.
  • The first data point argument should provide a list of "anchor" members that are to be found in the "current" data point when the function is deployed in query. The anchors do NOT need to be a complete match for the logic to return true.
  • The second Data Point returns a numeric value if the match is true. If not supplied, it returns the cell's current value.
Using HasDataPoint in Conditional Formatting

To make the conditional formatting easier to configure, and applicable to a subset of data cells (rather than entire measures), user can employ the "HasDataPoint" function - which allows the user to designate which slice of the query result set are in or out of the logic "zone." The "anchors" provide a convenient way to designate the "zone" and the numeric options provide a convenient way to return the value to drive the conditional formatting logic.

Examples

In the simple grid below, we have sales figures by education and occupation. The Conditional Formatting rules used on this grid employ 2 functions (one for each rule).

The Background color rule (blue arrow) uses the following function

HasDatapoint(([customerProfile].[Education].[High School]), [measures].[transactions Sales])

The function checks every cell in the query to find out if its data points contain the data point (or "anchor") "High School". If it does, it returns the value "Sales" for each cell. Otherwise it returns empty. The effect is that only the High School related cells are included in the background color logic and the effect is applied accordingly.

 

The arrows rule (black arrow) uses the following function, with a similar effect. Here, the function is focusing on the Manual Occupations only. In effect, arrows are only included in the CF logic for these data points. All other data points are flagged as empty and are ignored.

HasDatapoint(([customerProfile].[Occupation].[Manual]), [measures].[transactions Sales])