Okay, I could really use some help. I have built a pivot that has two table
fields, two column fields, and several data fields. My table fields are
"Month" and "Region", and my column fields are "Quality" and "Bin". I have
several calculation that feed off of the data within this pivot. My problem
is, when there is not a data point within a specified "Quality" and "Bin",
the pivot deletes the column. This messes up all of my formulas.

I have attempted to use the "Show Items with no data" feature within Field
Settings, but it listed all of my "Bin's" within each "Quality". That is not
how my data is set up. Certain "Bin's" need to be assigned to "Quality"
levels.

Any help on having the pivot recognize a Null and not deleting the column,
would be greatly appreciated.

Thanks

Pivot tables pre-aggregate data (which is why they are so fast with no need
to calc on the fly) in a heirarchical fashion. It creates a cube database.
The cube has dimensions (fields such as time or account ore geography) and
members in those dimensions (such as June, or Sales or Canada). If a member
does not exist then there will be no values in the cube and therefore nothing
to display. Based on what you have asked the only way to fix the problem will
be to fudge the source data to include some zeros so that there is something
to be aggregated. Without knowing more about your source data I can't comment
further.

The only other option that I can think of is a GetPivotData function that
might fix your referencing problem within the pivot table...
--
HTH...

Jim Thomlinson


"adam" wrote:

> Okay, I could really use some help. I have built a pivot that has two table
> fields, two column fields, and several data fields. My table fields are
> "Month" and "Region", and my column fields are "Quality" and "Bin". I have
> several calculation that feed off of the data within this pivot. My problem
> is, when there is not a data point within a specified "Quality" and "Bin",
> the pivot deletes the column. This messes up all of my formulas.
>
> I have attempted to use the "Show Items with no data" feature within Field
> Settings, but it listed all of my "Bin's" within each "Quality". That is not
> how my data is set up. Certain "Bin's" need to be assigned to "Quality"
> levels.
>
> Any help on having the pivot recognize a Null and not deleting the column,
> would be greatly appreciated.
>
> Thanks

Jim

Thanks for your response. My data has a quality rating of 1-5 (these are my
primary column fields). Each quality rating has a bin assigned to it (these
are my secondary column fields). For example:

Quality 1 (Bin: A-G, H+)
Quality 2 (Bin: A-G, H+)
Quality 3 (Bin: A-D, E+)
Quality 4 (Bin: A-D, E+)
Quality 5 (Bin: A-D, E+)

So as you can see, an "A-D" Bin does not apply to a Quality 1 or 2. When
use my data and do not have a field for Quality 4 (Bin: E+), the Pivot
automatically deletes this column and goes directly to Quality 5 (Bin: A-D).

Is there not some VB code that I can add to keep the format of the Pivot
standardized?

Thanks again.

"Jim Thomlinson" wrote:

> Pivot tables pre-aggregate data (which is why they are so fast with no need
> to calc on the fly) in a heirarchical fashion. It creates a cube database.
> The cube has dimensions (fields such as time or account ore geography) and
> members in those dimensions (such as June, or Sales or Canada). If a member
> does not exist then there will be no values in the cube and therefore nothing
> to display. Based on what you have asked the only way to fix the problem will
> be to fudge the source data to include some zeros so that there is something
> to be aggregated. Without knowing more about your source data I can't comment
> further.
>
> The only other option that I can think of is a GetPivotData function that
> might fix your referencing problem within the pivot table...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "adam" wrote:
>
> > Okay, I could really use some help. I have built a pivot that has two table
> > fields, two column fields, and several data fields. My table fields are
> > "Month" and "Region", and my column fields are "Quality" and "Bin". I have
> > several calculation that feed off of the data within this pivot. My problem
> > is, when there is not a data point within a specified "Quality" and "Bin",
> > the pivot deletes the column. This messes up all of my formulas.
> >
> > I have attempted to use the "Show Items with no data" feature within Field
> > Settings, but it listed all of my "Bin's" within each "Quality". That is not
> > how my data is set up. Certain "Bin's" need to be assigned to "Quality"
> > levels.
> >
> > Any help on having the pivot recognize a Null and not deleting the column,
> > would be greatly appreciated.
> >
> > Thanks

It is not the the column is deleted. The column does not exist in the cube.
there is no source data so there can not be a representation of it. Your
choices are either to fudge the source data or to use show all...
--
HTH...

Jim Thomlinson


"adam" wrote:

> Jim
>
> Thanks for your response. My data has a quality rating of 1-5 (these are my
> primary column fields). Each quality rating has a bin assigned to it (these
> are my secondary column fields). For example:
>
> Quality 1 (Bin: A-G, H+)
> Quality 2 (Bin: A-G, H+)
> Quality 3 (Bin: A-D, E+)
> Quality 4 (Bin: A-D, E+)
> Quality 5 (Bin: A-D, E+)
>
> So as you can see, an "A-D" Bin does not apply to a Quality 1 or 2. When
> use my data and do not have a field for Quality 4 (Bin: E+), the Pivot
> automatically deletes this column and goes directly to Quality 5 (Bin: A-D).
>
> Is there not some VB code that I can add to keep the format of the Pivot
> standardized?
>
> Thanks again.
>
> "Jim Thomlinson" wrote:
>
> > Pivot tables pre-aggregate data (which is why they are so fast with no need
> > to calc on the fly) in a heirarchical fashion. It creates a cube database.
> > The cube has dimensions (fields such as time or account ore geography) and
> > members in those dimensions (such as June, or Sales or Canada). If a member
> > does not exist then there will be no values in the cube and therefore nothing
> > to display. Based on what you have asked the only way to fix the problem will
> > be to fudge the source data to include some zeros so that there is something
> > to be aggregated. Without knowing more about your source data I can't comment
> > further.
> >
> > The only other option that I can think of is a GetPivotData function that
> > might fix your referencing problem within the pivot table...
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "adam" wrote:
> >
> > > Okay, I could really use some help. I have built a pivot that has two table
> > > fields, two column fields, and several data fields. My table fields are
> > > "Month" and "Region", and my column fields are "Quality" and "Bin". I have
> > > several calculation that feed off of the data within this pivot. My problem
> > > is, when there is not a data point within a specified "Quality" and "Bin",
> > > the pivot deletes the column. This messes up all of my formulas.
> > >
> > > I have attempted to use the "Show Items with no data" feature within Field
> > > Settings, but it listed all of my "Bin's" within each "Quality". That is not
> > > how my data is set up. Certain "Bin's" need to be assigned to "Quality"
> > > levels.
> > >
> > > Any help on having the pivot recognize a Null and not deleting the column,
> > > would be greatly appreciated.
> > >
> > > Thanks