Workinghours per customer per month

rbuser

Hi,

I have the following data in two tables.
Table1: ID
DateTimeFrom
DateTimeTo
Table2: ID
CustomerID

Based on these tables I want to create a report that will display the
data as follows:
The numbers in the report are the hours spend for each customer in a
given month.

................Month: Jan Feb Mar Apr May Jun Jul
Aug Sep Okt Nov Dec
Customer
1:........4..........3...........2........5........12..........8........
.6.......7.........10.......14........3.........5
Customer
2:........3..........1...........0........6..........5..........3.......
..0.......0............5.........7........1.........3
Customer
3:......14..........4...........6........5........12.......18.......16..
.....9..........20......13........8.......10
etc.

How would I set this up?

With kind regards,
Stef

nicocizik

Hi Stef,

Try creating a master query based on the CustomerID linked to a detail query
containing all the time information. In the main report display the months
across the header, then the customer just above a subreport in the detail.
Inside the subreport (which is connected to the detail dataset), create 12
columns and have it traverse left to right. Next place a variable in each
column to calculate the difference between the two fields.

The tricky part will be to get everything to line up but it should be
possible.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Nico,

Could you supply me with a straight foreward example?

Regards,
Stef

nicocizik

Hi Stef,

Creating an example will take some time as ReportBuilder is not designed to
handle calendar style reports. I would suggest trying to do all data
processing in SQL code. Start by creating a dataset that contains all the
months of a year. Connect this to another dataset that contains the
customer, month extracted from the date times, and total hours for each
month. This way your report will be able to traverse all months even if
there are not hours for a give month.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com