 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Services / Reporting Services / July 2008Creating a report when data exists on two levels of a data hierarchy
I have a report that I need to create where data exists on a transactional level e.g., timesheet entries against a cost category/ project, and also on a cost category level e.g., budget entries against a project. To be clear, the hierarchy for the data (starting at the bottom) is emplyoee - cost category - project. This poses a problem because I cannot figure out a way to include both types of data in one table. If I sum up the transactional level detail to the cost category level so that it aligns with the budgetary data, I lose all of the employee names and pay rates. If I create two SQL queries (one at each level), I cannot figure out a way to combine the data in one table. I have tried creating a table within a table but it seems that the "exterior" (grouped at the cost category level) table determines the data set for the "interior" table (grouped at the employee level) even if the "interior" table has a different data set. Does anybody have any ideas on what approach I can take? If anything is unclear, please ask and I will be happy to provide more details. Thanks. I think you should look at using subreports. I don't have a complete handle on what you are doing. Subreports are how you join disparate data like this. It could be that the parent data you will need to add (but not display) the data that you will need to pass on to the subreport as a parameter.  SignatureBruce Loehle-Conger MVP SQL Server Reporting Services >I have a report that I need to create where data exists on a > transactional level e.g., timesheet entries against a cost category/ [quoted text clipped - 14 lines] > > Thanks. I don't think subreports is the right approach. I will try and explain my problem more clearly. Employees enter their time against a cost category/project combination. It is easy to display this data in a table. However, the complicating issue is that there is also budgetary data associated with the cost category/project combination. This budget to cost category/project relationship is a one-to-one relationship and only exists at the cost category level (not the employee level) whereas the employee to cost category/project relationship is one-to- many. In case that is not clear, the data structure is as follows Project Cost Category (Time Budgeted) Employee (Time Worked) I currently have a data set where all of the budget and time data is summed up at the cost category/project in the query so that it looks as follows: Project Cost Category Hours Worked Hours Budgeted The data set involving the employees is as follows: Employee Project Cost Category Hours Worked As you can see, the second data set includes another level of data and I cannot figure out how to combine the two into a report that should look something like this: Project Hours Worked Hours Budgeted Cost Category 22.5 20.0 Employee A 10.0 Employee B 12.5 Any help would be appreciated. Thanks. On Jul 9, 3:52 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@hotmail.com> wrote: > I think you should look at using subreports. I don't have a complete handle > on what you are doing. Subreports are how you join disparate data like this. [quoted text clipped - 29 lines] > > - Show quoted text - This still seems to me to be a subreport solution. Subreports are just regular reports that have parameters. You develop the report by itself and test it then you embed it and link the parameters to whatever should be used for the parameter values. It loosk to me that your Project Cost Category Hours Worked Hours Budgeted is the main report. The Employee Project Cost Category Hours Worked is the subreport. The subreport has two parameters: Project and Cost Category. This seems to be the classic master detail type of report which is exactly what subreports are used for. SignatureBruce Loehle-Conger MVP SQL Server Reporting Services I don't think subreports is the right approach. I will try and explain my problem more clearly. Employees enter their time against a cost category/project combination. It is easy to display this data in a table. However, the complicating issue is that there is also budgetary data associated with the cost category/project combination. This budget to cost category/project relationship is a one-to-one relationship and only exists at the cost category level (not the employee level) whereas the employee to cost category/project relationship is one-to- many. In case that is not clear, the data structure is as follows Project Cost Category (Time Budgeted) Employee (Time Worked) I currently have a data set where all of the budget and time data is summed up at the cost category/project in the query so that it looks as follows: Project Cost Category Hours Worked Hours Budgeted The data set involving the employees is as follows: Employee Project Cost Category Hours Worked As you can see, the second data set includes another level of data and I cannot figure out how to combine the two into a report that should look something like this: Project Hours Worked Hours Budgeted Cost Category 22.5 20.0 Employee A 10.0 Employee B 12.5 Any help would be appreciated. Thanks. On Jul 9, 3:52 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@hotmail.com> wrote: > I think you should look at using subreports. I don't have a complete > handle [quoted text clipped - 32 lines] > > - Show quoted text - |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.