Symptom
Whether you are using PivotTable on data in your workbook or connecting to a Kepion application, you could run into a situation where row and/or column data disappear in the report based on your field selection(s).
- The highest level of table headers (in this case, the Account dimension) doesn’t contain any data (if connected to Kepion).
- By default, PivotTables display only the table headers in collapsed view (i.e., show the highest level of a hierarchy if connected to Kepion).
- By default, PivotTables do not display empty rows or columns.
This article explains how to display fields without any available data.
Tip: Learn how to build Excel PivotTables in this article.
Resolution
1. Right-click the PivotTable and select PivotTable Options…
2. Select Show items with no data on rows and Show items with no data on columns so they are enabled.
3. Select OK.
If you are connected to Kepion, you should see the table with full axis information according to your PivotTable selections.
If your PivotTable is not connected to Kepion, you must complete a few more steps to display see the table with full axis information.
3. Select a field in the Values area for which you want to change the summary function of the PivotTable report.
4. On the Analyze (or Options) tab, in the Active Field group, select Active Field.
5. Go to Field Settings > Layout & Print.
6. Select Show items with no data to enable.
7. To force the Pivot Table to display zero when items have no data, go to the Layout & Format tab of PivotTable Options and enable and enter a zero in the For empty cells show field.