Displaying all filtered row and column headers in cross table with 2 data tables

[Geoffrey Paris]

Hello,

I've had much trouble with something that seems really simple to do at first.

I have 2 data tables :

- table A containing some location IDs, a user ID and a workstation ID.

- table B containing the same IDs (location, user and workstation) + another_ID+ a datetime

 

Table A

 

 

location_id1

location_id2

user_id

workstation_id

 

 

 

 

1

1

1

 

1

 

 

 

1

1

2

 

1

 

 

 

1

1

2

2

 

 

1

1

2

3

 

 

 

 

 

Table B

 

 

location_id1

location_id2

user_id

workstation_id

another_id

datetime

 

 

 

 

1

1

1

 

1

 

1

26/05/2021

 

 

1

1

2

 

1

 

2

29/05/2021

 

 

1

1

2

2

3

29/05/2021

 

 

1

1

2

2

4

01/06/2021

 

 

 

I'd like to make a cross table with as horizontal axis the user IDs, as vertical axis the workstation IDs and the intersection would be Count(another_ID).

I want to filter the rows from table A with the location IDs and display all the resulting workstation IDs and user IDs as headers for my cross table even if no data is available for that combination in table B.

 

Cross table

 

 

Workstation

user_1

user_2

 

 

 

 

1

1

1

 

 

2

0

2

 

 

3

0

0

 

 

 

I've tried to change the 'Axes' settings in the cross table properties with:

- Categories: Show all values/filtered values

- Evaluate axis expression on: All data / Current fitering only

but nothing seems to work as only IDs for which data is available are shown or every ID is shown (without taking the filters into account).

I also tried some custom expressions for axes based on the values of the filters with ironpython scripts to filter manually the rows but I didn't make it work.

Does anyone know how to do this

 

Thanks in advance.

[David Boot-Olazabal]

Hi Geoffrey,

 

How have you joined the two data tables in the dashboard And what is it really that you want to display from both tables That part is a bit unclear to me. Do you have an example dxp in which you have tried to set this up perhaps

 

Kind regards,

 

David Boot-Olazabal