Issues when comparing dates in IIF in SSRS matrix

0
votes

Hi I'm new to ssrs and am attempting to make a report which lists the "Utilisation" percentage of people based on the amount of tasks they have completed and the amount of hours they are working that day, these two values are pulled in by a sql query and "Utilisation" is a calculated field within the report.

This is set up in a matrix format with [date] as the column grouping and [name] as the row grouping, the past year of "Utilisation" is calculated but only the past week from the chosen MaxDate parameter is unhidden in the actual preview.

My problem comes from attempting to calculate the average Utilisation of each person for the past week from the MaxDate parameter. I hope to do the past month aswell.

It just seems like all the code I try, at best just produces #Error when the IFF is true.

Here are some of the things I've tried:

       =Sum(IIF(DateDiff("d",Fields!Date.Value, Parameters!MaxDate.Value) < 7, Sum(Fields!Utilisation.Value), ""))
       =IIF(DateDiff("d",CDate(Fields!Date.Value), CDate(Parameters!MaxDate.Value)) < 7, Sum(Fields!Utilisation.Value), "")
       =IIF(DateAdd("d",-7, Parameters!MaxDate.Value) <= Fields!Date.Value, Sum(Fields!Utilisation.Value), "")

There have been alot more I've tried, these just calculate the total but I would use a count to find the average once I get this working.

My best guess at what's happening is that the IIF statement's only looking at the first Date in the matrix and then comparing that date to see if the conditions true, because if I do:

=IIF(Fields!Date.Value >= "2000/09/20", Sum(Fields!Utilisation.Value), "")

It returns the total of ALL of the Utilisation fields because the first date for each row is after "2000/09/20".

Does anyone have any suggestions?

Thank you

UPDATE:

Something like:

=SUM(IIF(IsNothing(Fields!Date.Value), 0, IIF(DateDiff("d", Fields!Date.Value, Parameters!MaxDate.Value) < 7, 1, 0)), "name")

"works", in that it provides a correct count of the amount of days with a non null utilisation in the past week but as soon as you turn that 1 into a field it errors.

I tried making a work around by making the sql decide whether the day is in the past week or month via W and M codes that are added in the statement. I did this incase the problem was with the date field but even when using the code it doesn't seem to really work. Is this some fundamental limitation with SSRS or am I just consistently fucking up.

=SUM(IIF(IsNothing(Fields!Date.Value), 0, IIF(Fields!Code.Value = "W" , (Fields!TaskTime.Value /Fields!AvailableHours.Value), 0)))

=SUM(IIF(IsNothing(Fields!Date.Value), 0, IIF(Fields!Code.Value = "W" , SUM(Fields!Utilisation.Value), 0)))

=Sum(IIF(Fields!Code.Value = "W", SUM(Fields!TaskTime.Value), 0))

UPDATE

Okay so:

=(IIF(IsNothing(Fields!AvailableHours.Value),"" , SUM(SUM(Fields!TaskTime.Value / Fields!AvailableHours.Value)) / (SUM(Fields!AvailableHours.Value)/7)))

will give me a yearly average from the whole 365 day dataset but for some reason

=(IIF(IsNothing(Fields!WeekCode.Value),"" , SUM(SUM(Fields!TaskTime.Value / Fields!AvailableHours.Value)) / (SUM(Fields!AvailableHours.Value)/7)))

where only the ones within the week of the chosen date are marked with a week code, the rest are applied null, wont work???I don't understand whats happening or why there seem to be these weird limits.

2
sql-serverreporting-services

2 Answers

0
votes

Ok so I didn't find an exact solution to this but I did find a workaround. I guess the problem likely came from using sum on calculated fields within ssrs, to work around this in a arguably inefficient way, within the sql I created 2 more columns that were calculated, weekUtilisation and MonthUtilisation, If the row's date was within a week of the date variable the utilisation was calculated there else there was a null and the same for the monthUtilisation but... within a month of the variable. This allowed me to just do sum(WeekUtilisation) and sum(MonthUtilisation) within SSRS which actually worked and then divide by count of the amount of non nulls in a row this gave me the average I wanted. Doing it within the sql is likely a faster solution anyway.

0
votes

I've found that the #Error code means that your code was correct and able to be compiled but produced an error. This is usually caused by math equations doing things like dividing by zero or trying to calculate with ambiguous data types.

Try casting all of your Fields and Parameters to the data type that is expected by the comparisons.

For example where you do:

IIF(DateDiff("d", Fields!Date.Value, Parameters!MaxDate.Value) < 7, 1, 0)

do

IIF(DateDiff("d", CDATE(Fields!Date.Value), CDATE(Parameters!MaxDate.Value)) < 7, 1, 0)

and where you have

SUM(SUM(Fields!TaskTime.Value / Fields!AvailableHours.Value))

not sure why you would SUM twice but

SUM(CINT(Fields!TaskTime.Value) / CINT(Fields!AvailableHours.Value))

and make sure those values you are dividing by aren't zero.