I need to build a pivot table that can compare my data against other similar benchmark data. Also using row-level security.
If I had this data-set and my resolved tenant was ‘tenant-1’, my row-level data-set would all nulls and tenant-1 (rows 1, 2, 5, 6, 7, 8, 9 into the dashboard view).
I want to filter on the benchmark-tag; default would be my-org. Here the dashboard shows:
1 - cat-foo, my-org, 2
2 - cat-bar, my-org, 3
Problem: I only want to show benchmarks associated with tenant-1, not the full set of benchmarks. Let’s say of rows 5-9 the tenant only has tag-aaa and tag-bbb for rows 5-8. I don’t want to show tag-ccc in the filter droplist.
I’d want to see in my droplist only [ my-org, tag-aaa, tag-bbb]. I’d force to include my-org always as an hidden filter. So if I want to compare with tag-aaa I want to see:
1 - cat-foo, my-org, 2
5 - cat-foo, tag-aaa, 5
2 - cat-bar, my-org, 3
7 - cat-bar, tag-aaa, 7
row num - tenant, category, benchmark-tag, count
1 - tenant-1, cat-foo, my-org, 2
2 - tenant-1, cat-bar, my-org, 3
3 - tenant-2, cat-foo, my-org, 2
4 - tenant-2, cat-xxx, my-org, 30
5 - null, cat-foo, tag-aaa, 5
6 - null, cat-foo, tag-bbb, 4
7 - null, cat-bar, tag-aaa, 7
8 - null, cat-bar, tag-bbb, 9
9 - null, cat-xxx, tag-ccc, 100
I could add tenant-1 tags to the dataset by joining those in. However for each tag a tenant had it would add extra rows. Now I have a column with (tag-aaa, tag-bbb, null) I could use.
1 - tenant-1, cat-foo, tag-aaa, my-org, 2
1b - tenant-1, cat-foo, tag-bbb, my-org, 2
2 - tenant-1, cat-bar, tag-aaa, my-org, 3
2b - tenant-1, cat-bar, tag-bbb, my-org, 3
…
5 - null, cat-foo, null, tag-aaa, 5
6 - null, cat-foo, null, tag-bbb, 4
7 - null, cat-bar, null, tag-aaa, 7
8 - null, cat-bar, null, tag-bbb, 9
9 - null, cat-xxx, null, tag-ccc, 100
And that impacts 5, 6, 7, 8, 9 where I’d have to put null. I’d have to somehow exclude ‘null’ from the filter selection list but not the data. Also I’d want to use the value set from one column to filter different column.