Method Community

Hi Community!

We recently signed up with MethodCRM and it's been working wonders as far as integrating with our Desktop Quickbooks (Premier 2012). I'm trying to set up a report with report designer that will allow me to list all time sheets that have been submitted from all our employees, grouped by customer:job within a given date range. At the bottom of each customer:job group should be a running total of each service item, the running sales total for each service item (service item sales * hours) and then a grand total of all hours and all sales totals. We need to be able to generate a running snapshot of how much billable sales we've accumulated for any given customer:job.

We currently have about 8 different service items, all of which have different sales prices per hour ($125/hour, $150/hour, $85/hour, etc).

Here's a "visual" breakdown/wireframe of what we're hoping to see:

Group: Customer: Job (we have a customer for whom we track 8 different jobs)

Detail: Employee Name, Service Item, Notes, Duration (in hours), Service Item Sales price

Footer: Service Item Hours Total, Service Item Sales Price total (sales price * hours)

Footer (cont): Total Service Item Hours, Total Sales Price

I've figured I would need to add, at minimum, the Customer table, ItemService table and TimeTracking table. Getting a running report of total hours, etc is the easy part, it's in somehow linking the sales price for that given item and totalling that. Any idea on where to begin?

*bump* anyone? ANYONE?! :D

Hi,

Sorry for the late reply. Which table have you based the report off of?  I haven't actually done anything like this myself, so I'll need to create this and see what's needed. and get back to you.

- Adam

Hey! No worries at all! Glad someone is even looking at this ;).

I originally had it based on the TimeTracking table, but was then unable to associate additional reference tables in the second section, so the primary table is now Customers. Back in the day when I used to build databases in Access and run reports with Crystal Reports, I was able to define the exact tables and the fields (and how they relate to each other via inner/outer joins in a query) and then process the information in a report. I'm finding that the Method Report Designer does not seem to have the ability to define queries. Additionally, not being as familiar with the quickbooks data structure makes it a sort of trial and error in getting the various components to be visible in the report.....

The Report Designer can be a difficult beast for really in depth reports.  I think the key is to know how your tables and fields link up in Method.

Since you've based the report off of the Customer table, you should be able to add in the Time Tracking table as a detail table.  I'm assuming you can't find the sales price field for each item in the time tracking entry.  This is because its not added as a linked field to the Time Tracking table.  This is fairly easy to do.  Just go to Tables/Fields in Method and click Edit Fields for the TimeTracking table.  Then just add the linked field SalesPrice as below:

Make sure to click Finish Editing Fields. This will make the SalesPrice field available in the report.

Hope this helps,

- Adam