asked on

YEARFRAC equivalent for SSRS?

I am trying to replicate the YEARFRAC feature in EXCEL into SSRS.

Here's the expression in my report, but it's not returning the same results as in Excel. I'm using DateDiff...

=ReportItems!TotalValue.Value/DateDiff("yyyy",Min(Fields!order_datefulfilledValue.Value),Max(Fields!order_datefulfilledValue.Value))

Open in new window

I believe the results would be in decimals.... the YEARFRAC in Excel basically gets the fraction of a year between two dates

Jim Horn

For the benefit of those SQL Server experts that are not familiar with the Excel function YEARFRAC, can you provide us with 'before and after' sample data of what you are trying to pull off here?

Thanks in advance.

teknovation

ASKER

Hi Jim, please see below.

=ReportItems![b]TotalValue[/b].Value/DateDiff("yyyy",Min(Fields![b]order_datefulfilledValue[/b].Value),Max(Fields![b]order_datefulfilledValue[/b].Value))

Open in new window

Sample debug code:
21/YEARFRAC("12/20/2012","01/15/2014")

10.500 is the result using the expression above when it should be 19.636

The two dates are 12/20/2012 and 01/15/2014
The total value is: 21

teknovation

ASKER

...anyone?

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

teknovation

ASKER

thanks ill try it. but its for crm dynamic which is fetch xml, we dont have sql server

teknovation

ASKER

Hi Jim, thanks so much for the formula! It was pretty darn close, the values were off by .03 but good enough, unless you have something else that can make it more accurate!

Jim Horn

My guess is that it's as accurate as the Excel YEARFRAC function, and the only way to test that is to do a side-by-side comparison. If you've already done that to get to the .03 it would be worth posting it here.

teknovation

ASKER

=21/(DateDiff("d",Fields!FirstDate.Value,Fields!SecondDate.Value) / 365)

Open in new window

Was able to get me this close to the YEARFRAC results which is the value to the right side.
19.556 vs 19.588

49.183 vs 49.169

Jim Horn

Thanks for the grade. Good luck with your project. -JIm

btw Check this out, it was just published a half an hour ago
Top 10 Ways to Write Rock Star Articles