Import Hours/Export Hours Help

[danielpaduck]

Hi,

I need to develop a report that shows both export hours (hours that an employee charges to a project whose office is not the home office of the employee) as well as import hours (vice versa - an office who is importing hours from another office).  For example, I have attached the visual

The left side (matrix) shows Waterloo employees who charged hours to a non Waterloo based project.  On the right side, I have Waterloo office that imported hours.  My region code filter (Waterloo) is currently only tied / associated to the employee org currently.  My question is how can I have that filter be associated both to the employee org and project org at the same time?  The employee org and project org data all exists in a table called Labor Detail?   Kind of tricky to solve for me.  Thanks! 

[danielpaduck]

The following sample file can be downloaded.  It appears as follows:

Essentially, there is the EmOrg (Employee Office) and PrOrg (Project Office).  When the right three digits of each match it means the Employee has charged time to a Project that belongs to their office.  When the Employee's Office does not match then that is considered an Export of Hours. When the Project's office does not match the Employee's Office then that is considered an Import of hours.   What I would like to try to create is a visual that has two matrix's - one for export of hours and one for import of hours.  There would be one slicer for the office. Thus, if the user chooses Seattle then it would show Seattle employee's export of hours and the other matrix would show Seattle project's who imported hours for non Seattle employee's.  

https://docs.google.com/spreadsheets/d/13_wnlQ4sKDxxTqeSCmax2WzzqEXiIzud/edit?usp=sharing&ouid=10363... 

[Anonymous]

Hi @danielpaduck

Here are some of the suggestions I gave:

Create Relationships:

Ensure that there are relationships established between your ‘Labor Detail’ table and both the ‘Employee’ and ‘Project’ tables. The relationships should be based on the office or region code.

Adjust Relationship Settings:

In the relationship settings, change the ‘Cross filter direction’ to ‘Both’. This allows the filter to apply in both directions.

Create a Slicer:

Use a slicer visual in your report and tie it to the region code column in the ‘Labor Detail’ table. This slicer will now control both the employee org and project org.

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danielpaduck]

I will see if I can provide some dummy data.  Essentially, the Labor Detail table has two Office Columns. One column identifying the employee's office and one column identifying the project's office.  Also, there is just one office dimension.  The business rule is that when an employee's office does not match the project's office that is considered an export of hours.  Likewise, when a project's office does not match an employee's office that is considered an import of hours.  If I just have one office dimension, then how can I create a report that has two matrixes. One matrix will show export hours for an office (Waterloo employees) while the other matrix shows the import hours for any Waterloo project?