Pivot table page field items

Question

Is there any way in which i can see only those items in the pivot table page fields which have data values associated with them ? For example if I have sales data of only 3 years, but i have a date dimension table of last 50 years, then it shows all the 50 years in the page fields. however when i drag the year in the rows, it shows only the ones which have data.

however slicers show only those which have data !!

this has been a source of great irritation to my customers.  is there any solution to this ?

sanjay shah

prosys, pune, india

Answer 1

Sanjay,

Do you work with Excel 2013? otherwise, the traditional Excel for developer forum is more appropriate for this question. There're more community members. Tell me if you want to move it.

Can you elaborate "it shows all the 50 years in the page fields"? I assume you mean a date and time filter. I would suggest you filter the source data table, keep records which has data.  Then create the pivot table base on that.

hope this helps.

---

Forrest Guo | MSDN Community Support | Feedback to manager

Answer 2

hi,

Do you still need assistance? 

regards,

---

Forrest Guo | MSDN Community Support | Feedback to manager

Answer 3

hi,

I had written a reply on this, but surprisingly it does not appear here !

Yes, my problem is not resolved. In any version of excel which i use, the page level fields show all the values, and the row / column level fiels show only those value for which there is associated data.

i would like to know how whether and how we can configure so that even at page level only those items are seen which have values in the data.

Sanjay

Answer 4

Sorry, but I don't understand the situation through above reply. Will you please elaborate with some snapshot?

thanks.

---

Forrest Guo | MSDN Community Support | Feedback to manager

Answer 5

hi,

in the first screenshot the drop down of the FinYear field shows all years from 1975 onwards.

Iin the second screenshot the finyear field which has been taken in the rows shows the data for only year 2012-13. this is because no other year contains any data.

 

Answer 6

So as I understand the above picture is what you want, but not the lower picture.  The value options in a dimension depends on data table, if you want to display these year which don't have data, you may fill empty value for them, so they can appear in options.

thanks.

---

Forrest Guo | MSDN Community Support | Feedback to manager

Answer 7

It is the lower option which I wish to have my friend.

I wish to see only those items at the page field level which have values.

Kind regards

Sanjay Shah

Answer 8

It is the lower option which I wish to have my friend.

I wish to see only those items at the page field level which have values.

Kind regards

Sanjay Shah

Answer 9

Got it.

I think we need to filter the data source, to exclude data which date < 2012. 

regards,

---

Forrest Guo | MSDN Community Support | Feedback to manager

Answer 10

The solution cannot be to set a filter to date <2012. this problem is not just with dates but with every field at page level. the field will show values for which there is no data.

Excel should be showing only those items for which there is data, just like it does when the same field is dragged to the rows.

sanjay shah, pune, india