I need to create full outer joins for data in 5 tables based on two keys (Date & Project ID), and then have the ability to join/filter based on Project Class, Date & Project ID. (Tables are server queries into Oracle).
My table structure is as follows
|Details Table||Plan Table||Actuals Table||Internal Order Table||External Order Table|
|Project ID||Project ID||Project ID||Project ID||Project ID|
|$ Value||$ Value||$ Value||$ Value|
I am using the Details Table as the primary table in order to filter by Project Class and then return all $ Value results for the Project IDs. (The Details Table maintains a dynamic list of thousands of Project IDs associated with a static list of ~10 Project Classes). I must limit based on Project Class and then look at the resulting set of Project IDs across a set of Dates.
When I try to add a second level link based on Month(Date), the date link fails. I have established the connection under Data > Edit Connections, but I do not have the option to activate the link between Dates. Similarly, I can create charts linked by Date but then cannot also link & filter by Project Class.
Is there a way to link & filter by fields from multiple 'primary' tables? Am I missing something else here?
Thank you in advance for your help