Report design issue

Hi,
i need to design a report similiar as the one below...
Category Time / Expense Type Totals 1/5 1/6 1/7 1/8 1/9
Regular Hours
Engineer PLC Programming 96.00 8.00 16.00 16.00 8.00
Engineer General 88.00 8.00 20.00 20.00 20.00 Project
Manager Validation 110.00 20.00 15.00 15.00
Project Manager General Head Office 24.00 8.00
Totals: 318.00 36.00 39.00 51.00 43.00 51.00
Overtime Hours
Engineer Validation San Francisco 48.00 8.00 8.00 8.00
Project Manager General San Francisco 32.00 8.00 8.00 8.00
Totals: 80.00 16.00 16.00 16.00 16.00 8.00
Expenses
Engineer Airfare $600.00 $300.00
Engineer Lodging $560.00 $80.00 $80.00 $80.00
Engineer Misc $25.00 $20.00 $5.00
Project Manager Airfare $1,800.00 $300.00 $400.00 Project
Manager Lodging $630.00 $90.00 $90.00 $90.00 $90.00 $90.00 $90.00
$90.00
Equipment Equipment $999.00 $999.00
Totals: $4,614.00 $1,789.00 $575.00 $570.00 $570.00 $470.00 $0.00
$300.00 $170.00 $170.00
The 3 sections regular hours,overtimehours and expense are in 3
different tables in the database...
Please let me know the design for this kind of report..
ThanksLet me explain the report in detail...
----
HEADER1 HEADER2 tOTAL 1/2 1/3 1/4 1/
----
SECTION1
VALUE1 VALUE1 4.00 2.00 2.00
VALUE2 vALUE2 5.00 2.00
3.00
TOTAL 9.00 4.00
2.00 3.0
----
SECTION2
VALUE1 VALUE1 4.00 2.00 2.00
VALUE2 vALUE2 5.00 2.00
3.00
TOTAL 9.00 4.00
2.00 3.0
----
SECTION3
VALUE1 VALUE1 4.00 2.00 2.00
VALUE2 vALUE2 5.00 2.00
3.00
TOTAL 9.00 4.00
2.00 3.0
----
AS I MENTIONED THE 3 SECTION NEEDS TO BE REPETIVE (THESE 3 SECTIONS WILL BE
REPEATED FOR EACH DIFFERENT PROJECT) AND THEY ARE COMING FROM THE 3
DIFFERENT STORED PROCUDEURES....
IF A PARTICULAR SECTION HAS NO VALUES THEN THAT SECTION NEED NOT BE
SHOWN......
"CCP" wrote:
> Hi,
> i need to design a report similiar as the one below...
>
> Category Time / Expense Type Totals 1/5 1/6 1/7 1/8 1/9
> Regular Hours
> Engineer PLC Programming 96.00 8.00 16.00 16.00 8.00
> Engineer General 88.00 8.00 20.00 20.00 20.00 Project
> Manager Validation 110.00 20.00 15.00 15.00
> Project Manager General Head Office 24.00 8.00
> Totals: 318.00 36.00 39.00 51.00 43.00 51.00
> Overtime Hours
> Engineer Validation San Francisco 48.00 8.00 8.00 8.00
> Project Manager General San Francisco 32.00 8.00 8.00 8.00
> Totals: 80.00 16.00 16.00 16.00 16.00 8.00
> Expenses
> Engineer Airfare $600.00 $300.00
> Engineer Lodging $560.00 $80.00 $80.00 $80.00
> Engineer Misc $25.00 $20.00 $5.00
> Project Manager Airfare $1,800.00 $300.00 $400.00 Project
> Manager Lodging $630.00 $90.00 $90.00 $90.00 $90.00 $90.00 $90.00
> $90.00
> Equipment Equipment $999.00 $999.00
> Totals: $4,614.00 $1,789.00 $575.00 $570.00 $570.00 $470.00 $0.00
> $300.00 $170.00 $170.00
> The 3 sections regular hours,overtimehours and expense are in 3
> different tables in the database...
> Please let me know the design for this kind of report..
> Thanks
>|||Don't want much, do you? I'm sure there are consultants out there somewhere
that would be happy to design your report for you....for a fee.
"CCP" wrote:
> Let me explain the report in detail...
> ----
> HEADER1 HEADER2 tOTAL 1/2 1/3 1/4 1/5
> ----
> SECTION1
> VALUE1 VALUE1 4.00 2.00 2.00
> VALUE2 vALUE2 5.00 2.00
> 3.00
> TOTAL 9.00 4.00
> 2.00 3.00
> ----
> SECTION2
> VALUE1 VALUE1 4.00 2.00 2.00
> VALUE2 vALUE2 5.00 2.00
> 3.00
> TOTAL 9.00 4.00
> 2.00 3.00
> ----
> SECTION3
> VALUE1 VALUE1 4.00 2.00 2.00
> VALUE2 vALUE2 5.00 2.00
> 3.00
> TOTAL 9.00 4.00
> 2.00 3.00
> ----
> AS I MENTIONED THE 3 SECTION NEEDS TO BE REPETIVE (THESE 3 SECTIONS WILL BE
> REPEATED FOR EACH DIFFERENT PROJECT) AND THEY ARE COMING FROM THE 3
> DIFFERENT STORED PROCUDEURES....
> IF A PARTICULAR SECTION HAS NO VALUES THEN THAT SECTION NEED NOT BE
> SHOWN......
> "CCP" wrote:
> > Hi,
> > i need to design a report similiar as the one below...
> >
> >
> >
> > Category Time / Expense Type Totals 1/5 1/6 1/7 1/8 1/9
> > Regular Hours
> > Engineer PLC Programming 96.00 8.00 16.00 16.00 8.00
> > Engineer General 88.00 8.00 20.00 20.00 20.00 Project
> > Manager Validation 110.00 20.00 15.00 15.00
> > Project Manager General Head Office 24.00 8.00
> > Totals: 318.00 36.00 39.00 51.00 43.00 51.00
> > Overtime Hours
> > Engineer Validation San Francisco 48.00 8.00 8.00 8.00
> > Project Manager General San Francisco 32.00 8.00 8.00 8.00
> > Totals: 80.00 16.00 16.00 16.00 16.00 8.00
> > Expenses
> > Engineer Airfare $600.00 $300.00
> > Engineer Lodging $560.00 $80.00 $80.00 $80.00
> > Engineer Misc $25.00 $20.00 $5.00
> > Project Manager Airfare $1,800.00 $300.00 $400.00 Project
> > Manager Lodging $630.00 $90.00 $90.00 $90.00 $90.00 $90.00 $90.00
> > $90.00
> > Equipment Equipment $999.00 $999.00
> > Totals: $4,614.00 $1,789.00 $575.00 $570.00 $570.00 $470.00 $0.00
> > $300.00 $170.00 $170.00
> >
> > The 3 sections regular hours,overtimehours and expense are in 3
> > different tables in the database...
> > Please let me know the design for this kind of report..
> >
> > Thanks
> >|||No......
Im very new to reporting services and to programming too....
thought i would get some valuable here which could help me learn sql
reporting services......
"Ronathospice" wrote:
> Don't want much, do you? I'm sure there are consultants out there somewhere
> that would be happy to design your report for you....for a fee.
>
> "CCP" wrote:
> >
> > Let me explain the report in detail...
> > ----
> > HEADER1 HEADER2 tOTAL 1/2 1/3 1/4 1/5
> > ----
> > SECTION1
> > VALUE1 VALUE1 4.00 2.00 2.00
> > VALUE2 vALUE2 5.00 2.00
> > 3.00
> > TOTAL 9.00 4.00
> > 2.00 3.00
> > ----
> > SECTION2
> > VALUE1 VALUE1 4.00 2.00 2.00
> > VALUE2 vALUE2 5.00 2.00
> > 3.00
> > TOTAL 9.00 4.00
> > 2.00 3.00
> > ----
> > SECTION3
> > VALUE1 VALUE1 4.00 2.00 2.00
> > VALUE2 vALUE2 5.00 2.00
> > 3.00
> > TOTAL 9.00 4.00
> > 2.00 3.00
> > ----
> >
> > AS I MENTIONED THE 3 SECTION NEEDS TO BE REPETIVE (THESE 3 SECTIONS WILL BE
> > REPEATED FOR EACH DIFFERENT PROJECT) AND THEY ARE COMING FROM THE 3
> > DIFFERENT STORED PROCUDEURES....
> > IF A PARTICULAR SECTION HAS NO VALUES THEN THAT SECTION NEED NOT BE
> > SHOWN......
> >
> > "CCP" wrote:
> >
> > > Hi,
> > > i need to design a report similiar as the one below...
> > >
> > >
> > >
> > > Category Time / Expense Type Totals 1/5 1/6 1/7 1/8 1/9
> > > Regular Hours
> > > Engineer PLC Programming 96.00 8.00 16.00 16.00 8.00
> > > Engineer General 88.00 8.00 20.00 20.00 20.00 Project
> > > Manager Validation 110.00 20.00 15.00 15.00
> > > Project Manager General Head Office 24.00 8.00
> > > Totals: 318.00 36.00 39.00 51.00 43.00 51.00
> > > Overtime Hours
> > > Engineer Validation San Francisco 48.00 8.00 8.00 8.00
> > > Project Manager General San Francisco 32.00 8.00 8.00 8.00
> > > Totals: 80.00 16.00 16.00 16.00 16.00 8.00
> > > Expenses
> > > Engineer Airfare $600.00 $300.00
> > > Engineer Lodging $560.00 $80.00 $80.00 $80.00
> > > Engineer Misc $25.00 $20.00 $5.00
> > > Project Manager Airfare $1,800.00 $300.00 $400.00 Project
> > > Manager Lodging $630.00 $90.00 $90.00 $90.00 $90.00 $90.00 $90.00
> > > $90.00
> > > Equipment Equipment $999.00 $999.00
> > > Totals: $4,614.00 $1,789.00 $575.00 $570.00 $570.00 $470.00 $0.00
> > > $300.00 $170.00 $170.00
> > >
> > > The 3 sections regular hours,overtimehours and expense are in 3
> > > different tables in the database...
> > > Please let me know the design for this kind of report..
> > >
> > > Thanks
> > >|||This is a simple LEFT join assuming there is a key that ties all three tables
together.
SELECT section, a.header1, a.header2
, SUM(ISNULL(a.hours ,0) + ISNULL(b.hours ,0) + ISNULL(c.hours ,0))
,ISNULL(a.hours ,0)
,ISNULL(b.hours ,0)
ISNULL(c.hours ,0)
FROM table1 a
LEFT JOIN table 2 on a.id = b.id
LEFT JOIN table 3 on a.id = c.id
GROUP BY section, a.header1, a.header2
The grouping would also need to be declared in the report output
with a sum function going in the group footer.
Good luck|||Thanks William,
ur method was very helpful for me.....
One problem though is the date field is dynamic, so can i use a matrix
instead of a table and get that design
Thanks for ur time and understanding....
"William" wrote:
> This is a simple LEFT join assuming there is a key that ties all three tables
> together.
> SELECT section, a.header1, a.header2
> , SUM(ISNULL(a.hours ,0) + ISNULL(b.hours ,0) + ISNULL(c.hours ,0))
> ,ISNULL(a.hours ,0)
> ,ISNULL(b.hours ,0)
> ISNULL(c.hours ,0)
> FROM table1 a
> LEFT JOIN table 2 on a.id = b.id
> LEFT JOIN table 3 on a.id = c.id
> GROUP BY section, a.header1, a.header2
> The grouping would also need to be declared in the report output
> with a sum function going in the group footer.
> Good luck