Show all dimensions in a pivot

[#1]

Hi,

I want to show all dimnesions in my pivot table regardless of what selections I make.

I have a table of 91 records for parts and when I select a month it automatically disregards any data. i,e I select January 2011 and shows 41 records, if there have been no parts used this vanishes from my data.

I want to show all dimensions, show a zero if no parts have been used rather than disregarding anything where no parts have been used. I have tried surpressing zero values but to no avail.

I hope this makes sense?

Dave

[#2]

Hello I dont know if I understood your issue right, but you may try this under the chart properties, you may go to the Presentation tab and there at the bottom enter the Null Value - and Missing Value as - and check if it works... Make sure the show all values under the Dimension is Checked.

Mama

[matt_crowther]

Dave,

Bit of a workaround but it works for me in pivots and Straight tables where I need to show all dimensions ie all stock items whether they've been sold or not.

Add an extra expression to the chart: ='' and give it the label ='' and then shrink to its smallest width. As ='' applies to all dim values where as =Sales may not it forces all the dim values to be shown and '0' in the Sales expression.

I usually move the ='' expression to be the first expression making a nice gap between dim values and results - alternatively check the 'invisible' check box.

Hope that halpe,

Matt - Visual Analytics Ltd

[#4]

Thank you for replying so quickly but I have already done that.

Problem remains that when I select the month it only shows the dimensions where there has been parts used.. I have tried set analysis with the month

Sum ({$<YEARMONTH=>} QUANTITY)

but this does not work

[#5]

Thanks Matt,

Still not done the trick unfortunatley, I want to reatin all dimesnion values so one of my colleagues can just copy and paste into Excel after exporting it to a spredsheet.

Its a shame I cannot attach the view so you can see my dilema.

Why is this so diffcult|? Would not ben an issue in an excel pivot table.

[#6]

Hi all,

any solution for the issue decribed above? Facing the same problem at the moment...

Greetz from Austria,

Steffi

[rob_greene]

Hi Steffi,

I have just had this problem with a pivot table and got around it by creating an expression, in 1st position.

Label = 0 (or whatever you want)

Expression = SUM ({1}Revenues)-SUM({1}Revenues)  .  Or whatever measure you have in your data.

This will produce a 0 on every line of your used dimensions data, whatever dimension filters are used.

You can then format the 0's the same as the background and they will not show in the finished object.

Also works on straight tables and charts.

Hope this helps

Regards

Rob

[Anonymous]

Hi

     Hope the following attached file will be give you the clear idea for your doubt and problem

Regards

Ashok

[rob_greene]

Hi again,

I have been advised by my learned friend Jonas that I should use the following to achieve the same without the zero column.  You don't have to put this as the first expression either, as long as it is enabled.

SUM ( {1} AGGR ( Sum ( Revenues ), Dim ) )    where Dim is the dimension you want to show all the values.

Best Regards

Rob