How to calculate the percentage using date fields?

0
votes

I want to calculate the percentages using date fields in my SSRS report.

I have two date fields :

eg I have 3 columns in my matrix
monthly target, monthly completed and percentage

Due date field count 10 completed date count 5 percentage 50%

for example

=Sum(Fields!Completeddate.Value)/Sum(Fields!duedate.Value)*100

however, this will not work for date fields.

Can anyone help?

Thanks in advance

1
sqldatereporting-servicesssrs-2008-r2
I think you want to COUNT instead of SUM. I expect you will be getting an error with SUM – Tak
Thanks count worked this time round – Nite Cat

1 Answers

0
votes

The most straight-forward way to do this would be to add this field to your stored procedure (since it represents a column in your matrix) instead of trying to calculate it in SSRS.

Your SQL statement may look like this:

SELECT @CompletedDate, @DueDate, CAST(DateDiff(day, GetDate(), @CompletedDate) AS FLOAT)/CAST(DateDiff(day, GetDate(), @DueDate) AS FLOAT) * 100 AS INT) AS [Percentage]

The exact implementation will depend on how exactly you're using this value, but the point is that you can use the DateDiff() function to determine how many days apart two dates are, and with that information, you can find a percent difference. Once you've calculated this, you can assign it to a matrix column like you would any other value.