SQL functions

The Apply To class for the SQL function is Embed-UserFunction, which differentiates this function from other Rule-Alias-Function instances. In the App Explorer, you can filter on the Embed-UserFunction class to see only SQL functions.

As a Lead System Architect (LSA) who helps FSG enhance its Booking application, your task is to capture every customer’s opinion of how well FSG hosted an event at a certain venue. The customers rate FSG’s performance by entering a number between zero and ten. The numbers zero and ten represent the minimum and maximum net promoter score (NPS). This range of values consists of the following categories:

• 0-6: Detractors
• 7-8: Passives
• 9-10: Promoters

After capturing the NPS, FSG wants you to define a report that produces output similar to the following example. FSG wants the report to be grouped by venue.

Do not use decision rules to precompute the category. This approach is not optimal because of the following reasons:

• Creates data redundancy, which makes it difficult to keep the data accurate when the original value changes.
• Inhibits “what if” analysis.

You can use other ways to produce this output without using decision rules:

• Use a custom SQL function (the SUM function) three times to determine whether the NPS falls within a certain range.
• Use a parameterized subreport (the COUNT function) three times to count whether the NPS falls within a certain range.

Recommended approach A

The best approach is to define an SQL function with three integer parameters (such as value, range start, and range end). The SQL function uses the following code:

The report labels each column according to the categories that it represents (such as Promoters, Passives, or Detractors). 

Steps

1. The FSG enterprise layer define an abstract class FSG-Data-Feedback class in the FSG ruleset. The Booking team uses pattern inheritance to extend the abstract FSG-Data-Feedback class to a concrete class FSG-Data-Feedback-Event. The Booking team creates the FSG-Data-Feedback-Event class within the BookEvent ruleset that the Booking application owns.
   The FSG-Data-Feedback-Event class contains the EventRef property, the value of which is equal to the pyID value of the FSG-Booking-Work-BookEvent case.
2. To maximize reuse potential, define a report that uses the custom SQL function in FSG-Data-Feedback-Event, instead of FSG-Booking-Work-BookEvent.
   The report definition INNER JOINs FSG-Data-Feedback-Event to FSG-Booking-Work-BookEvent . Inner join condition is .EventRef =  EVENT.pyID, "EVENT" being a join alias. 
3. The report definition also INNER JOINs the  FSG-Booking-Work-BookEvent to FSG-Data-Venue. Inner join condition is using .EVENT.VenueGUID = VENUE.pyGUID, "VENUE" being a join alias. 
4. The report definition is GROUPED BY VENUE.Name, the remaining columns in the report using aggregate functions, the first being COUNT EVENT.pyID. 
5. The three aggregate columns on the right use the SUM() function. 
6. Ensure that the input to each SUM() function is the custom SQL function. The first argument to that custom SQL function is the FSG-Data-Feedback .NetPromoterScore property. The second and third arguments to the custom SQL function are literal values, such as 9 and 10, or values that the JOIN function to the reference table obtains.

Recommended approach B

An alternative to approach A is to use a SELECT subquery to retrieve the start and end range values from a grouping of live data rows as shown in the following example:

This sample subquery assumes that a reference table is defined with the RANGE_GROUP, RANGE_NAME, START, and END column names.

Check your knowledge with the following interaction: