I have a situation that I am trying to overcome.
First off, our software supports multiple companies in the same database where it is defined by a company field in every table of the database.
For this example I will use 3 Companies, A,B, and C where A is the main company, B and C are other acquired companies.
We maintain Employee records in company A. All employee fields like hire dates and inactive dates are stored here. They also exist in the company they will be working at. So say employee 1 works at company C. Their hire info is only recorded in company A.
A sample row for the above could be:
Employee Table:
| Company | Employee | Hire Date | Inactive Date | Status |
| A | 1 | 1/1/2012 |
| Active |
| C | 1 |
|
| Active |
So when I do a table showing Head count by month using hire date, employee 1 only shows up in company A, when I really want to see them in company C. I was to solve this so that they could show up in the headcount for company C when it is selected by making a new table with only employee and the hire and inactive dates and linking that back into the employee table by Employee. This gives me the below:
| Company | Employee | Hire Date | Inactive Date | Status |
| A | 1 | 1/1/2012 |
| Active |
| C | 1 | 1/2/2012 |
| Active |
Now the issue I have is that when I display Company Headcount, with a dimension of Company, they show up in each company. Company C will show correctly (Except for people who are mainly working at company A and B but were created in C to do some work). Company A will always however show the total throughout, and not only those whose main company is A.
I know which company is their main based on another field Plant like:
What would be a good way for me to get the correct data and display from this? As I said they can do work in more than 1 company, so I can not do
if(match(Plant,'1','2'),A) as Company to link the hire data in because the records where they work at company B will be labeled as such:
| Company | Employee | Plant | Hours |
| C | 1 | 6 | 5 |
| C | 1 | 6 | 3 |
| B | 1 | 6 | 7 |
| A | 1 | 6 | 8 |
| C | 1 | 6 | 2 |
Anyone have any Ideas?
