Why Is There No Data in My PivotTable?

Author: Joyce Zhou

Symptom

Whether you are using PivotTable on data in your workbook, or connecting to a Kepion Application, you may run into a situation where row and/or column data disappear in the report based on your field selection(s).

1. The highest level of table headers (in this case, the Account Dimension) doesn’t contain any data (if connected to Kepion).
2. By default, PivotTables only display table headers in collapsed view (i.e., show the highest level of a hierarchy if connected to Kepion).
3. By default, PivotTables don’t display empty rows or columns.

In the example below, you will see that even with Product, Account, and FiscalDate selected on the rows and columns, they are not showing on the Pivot Table itself. 

In the following article we will show you steps on how to enable these fields to be shown, even when no data is available.

If you would like to learn how to build Excel PivotTables, please refer to this article.

Resolution

To display data in the table, we should follow these steps:

Step 1. Right click on the PivotTable and select PivotTable Options…

Step 2. Check the box before Show items with no data on rows and Show items with no data on columns. Click OK.

If you are connected to Kepion, you should be able to see the table with full axis information according to your PivotTable selections.  However if the data still has not shown through, continue to steps 3 & 4.

Step 3. If you are creating a Pivot Table not connected to Kepion, you can also enable Show items with no data within Layout & Print tab of the field settings of the select Pivot Table field on the row or column axis.

Step 4. To force the Pivot Table to display zero when items have no data, you can enter a zero under the Layout & Format section of PivotTable options.