I wish I could find my original implementation but I will explain what I remember doing for now. I had some help from a colleague on this one and we tried a number of methods like calculating the IRR in SQL Server business objects but the accountants wanted the one that matched their excel spreadsheets so this one was what was settled on. I don't like it from the standpoint of it being a black box with no one really knowing how it is calculated. In fact there are a number of different methods to calculate IRR however only one poorly documented Excel calculation makes the accountants happy.
As I remember my final version which did not use sub-reports I used the FOR XML SQL Server statement to build a comma separated list of values(CSV) column for each record. The SSRS report contained code to pass the CSV column to a function which split the values into a string array and converted the array into an array of the type the IRR function accepts which I believe is a double.
I'm sure there is a more elegant way but I think I just created a function to create a loop to convert and pass the values to the correct array type. Then I passed the correct array type to the IRR function. The IRR function needs to be inside a wrapper function containing proper error checking and return a zero on legitimate errors. The IRR function is very picky!
Found the code! There is probably a more eligant way but this works.
Public Shared Function CSVToArray(strInputString As String) As Double()
Dim astrSplitItems() As String
Dim astrSplitItems2() As Double
Dim intX As Integer
Dim intMax As Integer
if Len(strInputString) = 0 Then Exit Function
astrSplitItems = Split(strInputString, ",")
intMax = UBound(astrSplitItems)
ReDim astrSplitItems2(intMax)
For intX = 0 To intMax
astrSplitItems2(intX) = CDbl(astrSplitItems(intX))
Next
CSVToArray = astrSplitItems2
End Function
Public Shared Function MyIRR(strInputString As String) As Double
Dim intNumItems as integer
Dim ColumnNamesList As Double()
ColumnNamesList = CSVToArray(strInputString)
on error resume next
MyIRR = ((1 + irr(ColumnNamesList, 0.05))^4)-1
on error goto 0
End Function
As I remember my final version which did not use sub-reports I used the FOR XML SQL Server statement to build a comma separated list of values(CSV) column for each record. The SSRS report contained code to pass the CSV column to a function which split the values into a string array and converted the array into an array of the type the IRR function accepts which I believe is a double.
I'm sure there is a more elegant way but I think I just created a function to create a loop to convert and pass the values to the correct array type. Then I passed the correct array type to the IRR function. The IRR function needs to be inside a wrapper function containing proper error checking and return a zero on legitimate errors. The IRR function is very picky!
Found the code! There is probably a more eligant way but this works.
Public Shared Function CSVToArray(strInputString As String) As Double()
Dim astrSplitItems() As String
Dim astrSplitItems2() As Double
Dim intX As Integer
Dim intMax As Integer
if Len(strInputString) = 0 Then Exit Function
astrSplitItems = Split(strInputString, ",")
intMax = UBound(astrSplitItems)
ReDim astrSplitItems2(intMax)
For intX = 0 To intMax
astrSplitItems2(intX) = CDbl(astrSplitItems(intX))
Next
CSVToArray = astrSplitItems2
End Function
Public Shared Function MyIRR(strInputString As String) As Double
Dim intNumItems as integer
Dim ColumnNamesList As Double()
ColumnNamesList = CSVToArray(strInputString)
on error resume next
MyIRR = ((1 + irr(ColumnNamesList, 0.05))^4)-1
on error goto 0
End Function
No comments:
Post a Comment