Displaying missing rows in a pivot table

lidsurfer · Mar 30th, 2004, 04:09 AM

Hi there,

I have a pivot table which is reporting off an Olap cube. I wish for my pivot table to display a member, even if it has a 0 value. Now, by default, Excel on displays members which have data....is there any way to show all members, regardless of what data they have?

Thanks.

Jared.

Juan Pablo González · Mar 30th, 2004, 04:13 AM

Double click on the field, and you should see a checkbox that reads "Show items with no data". Make sure that is selected, and click OK.

lidsurfer · Mar 30th, 2004, 04:17 AM

I can double click the dimension, and see the option that you are referring to, however, it is greyed out, and I can not select it.

Any suggestions?

Juan Pablo González · Mar 30th, 2004, 04:21 AM

Mhm... I see that you're using an OLAP cube for the source... let me see if I can create a cube here...

Juan Pablo González · Mar 30th, 2004, 04:35 AM

Well, I don't know. I *think* that that must be handled directly on the OLAP creation, but I haven't played with that. I'll try to do some research on this to see if I find anything.

lidsurfer · Mar 30th, 2004, 10:48 PM

Thank you...

Its strange, because I have an option to suppress or display missing rows in Analysis Services, however not in Excel...

Look forward to your reply.

Juan Pablo González · Mar 31st, 2004, 02:01 AM

This is the only thing that I found... so, sorry, don't know what else to tell you:

Quote:

According to 234700 (below), the "Show items with no data" option in
the PivotTable Field dialog box is not available on PivotTable reports
based on OLAP source data.

234700
XL: Differences Between OLAP and Non-OLAP PivotTables
http://support.microsoft.com/?kbid=234700

lidsurfer · Mar 31st, 2004, 04:44 AM

Ah well....bugger.

Thanks for your time.

Mar 30th, 2004, 04:09 AM	#1
lidsurfer Join Date: Mar 2004 Posts: 28	Displaying missing rows in a pivot table Hi there, I have a pivot table which is reporting off an Olap cube. I wish for my pivot table to display a member, even if it has a 0 value. Now, by default, Excel on displays members which have data....is there any way to show all members, regardless of what data they have? Thanks. Jared.

Mar 30th, 2004, 04:13 AM	#2
Juan Pablo González MrExcel MVP Join Date: Feb 2002 Location: Bogota, Colombia Posts: 11,738	Re: Displaying missing rows in a pivot table Double click on the field, and you should see a checkbox that reads "Show items with no data". Make sure that is selected, and click OK. __________________ Regards, Juan Pablo González http://www.juanpg.com

Mar 30th, 2004, 04:21 AM	#4
Juan Pablo González MrExcel MVP Join Date: Feb 2002 Location: Bogota, Colombia Posts: 11,738	Re: Displaying missing rows in a pivot table Mhm... I see that you're using an OLAP cube for the source... let me see if I can create a cube here... __________________ Regards, Juan Pablo González http://www.juanpg.com

Mar 30th, 2004, 04:35 AM	#5
Juan Pablo González MrExcel MVP Join Date: Feb 2002 Location: Bogota, Colombia Posts: 11,738	Re: Displaying missing rows in a pivot table Well, I don't know. I think that that must be handled directly on the OLAP creation, but I haven't played with that. I'll try to do some research on this to see if I find anything. __________________ Regards, Juan Pablo González http://www.juanpg.com

Mar 30th, 2004, 10:48 PM	#6
lidsurfer Join Date: Mar 2004 Posts: 28	Re: Displaying missing rows in a pivot table Thank you... Its strange, because I have an option to suppress or display missing rows in Analysis Services, however not in Excel... Look forward to your reply.

Mar 30th, 2004, 04:17 AM	#3
lidsurfer Join Date: Mar 2004 Posts: 28	I can double click the dimension, and see the option that you are referring to, however, it is greyed out, and I can not select it. Any suggestions?

Mar 31st, 2004, 04:44 AM	#8
lidsurfer Join Date: Mar 2004 Posts: 28	Ah well....bugger. Thanks for your time.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode