vbscriptbusiness-rulesleap-yearhyperion

Dim Leap Year, VBScript

I have a series of Ratios calculated in VBScript which incorporates the total days in the year. For 2016 we need to utilize "366" days instead of the normal "365."

I created a Dim variable to populate based on the year, but it's clear the script isn't working and instead zeros out.

VBScript:

'/ Adding YrDays to account for Leap Years'/
If POV_Year = "2012" OR "2016" OR "2020" OR "2024" OR "2028" Then 
    YrDays = "366" 
    Else YrDays = "365" 
End If

HS.Exp "A#Ratio007.C1#[None]" & DynC1 & " =  
((A#Tohinc" & Tops & " * (YrDays / A#numberdays.E#PVTB Admin))  
/ (A#Totass" & Tops &")) * 100"

When I upload the Business Rules into Oracle HFM, the VBScript uploads without errors but fails to calculate the ratio.

There is no account in HFM for YrDays as I want to calculate the value (YrDays) during the consolidation and calculation. I've verified POV_Year, Numberdays, and other values are loading properly.

Any idea what I might be doing wrong?

Software: Oracle HFM 11.1.2.3.500 VBScript Rule.rle file editing in Notepad++

Edit: This is the solution I went with: 

'/ Adding YrDays to account for Leap Years'/
SELECT CASE POV_YEAR
 CASE "2012", "2016", "2020", "2024", "2028"
 YrDays = 366
 Case Else
 YrDays = 365 
End Select

I also needed to change the way YrDays was referenced in the formula, I should have known it needed to be in Quotation marks to be referenced in the calculation. 

HS.Exp "A#Ratio007.C1#[None]" & DynC1 & " =  
((A#Tohinc" & Tops & " * ("& YrDays & "/ A#numberdays.E#PVTB Admin))  
/ (A#Totass" & Tops &")) * 100"
Solution

  • Your code might be better as a Select Case statement.

    Select Case POV_Year
    Case 2012, 2016, 2020, 2024, 2028
        YrDays = 366 
    Case Else
        YrDays = 365 
End Select

    However, there may be an easier method.

    '/ Adding YrDays to account for Leap Years'/
YrDays = DateSerial(POV_Year + 1, 1, 1) - DateSerial(POV_Year, 1, 1)
'alternate
YrDays = 365 - (POV_Year = 2012 Or POV_Year = 2016 Or POV_Year = 2020 Or POV_Year = 2024 Or POV_Year = 2028)

    I've remove the quoted numbers; numbers should stay as numbers. However "2016" does not equal 2016 so you might have to put them back or adjust the remainder of your code. You can concatenate a true number into a string.