Hi guys,
i was developing some custom code to do a running total in a matrix, and i have noticed some odd behaviour with the InScope function. I am doing year on year reporting, so i have two row groups on my matrix: the first is on month (matrix2_Calendar_Month), the second on year (matrix2_Calendar_Year).
I needed to total the number of days covered by the months i was reporting on, so i wrote some very standard code to do this, along with an expression in that column of the matrix:
=IIf(
InScope("matrix2_Calendar_Year"),
CStr( Round( Sum(Fields!Sales.Value / (24 * Code.AddDays( CStr(Fields!Calendar_Month.Value), CInt(Fields!Calendar_Year.Value))), 2)) ,
Code.getBounds()
)
Code.AddDays() calculates and returns the number of days in the month of that year on that row. Code.getBounds simply returns the lower and upper bounds of the array, plus its contents (so i can inspect them). This is what is returned in the report:
| Month / Year | Sales | Capacity | % Capacity | Avg $/h | ||
| February | 2006 | 3842 | 7706 | 49.86% | 2.86 | |
| 2007 | 0 | 0 | 0.00% | 0 | ||
| March | 2006 | 4949 | 8692 | 56.94% | 3.33 | |
| 2007 | 0 | 0 | 0.00% | 0 | ||
| April | 2006 | 5160 | 8154 | 63.28% | 3.58 | |
| 2007 | 0 | 0 | 0.00% | 0 | ||
| May | 2006 | 3309 | 8348 | 39.64% | 2.22 | |
| 2007 | 0 | 0 | 0.00% | 0 | ||
| Total | 17259 | 32900 | 52.46% | 0-8*28,28,31,31,30,30,31,31,28 | ||
If you look at the output in the total row, you will see that Code.AddDays() has been called one extra time at the end, with Feb 2006 as its parameters, thus adding an extra 28 days to the running total. Why is Code.AddDays called on the total row, when i should be out of the scope of both the row groups? (Note: this happens for whichever row group i use in the InScope check in the expression).
Here is the custom code used for all this:
Dim numDays()
Public Function AddDays(ByVal month As String, ByVal year As Integer) As Integer
Dim thisMonth As String
Dim upper As Integer
upper = 0
On Error Resume Next
upper = UBound(numDays) + 1
ReDim Preserve numDays(upper)
thisMonth = CStr(year) & "-" & month & "-01"
numDays(upper) = DateDiff("d", CDate(thisMonth), DateAdd("m", 1, CDate(thisMonth)))
AddDays = numDays(upper)
End Function
Public Function TotalDays() As Integer
Dim lower As Integer
Dim upper As Integer
lower = 0
upper = 0
On Error Resume Next
lower = LBound(numDays)
upper = UBound(numDays)
TotalDays = 0
Dim ii As Integer
For ii = lower To upper
TotalDays = TotalDays + CInt(numDays(ii))
Next
End Function
public function getBounds() as string
getBounds = Cstr(LBound(numDays)) & "-" & CStr(UBound(numDays)) & "*" & Join(numDays, ",")
end function
sluggy
This has nothing to do with InScope().
The AddDays function is used within a IIF function call. IIF (like any other VB function call) evaluates all arguments before the function is invoked. Hence, the AddDays function is invoked in all cases.
Suggestion:
1. change the expression in the matrix cell to:
=Code.MyCalculation(InScope("matrix2_Calendar_Year"))
2. add a custom code function MyCalculation which uses IF - ELSE blocks to call the other custom code functions. Only in the case of using the conditional IF statement (instead of the IIF function) you will achieve the desired effect.
-- Robert
|||Doh, thanks Robert, i should have known that 
I last did VB a few years ago, i've obviously forgotten a bit :)
sluggy
|||No problem. I'm glad I could help resolving your issue and there is no bug in InScope :)
-- Robert
