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

Breaking down Total Hours worked into Day and Evening hours

I have data coming from a telephony system that keeps track of when an
employee makes a phone call to conduct a survey and which project number
is being billed for the time the employee spends on that phone call in a
MS SQL Server 2000 database (which I don't own).

The data is being returned to me in a view (see DDL for w_HR_Call_Log
below). I link to this view in MS access through ODBC to create a
linked table. I have my own view in Access that converts the integer
numbers for start and end date to Date/Time and inserts some other
information i need.

This data is eventually going to be compared with data from some
electronic timesheets for purposes of comparing entered hours vs hours
actually spent on the telephone, and the people that will be viewing the
data need the total time on the telephone as wall as that total broken
down by day/evening and weekend. Getting weekend durations is easy
enough (see SQL for qryTelephonyData below), but I was wondering if
anyone knew of efficient set-based methods for doing a day/evening
breakdown of some duration given a start date and end date (with the
day/evening boundary being 17:59:59)? My impression is that to do this
correctly (i.e., handle employees working in different time zones,
adjusting for DST, and figuring out what the boundary is for switching
from evening back to day) will require procedural code (probably in
Visual Basic or VBA).

However, if there are set-based algorithms that can accomplish it in
SQL, I'd like to explore those, as well. Can anyone give any pointers?
Thanks.

--
DDL for view in MS SQL 2000 database:

CREATE VIEW dbo.w_HR_Call_Log
AS
SELECT TOP 100 PERCENT dbo.TRCUsers.WinsID, dbo.users.username AS
Initials, dbo.billing.startdate, dbo.billing.startdate +
dbo.billing.duration AS EndDate,
dbo.billing.duration, dbo.projects.name AS
PrjName, dbo.w_GetCallTrackProject6ID(dbo.projects.descript ion) AS ProjID6,

dbo.w_GetCallTrackProject10ID(dbo.projects.descrip tion) AS ProjID10,
dbo.billing.interactionid
FROM dbo.projects INNER JOIN
dbo.projectsphone INNER JOIN
dbo.users INNER JOIN
dbo.TRCUsers ON dbo.users.userid =
dbo.TRCUsers.UserID INNER JOIN
dbo.billing ON dbo.users.userid =
dbo.billing.userid ON dbo.projectsphone.projectid =
dbo.billing.projectid ON
dbo.projects.projectid = dbo.projectsphone.projectid
WHERE (dbo.billing.userid 0)
ORDER BY dbo.billing.startdate

I don't have acess to the tables, but the fields in the view come
through as the following data types:
WinsID - varchar(10)
Initials - varchar(30)
startdate - long integer (seconds since 1970-01-01 00:00:00)
enddate - long integer (seconds since 1970-01-01 00:00:00)
duration - long integer (enddate - startdate)
ProjID10 - varchar(15)
interactionid - varchar(255) (the identifier for this phone call)

MS Access SQL statement for qryTelephonyData (based on the view,
w_HR_Call_Log):
SELECT dbo_w_HR_Call_Log.WinsID, dbo_w_HR_Call_Log.ProjID10,
FORMAT(CDATE(DATEADD('s',startdate-(5*60*60),'01-01-1970
00:00:00')),"yyyy-mm-dd") AS HoursDate,
CDATE(DATEADD('s',startdate-(5*60*60),'01-01-1970 00:00:00')) AS
StartDT,
CDATE(DATEADD('s',enddate-(5*60*60),'01-01-1970 00:00:00')) AS EndDT,
DatePart('w',[StartDT]) AS StartDTDayOfWeek, Duration,
IIf(StartDTDayOfWeek=1 Or StartDTDayOfWeek=7,Duration,0) AS
WeekendSeconds,
FROM dbo_w_HR_Call_Log
WHERE WinsID<>'0'Beowulf (beowulf_is_not_here@.hotmail.com) writes:

Quote:

Originally Posted by

This data is eventually going to be compared with data from some
electronic timesheets for purposes of comparing entered hours vs hours
actually spent on the telephone, and the people that will be viewing the
data need the total time on the telephone as wall as that total broken
down by day/evening and weekend. Getting weekend durations is easy
enough (see SQL for qryTelephonyData below), but I was wondering if
anyone knew of efficient set-based methods for doing a day/evening
breakdown of some duration given a start date and end date (with the
day/evening boundary being 17:59:59)? My impression is that to do this
correctly (i.e., handle employees working in different time zones,
adjusting for DST, and figuring out what the boundary is for switching
from evening back to day) will require procedural code (probably in
Visual Basic or VBA).
>
However, if there are set-based algorithms that can accomplish it in
SQL, I'd like to explore those, as well. Can anyone give any pointers?

It sounds perfectly possible to do that set-based, provided there is
enough data. Mapping the hour to day/night may be best be done
through a table, so you can enter the table with the hour and get
back what part of the day it is. With a calendar table, you can also
use this for days, so that you can catch non-working days in the middle
of the week.

The time zone is a little more complicated, but provided that there is
a time zone available somewhere this should not be any problem. Assuming
that all times are stored in UTC (or some other time zone), just add the
time-zone offset to get the local time.

Quote:

Originally Posted by

CREATE VIEW dbo.w_HR_Call_Log
AS
SELECT TOP 100 PERCENT dbo.TRCUsers.WinsID, dbo.users.username AS
>...
ORDER BY dbo.billing.startdate

I would recommend that you take out that TOP 100 PERCENT and ORDER BY,
as it fills no purpose, but just results in extra query overhead.

If you want the data to be sorted that way, you need to apply an
ORDER BY clause when you retrieve it. In SQL 2000 it may seen that
when you say "SELECT ... FROM view" that you get the order anyway,
but that is mere chance, and on SQL 2005 that does typically not happen.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Beowulf (beowulf_is_not_here@.hotmail.com) writes:

Quote:

Originally Posted by

>This data is eventually going to be compared with data from some
>electronic timesheets for purposes of comparing entered hours vs hours
>actually spent on the telephone, and the people that will be viewing the
>data need the total time on the telephone as wall as that total broken
>down by day/evening and weekend. Getting weekend durations is easy
>enough (see SQL for qryTelephonyData below), but I was wondering if
>anyone knew of efficient set-based methods for doing a day/evening
>breakdown of some duration given a start date and end date (with the
>day/evening boundary being 17:59:59)? My impression is that to do this
>correctly (i.e., handle employees working in different time zones,
>adjusting for DST, and figuring out what the boundary is for switching
>from evening back to day) will require procedural code (probably in
>Visual Basic or VBA).
>>
>However, if there are set-based algorithms that can accomplish it in
>SQL, I'd like to explore those, as well. Can anyone give any pointers?

>
It sounds perfectly possible to do that set-based, provided there is
enough data. Mapping the hour to day/night may be best be done
through a table, so you can enter the table with the hour and get
back what part of the day it is. With a calendar table, you can also
use this for days, so that you can catch non-working days in the middle
of the week.

Thanks for taking the time to reply. I always appreciate your advice
here. I'm a little confused by your suggestion. What I have is a
duration (start datetime and end datetime). Would an "hour" to "part of
day" table still work with this data or would I have to convert the
start and end date into something else first? Do you have any pointers
to good tutorials on calendar tables (or is google my friend)? It's a
concept I haven't heard of before.

Quote:

Originally Posted by

The time zone is a little more complicated, but provided that there is
a time zone available somewhere this should not be any problem. Assuming
that all times are stored in UTC (or some other time zone), just add the
time-zone offset to get the local time.

As returned by the view, the startdate and enddate are integers (number
of seconds since 1970-01-01 00:00:00) so it's fairly simple to convert
to UTC.

Quote:

Originally Posted by

Quote:

Originally Posted by

>CREATE VIEW dbo.w_HR_Call_Log
>AS
>SELECT TOP 100 PERCENT dbo.TRCUsers.WinsID, dbo.users.username AS
>...
>ORDER BY dbo.billing.startdate

>
I would recommend that you take out that TOP 100 PERCENT and ORDER BY,
as it fills no purpose, but just results in extra query overhead.
>
If you want the data to be sorted that way, you need to apply an
ORDER BY clause when you retrieve it. In SQL 2000 it may seen that
when you say "SELECT ... FROM view" that you get the order anyway,
but that is mere chance, and on SQL 2005 that does typically not happen.

Thank you for the advice. I learned that fact a little while ago in
this very newsgroup. I don't own that particular view, though.|||Beowulf (beowulf_is_not_here@.hotmail.com) writes:

Quote:

Originally Posted by

Thanks for taking the time to reply. I always appreciate your advice
here. I'm a little confused by your suggestion. What I have is a
duration (start datetime and end datetime). Would an "hour" to "part of
day" table still work with this data or would I have to convert the
start and end date into something else first?

I don't know. That is, I don't know what your business requirements are,
so I cannot answer. I made the simple assumption that only the start time
applied. If you want to split a call that started at 17:23 and ended at
18:14 into day and evening, I don't know in which way you want to split it.

<Standard rant>

Please post:

o CREATE TABLE(s) statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.

That makes it possible to easily copy and paste to develop a tested
solution.

</Standard rant>

(It's not likely that it will be me this time though, as I'm
off for vacation tomorrow.)

Quote:

Originally Posted by

Do you have any pointers to good tutorials on calendar tables (or is
google my friend)? It's a concept I haven't heard of before.

http://www.aspfaq.com, search for calendar. Aaron has several entries
on them.

Essentially a calendar is a table with one row for each day, and then
you associate attributes to the days that are appropriate for your
business like IsWorkingDay.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Break on Running Total

I am trying to create Packing List(s) based on an individual sales order.

Each Packing List page must be based upon the number of square feet.

I have set up a detail line with
Pieces, Width, Length and a running total of square feet (Pieces*width*length)

When the Running Total Square Feet > 400 then I need to go to a new page and reset the Running Total Square Feet and continue to print the remaining detail, until it again reaches 400 square feet.

I'm close, but I am having trouble. Can anyone help?

Note: Its MAS 90, if that makes a difference.What did you try and what problem are you facing?

Borderstyle and Padding

I need a table similar to Fig-1 on my report. The subtotal and total lines need top padding and solid border at top and bottom. I’m currently using bottom padding of 20pt on the sub total row and setting the border style property of the sub total and total rows to solid at top and bottom. But the problem is that the sub total rows are being padded first and then the border at the bottom is being set to solid. As a result, my table looks something similar to figure 2, which is not acceptable to my customer. I also tried by setting the top padding of the lines below subtotals, but it doesn't give me what I need. Any way I can set the border before padding? Appreciate your help.

Figure-1

A

10

B

20

C

30

Sub Total

60

D

20

E

10

F

50

Sub Total

80

TOTAL

140

Figure-2

A

10

B

20

C

30

Sub Total

60

D

20

E

10

F

50

Sub Total

80

TOTAL

140

You can accomplish this by adding another row to the grouping footer where the subtotal row is located. To add a row in the Report Designer, right-click on the gray box to the left of the row and choose "insert row below." Be sure to remove the extra padding from the subtotal row.

Ian|||

Ian,

Clicking on the gray box on the subtotal row selects the entire row and when I right click it just gives me options to add row groups and not rows. Is there any other way? Thanks

|||What authoring application are you using? The VS 2005 Report Designer? There should be a "Insert Row" menu option on the context menu. Are you using a matirx or a table? You seem to be describing the behavior for a matrix subtotal.

From your description, I thought you were using a table with a detail group, a grouping that has only the "Include group footer" checked, for the subtotal, and the table footer enabled, for the total. My solution only works in this scenario. This solution won't work for a Matrix, since the subtotal is derived from the group being subtotaled, and cannot have another row added to it. There are ways to accomplish this in a Matrix, but it is not as straightforward as adding an empty row.

Ian|||I should have been more specific. I'm using a matrix and not a table.