OLAP Pivot Table Calculations
 

How can you add a column to an OLAP pivot table to show the difference
between values in two columns. We need to show data for two different dates
and then show the change (financial reporting). Unfortunately, it's not a
year over year or quarter over quarter, or any other standard timeframe,
otherwise we could calculate it in the SSAS cube.

Thanks in advance for any insight.

Malinda

OLAP Pivot Table Calculations
 

Hi,

The OLAP fact has no impact on this.

Suppose you have pivot table with a column field called year with two years,
and a row field called department and a data field called Amount.
Double-click the Sum of Amount field button, and in the Pivot Table Field box
click Options. Open the Show Data as drop down and choose Difference from.
Then pick Year field under Base field and pick either year in the Base item
area.

Remember to click Yes if this information helps.
--
Thanks,
Shane Devenshire


"Malinda" wrote:

How can you add a column to an OLAP pivot table to show the difference
between values in two columns. We need to show data for two different dates
and then show the change (financial reporting). Unfortunately, it's not a
year over year or quarter over quarter, or any other standard timeframe,
otherwise we could calculate it in the SSAS cube.

Thanks in advance for any insight.

Malinda

OLAP Pivot Table Calculations
 

Thanks for the response. I am using Excel 2007 with an OLAP pivot table and
I do think there is a difference with the OLAP pivot table (for one thing it
is a "Value Field Setting" not data field). When I follow these steps, I get
#N/A as all of my results. I did this with the Adventure Works cube by
putting Product Categories on Rows, Calendar on Columns and Order Quantity on
values.

Malinda

"ShaneDevenshire" wrote:

Hi,

The OLAP fact has no impact on this.

Suppose you have pivot table with a column field called year with two years,
and a row field called department and a data field called Amount.
Double-click the Sum of Amount field button, and in the Pivot Table Field box
click Options. Open the Show Data as drop down and choose Difference from.
Then pick Year field under Base field and pick either year in the Base item
area.

Remember to click Yes if this information helps.
--
Thanks,
Shane Devenshire


"Malinda" wrote:

How can you add a column to an OLAP pivot table to show the difference
between values in two columns. We need to show data for two different dates
and then show the change (financial reporting). Unfortunately, it's not a
year over year or quarter over quarter, or any other standard timeframe,
otherwise we could calculate it in the SSAS cube.

Thanks in advance for any insight.

Malinda

OLAP Pivot Table Calculations
 

malinda,

did you ever figure this out? i'm dealing with the same issue. thanks,

arthur

"Malinda" wrote:

Thanks for the response. I am using Excel 2007 with an OLAP pivot table and
I do think there is a difference with the OLAP pivot table (for one thing it
is a "Value Field Setting" not data field). When I follow these steps, I get
#N/A as all of my results. I did this with the Adventure Works cube by
putting Product Categories on Rows, Calendar on Columns and Order Quantity on
values.

Malinda

"ShaneDevenshire" wrote:

Hi,

The OLAP fact has no impact on this.

Suppose you have pivot table with a column field called year with two years,
and a row field called department and a data field called Amount.
Double-click the Sum of Amount field button, and in the Pivot Table Field box
click Options. Open the Show Data as drop down and choose Difference from.
Then pick Year field under Base field and pick either year in the Base item
area.

Remember to click Yes if this information helps.
--
Thanks,
Shane Devenshire


"Malinda" wrote:

How can you add a column to an OLAP pivot table to show the difference
between values in two columns. We need to show data for two different dates
and then show the change (financial reporting). Unfortunately, it's not a
year over year or quarter over quarter, or any other standard timeframe,
otherwise we could calculate it in the SSAS cube.

Thanks in advance for any insight.

Malinda

OLAP Pivot Table Calculations
 

Unfortunately, I have not found a solution to this except to save the pivot
table as formulas (OLAP tools) and then add the calculation. The only
problem with this approach is that if any members are added to the dimensions
you have included, you won't get those in future refreshes of the data.

Malinda

"arthur" wrote:

malinda,

did you ever figure this out? i'm dealing with the same issue. thanks,

arthur

"Malinda" wrote:

Thanks for the response. I am using Excel 2007 with an OLAP pivot table and
I do think there is a difference with the OLAP pivot table (for one thing it
is a "Value Field Setting" not data field). When I follow these steps, I get
#N/A as all of my results. I did this with the Adventure Works cube by
putting Product Categories on Rows, Calendar on Columns and Order Quantity on
values.

Malinda

"ShaneDevenshire" wrote:

Hi,

The OLAP fact has no impact on this.

Suppose you have pivot table with a column field called year with two years,
and a row field called department and a data field called Amount.
Double-click the Sum of Amount field button, and in the Pivot Table Field box
click Options. Open the Show Data as drop down and choose Difference from.
Then pick Year field under Base field and pick either year in the Base item
area.

Remember to click Yes if this information helps.
--
Thanks,
Shane Devenshire


"Malinda" wrote:

How can you add a column to an OLAP pivot table to show the difference
between values in two columns. We need to show data for two different dates
and then show the change (financial reporting). Unfortunately, it's not a
year over year or quarter over quarter, or any other standard timeframe,
otherwise we could calculate it in the SSAS cube.

Thanks in advance for any insight.

Malinda