What I'm trying to do:
This is going to be tough because my data is confidential. If you need more let me know, I'm happy to oblige.
I'm (we are) trying to establish Pivot table with relationships across 3 or more tables with one table and field used as a filter value, which drives the presentation of data of 3 tables. Let me try to generalize an example
Tables:
Hypen (-) separating columns and data
Problem (not sure what I'm doing right or wrong):
When I drag the fields in, with relationships, all the data shows instead of specific data based upon the filtered selection.
I do find if I "sum" by dragging the SYSTEM_COM [TransNameRef] to the Pivot table "VALUE" area, the data will appear correctly. However, we do not what to present sums or totals.
This is going to be tough because my data is confidential. If you need more let me know, I'm happy to oblige.
I'm (we are) trying to establish Pivot table with relationships across 3 or more tables with one table and field used as a filter value, which drives the presentation of data of 3 tables. Let me try to generalize an example
Tables:
Hypen (-) separating columns and data
- SYSTEM: [Name] - [ExtSys] - [Description] - [Owner] <- Headers
- SYSABC- SYSDEF - Trans Processing NY - FBI
- SYSXYZ- SYSRST - Trans Processing CA - FBI
- SYSTEM_COM: [SysName] - [ExtSystName] - [TransNameRef] <- Headers
- SYSABC- SYSDEF - 555
- SYSABC- SYSDEF - 777
- SYSXYZ- SYSRST - 666
- SYSXYZ- SYSRST - 999
- TRANS: [TranName] - [Description] - [Type] - [Path] <- Headers
- 555 - Purchase - XML - /xml/555.xml
- 666 - Revocation - UDF - /UDF/666.xml
- 777- Completed - XML - /xml/777.xml
- 999- Processing- UDF - /UDF/999.xml
- My table relationships are based upon the "unique" table values (e.g. SYSTEM and TRANS)
- Primary SYSTEM [Name] > Secondary SYSTEM_COM [SysName]
- Primary TRANS [TranName] > Secondary SYSTEM_COM [TransNameRef]
- Pivot Table:
- Filter on: SysName (SYSTEM_COM)
- Fields (tabular): [Description (SYSTEM)] - [Owner (SYSTEM)] - [ExtSysName (SYTEM_COM)] - [TransNameRef](SYTEM_COM) - [Type (TRANS)] - [Path (TRANS)]
Problem (not sure what I'm doing right or wrong):
When I drag the fields in, with relationships, all the data shows instead of specific data based upon the filtered selection.
- When I filter on a SYSTEM_COM [SysName], I receive all the SYSTEM [Description]s instead of the the one specific to the system. If I filter on SYSTEM [Name], I get the correct description but all the SYSTEM_COM [ExtSysName]s show up instead the one associated only
- The same happens with SYSTEM_COM [TRansNameRef]. Despite the filtered value, it shows all transactions versus only those actually related to the specific SYSTEM [Name] or [SysName].
I do find if I "sum" by dragging the SYSTEM_COM [TransNameRef] to the Pivot table "VALUE" area, the data will appear correctly. However, we do not what to present sums or totals.
