Reporting 2 one to many tables on one report

[noodleman]

I need to generate a report that lists 'work items' and the 'hours' and 'expenses' belonging to each.

Each Work Item links to:

• a) many Hours records in the Hours table (eg. Dave spends 8 hours completing his portion of the work item, Mike spends 4 hours completing his porttion...)
  and
  many Expense records in the Expense table (eg. Lunch = $20, Fuel = $40...)
  

How can I generate a list layout that-

• 
  -summarises by Work Item
  -includes both Hours and Expenses?
  

Should I create a calculation field that list all the Expenses against each Work Item, then... I don't know?? I may be thinking about it too hard and there's a simpler way. All I know is I'm lost.

Cheers!!

[bcooney]

You're having trouble bcs you created two tables. Both hours and Expenses should be in one table with a type field.

[noodleman]

Sorry, I should have mentioned that this was the way I had things set up before. But you're right- I just need to create 2 table occurrences, define a couple more fields, and add 2 portals based on the 'LineItem' table-

TO1:

2 predicates-

• 
  WorkItem::ID = LineItem::WorkItemID
  WorkItem::ExpenseGlobalTag = LineItem::Type
  

TO2:

2 predicates-

• 
  same but use HoursGlobalTag
  

Then I can set up 2 portals from a WorkItem layout that separate Hours from Expenses.

Do you think this will work or is there a better/alternative method?

[bcooney]

If all Expenses, both Time and Materials are in one table, then you can use that table to create a sub-summary report, by WorkItem, by ExpenseType. You'll need a summary field Total of Expense.

Do you have an Hours table and an Expense table or not? Your posts seem to contradict each other.