Jump to content
Sign in to follow this  
noodleman

Reporting 2 one to many tables on one report

Recommended Posts

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!!

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.