Pivot - missing entries

[#1]

Afternoon everyone.

I have two tables:

Table:     Master

EmpID	Name
1	Stannis
2	Sansa
3	Cersei
4	Jon

Table:     Details

EmpID	Project	Month	Total
1	A	Jan	3
1	B	Jan	4
1	A	Feb	2
2	C	Jan	7
4	A	Jan	2
4	D	Feb	5

I have a PIVOT table with the EmpID (and Name) and Month as Dimensions.  This gives me the following:

EmpID	Name	Project	Jan	Feb
1	Stannis	A	3	2
1	Stannis	B	-	2
2	Sansa	C	7	-
4	Jon	A	2	-
4	Jon	D	-	5

i.e. with no data for Cersei.  How can I ensure Cersei has an entry in the PIVOT table? 

Note that I would rather not script it at LOAD time.  Note also that my data is quite complex - with many entries in the dimension space (specifically PROJECT).  I tried using PRESENTATION > SUPPRESS ZERO VALUES (untick), but then it creates many, many entries with blank entries.

What I'm really after is simply to ensure that Cersei is represented in the PIVOT (without creating an entry for Stannis for projects C and D).

Thanks in advance.

[marcus_sommer · Accepted solution]

There are no ways to display such missing data without some disadvantages.

The "classic" way would be to create these data within the script which could be extremly expensive by larger datasets or if you need such approach over all possible data-associations which meant you would need to create a cartesian product over all fields.

Another way could be to build such cartesian products within the gui per calculated dimensions. But this is quite complex and beside the need of enormous ressources (ram+cpu) you will have disadvantages with the usability.

Easier are approaches like you have already done and/or to use a further (dummy) expression with something like:

= 1

and then to hide them manually or per macro:

sub SetColumnWidth

set chart = ActiveDocument.GetSheetObject("CH23")

chart.SetPixWidth 3, 0

end sub

But everything will be a compromise. Recommended is to use of insights which you could get from associations-colorsheme from green-white-grey.

- Marcus