IRR calculation in SSRS

Question

I'm attempting to develop a report in SSRS with a computed IRR.  I have converted my data stream to Double and have included an IRR function in the report.  I am getting the following warning.

Warning 1 [rsRuntimeErrorInExpression] The Value expression for the textrun ‘IRR.Paragraphs[0].TextRuns[0]’ contains an error: Unable to cast object of type 'System.Double' to type 'System.Double[]'. C:\Users\####\Documents\Visual Studio 2008\Projects\#####\SSRS1\SSRS1\Report1.rdl 0 0 

Does anyone know how to resolve this?

 

Answer 1

Hi There

Thanks for your posting. Can you please put your code here for calculating IRR. It seems like you are trying to cast double with the array of double.

Please pot your code so that someone might be able to help up out

Many thanks

Syed Qazafi Anjum

You can also try to use this code on SQL Server side

CREATE FUNCTION dbo.Calculate_IRR(@commaseparatedStringIDs varchar(8000), @guessAmount decimal(30,10))
RETURNS decimal(30,10)
AS
BEGIN
  DECLARE @t_IDs table(id int identity(0,1), value decimal(30,10))
  DECLARE @strID varchar(12), @sepPos int, @NPV decimal(30,10)
  set @commaseparatedStringIDs = coalesce(@commaseparatedStringIDs + ',', '')
  set @sepPos = charindex(',', @commaseparatedStringIDs)
  WHILE @sepPos > 0  BEGIN
     SET @strID = LEFT(@commaseparatedStringIDs, @sepPos - 1)
     INSERT INTO @t_IDs(value) SELECT (CAST(@strID AS decimal(20,10))) where isnumeric(@strID)=1
     SET @commaseparatedStringIDs = RIGHT(@commaseparatedStringIDs, DATALENGTH(@commaseparatedStringIDs) - @sepPos)
     set @sepPos = charindex(',', @commaseparatedStringIDs)
  END
  set @guessAmount = case when isnull(@guessAmount, 0) <= 0 then 0.00001 else @guessAmount end
  select @NPV = SUM(value/power(1+@guessAmount, id)) from @t_IDs
  WHILE @NPV > 0
  BEGIN
    set @guessAmount = @guessAmount + 0.00001
    select @NPV = SUM(value/power(1+@guessAmount, id)) from @t_IDs
  END
  RETURN @guessAmount
END
go
-- values to compare with excel (note that for excel 0.00001 is 0.1%)
select dbo.Calculate_IRR('-90,13,14,15,16,17,18', 0.00001) as irr