strange behaviour on textbox value with expression

• aaron.reese

  SSChampion

  Points: 13498

  More actions

  November 12, 2014 at 4:16 am

  #300944

  Guys, I have a really odd one here that none of our developers can identify the root cause.

  I have a dataset that gives me summaries of a unit count and unit value by weekdate and segment (plus some other fields)

  Weekdate Segment CountUnits Sum_Movements

  The report has a rectangle (parent = Body) with two text fields which SHOULD give me the sum of the SumMovement for the first and last weeks in the dataset. the expression is as follows

  =Sum(iif(Fields!WeekDate.Value = Min(Fields!WeekDate.Value) And Fields!IsVoid.Value = 1 And Fields!MajorVoid.Value = 1 And Fields!ActiveTenancy.Value = 1, cint(Fields!Sum_Movements.Value), cint(0)))

  The other textbox is the same but uses Max instead of Min

  The rectangle and text fields are also replicated inside a list which breaks by Segment

  When I run the report the text box for the MAX expression gives me the wrong answer. Looking at the data, one of the segments for last week has a NULL for Sum_Movements and the difference between the display value and the correct value is the sum(Sum_Movements) for one of the OTHER segments from the previous week.

  Lastweek,Segment A = 100

  Lastweek,Segment B = 200

  Lastweek,Segment C = NULL

  Lastweek,Segment D = 400

  Lastweek-1,Segment C = 800

  Lastweek-1,Segment D = 1600

  I am expecting to see 700 (100+200+400)

  I am actually seeing 2300 (100+200+400+1600)

  Bizzarely, If I add another textbox to the report with the expression =Max(Fields!WeekDate) the text box returns the correct value.

  Q1)

  Does reporting services do some sort of expression replacement (i.e. it recognises that the value in the new textbox is used in a more complex expression and therefore calculates the simple expression and substitues the expression with the calculated value) - this is the only explanation I have for the values being different as there is no relationship between the two fields apart from they both have Body as their parent

  Q2)

  Why is the calculated value wrong without this additional text field, I can't believe this is designed behaviour and looks like a bug to me.

  In this instance I can protect against the NULLs by COALESCEing the aggregates in the query - but I shouldn't have to....