Bug with InScope() and some custom code

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