The Goal: To display job costing vs billing. Starting small with just total hours through machines and display the amount we billed by Work Order Number.
I am creating a sharepoint database that is connected to our quickbooks company file. I have created some simple apps to create time tracking for certain machines in our facility. I am trying to track our productivity and display this information along with the billing.
Every job that comes through the facility has a work order number. The time entry apps reference this work order number to allocate time to that job. When I connect to the sharepoint data source, I used a Left join from our completed job list which has billing information, and joined it to our time entry tables using the work order reference number as the join clause.
I started creating some simple visualizations but quickly realized that the join is working in a way that I did not intend. For every time entry associated to a work order, the total billing for the job was attached. So each time entry ended up adding a duplicate of the billing. After researching this it looks like I need to blend the data because these two tables have a different granularity to the data.
The tutorial for blending data says to add a second data source. When I try to do this using sharepoint, it seems to just replace the original datasource instead of adding a copy. Duplicating the data source does not seem to do anything. It seems I do not have the ability to designate a primary data source and secondary data source as the tutorial recommends.
I have only taken Tableau fundamentals 1, and the class was excellent. However the data we worked with was simple, and we did not go over joins or blending.
Here is how the connection looks in the data source page:
Here is a sample of each table:
TxnID WO# CustomerID Customer Name SubTotal Job info etc.
Entry # WO# CustomerName Material Hours
Entry # WO# CustomerName BlastBooth Loc Material Hours
When I join these tables on the WO#, the subtotal of billing is included on every entry. I definitely screwed up the join process! I am quite a novice when it comes to database design, so I fear the issue is with how I structured the data. The good thing is that all of this has been created in the last month, so changing something now will be easy and have little to no impact on our business yet.
Appreciate any help you can give!