|
|
You're in the
sql server reporting services
group:Report design issue
sql server reporting services:
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
Let 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....... [quoted text, click to view] "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. [quoted text, click to view] "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....... [quoted text, click to view] "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.
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..... [quoted text, click to view] "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. >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |