Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

Support Article

Average summary function for SLA aliases return 0 w/ SQL Server

SA-24906

Summary



HFix-27382 corrects an issue where SLA function aliases return null instead of zero.

Reference: 
https://pdn.pega.com/support-articles/timeliness-function-aliases-break-average-computation-null

Despite having this fix, when performing an average summary function on this column to calcuate an average percentage it again always returns 0 on the "final grouping level"


Error Messages



Not Applicable.


Steps to Reproduce

  1. Ensure to have the newest version of the Function Alias pxSLAHistoryTimeliness (Pega-Reporting:07-10-18) as provided by the hotfix mentioned above. The new function alias returns a 0 rather than NULL when it does not meet the condition. This is what enables us to calculate an average and return a percentage.
  2. Start with Out-of-the-box (OOTB) report definition pyAgeByFlowAssignment.
  3. Add an extra column for each of the three timeliness calculations in order to return the percentage.

    .pyTaskName
    .pxSLAHistoryTimeliness [within goal] SUM
    .pxSLAHistoryTimeliness [within goal] AVERAGE
    .pxSLAHistoryTimeliness [past goal] SUM
    .pxSLAHistoryTimeliness [past goal] AVERAGE
    .pxSLAHistoryTimeliness [past deadline] SUM
    .pxSLAHistoryTimeliness [past deadline] AVERAGE
    Total COUNT
     
  4. Run the report with cases which have gone past assignment SLA.

 


Root Cause



This issue is specific to SQL Server and not a Pega defect.Doing an average of values for 0 and 100% are fine, but everything in between faces this issue. This is key in understanding what is going wrong.

In SQL Server when user performs an average operation with an integer, it will only ever return another integer. So if user performs the mathematical average of say [1, 1, 0, 0] that would be 0.5 and since 0.5 is not an integer, SQL Server will effectively truncate the decimal portion and return the remaining whole integer (in this case 0). That is the root cause of the issue.

Other databases (Oracle, Postgres) handle this differently and will return a "proper" average as expected.

Resolution



As a local change one can make to your report to work around this SQL Server specific behavior.

In the average calculation columns user will be effectively adding two functions together.

One function is the exact same function alias that have been using so far. The other "function" will be a literal decimal.

In other words:
  1. Replace the main function alias and choose the "Add" function
  2. Make sure to choose the "correct" Add. You will want the numeric value version, NOT the integer version.



For the inputs to the Add function you will provide
 - A literal decimal of 0.0
 - Your original function alias which calculates SLA deadlines, etc. (Pass the same params are before, and this will still return 1 or 0 as usual)


 
After replacing the column with this new function, make sure to reset the summarize field back to Average and format as needed.

This works, because now there is "faked" out SQL Server to perform an average of floating point numbers, so the average will not truncate to a whole integer.
Suggest Edit

Published June 30, 2016 - Updated October 8, 2020

Did you find this content helpful? Yes No

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us