WebI: Report Creators - Having issues with formulas

Issue:
On WebI reports, variables that use old context calculation methods do not aggregate correctly, which causes invalid summary totals, counts, and averages, and invalid hidden (or unhidden) sections.

Cause:
This is caused by the change made to the Calculation Engine by the vendor, SAP BusinessObjects. The issue seems to be specific to cases where formulas or variables do not have section object(s) qualified in the calculation, either because of parentheses in the wrong place or missing contextual references.  However, variables work correctly if there is a "Where" statement involved in the calculation (even if the section is not fully qualified).

Resolution:
Make sure contextual references are correct and that parentheses are located in the correct positions in formulas. Some examples below show you how this issue was corrected on some published reports:

Example 1:
-Issue: WebI report HR10330 has a variable used to hide sections.
-Resolution: The parentheses should be around the Count object

  • Before: =If (Count([Dedn-Minus].[Posn 1] In ([Dedn-Minus].[Spriden Id])) > 1) Then 1
  • After: = If Count([Dedn-Minus].[Posn 1]) In ([Dedn-Minus].[Spriden Id]) > 1) Then 1

Example 2:
-Issue: WebI report UNH HR540 has 2 variables used to hide sections.
-Resolution: Add a section qualifier (For Each <section name>)

  • Before: =If Sum([Gross Earnings])  <> 0 Then 1
  • After: =If Sum([Gross Earnings]) ForEach ([v_section_grant]) <> 0 Then 1   (Note - In the UNH HR540, this equation is also rounded)
  • Before: =If Round(Sum([Gross Earnings]);2) <> 0 Then 1
  • After: =If Round(Sum([Gross Earnings]);2) ForEach ([v_section_pidm_posn_suff]) <> 0 Then 1

Example 3:
-Issue: WebI report SIS20515 has a variable used to hide a section (sections are set to hide when variable is null).
-Resolution: Add a section qualifier ("For Each" or "In") to make the variable work.  The "Before" formula evaluates to null for each section so all sections were hidden.  The "After" formula evaluates to "1" for sections where the sum is null, so section hiding then works as expected.

  • Before: =If IsNull(Sum([v_null_line_and_seats])) Then 1
  • After: = If IsNull(Sum([v_null_line_and_seats]) In ([v_section_subj_crse])) Then 1

We could also have updated the formula to =If IsNull(Sum([v_null_line_and_seats]) ForEach ([v_section_subj_crse]))
    
Example 4:
-Issue: WebI report SIS82150 had two issues: 1) the averages for values in the table footers were incorrect on the Applications, Holds, Admits, Paid and Act Dep/Enroll tabs; 2) the averages in the table footer and break footer were incorrect on all tables on the Associates tab.
-Resolution 1 : In the below example for the section, in the "Before" example the section was qualified; however, the formula still does not work correctly. This is most likely due to missing parentheses and the order in which the formula was being evaluated.  The "After" example explicitly defines the input contexts to be used and in what order.

  • Before: =[Apps SATV (Avg)] In ([v_section_student_type];[v_section_college];[Term Snapshot Date])
  • After: =([Apps SATV (Avg)] ForEach ([Term Snapshot Date])) In Section

-Resolution 2: "HS GPA" is a variable that already used a Where clause.  It evaluates correctly in without any added context, so all context was removed.

  • Before: =[v_hs_gpa_avg_apps_styp1] In ([v_section_student_type];[v_section_college];[Term Snapshot Date])
  • After: [v_hs_gpa_avg_apps_styp1]

*Need help?*
If you need help please contact the Center for DATA WebI team at https://td.usnh.edu/TDClient/Requests/ServiceDet?ID=142, or by calling the IT Service Desk at 862-4242.

Print Article