Pivot Table - Data Column with % of Parent Calculation

[marowle <ro...@gmail.com>]

I am creating a Pivot Table using Apache POI.  I'm trying to add a data
column that will be shown as the % of Total for a given set of rows.  I've
used the code below that generally works, but there doesn't seem to be a
STShowDataAs enum value for "% of Parent".

Assuming an example where there are 4 rows of data like the following:

State   City       Number of People  % of Total
------  -------- ----------------   -----------
NY       NY         9000                   90%
NY       Albany    1000                   10%
TX       Dallas      200                    50%
TX       Houston   200                    50%

In the table below, 
  pivotDataFieldIndex  = index of the % of total column as data column (1)
  baseColumnIndex = index of the State (0)
So basically, the % of total should be the number of poeple for that
particular city divided by the sum of the number of people for that entire
state.  I'm able to use PERCENT_OF_COL or PERCENT_OF_TOTAL to get the
percent of that city relative to the sum of ALL - but not able to do based
on a given State.

In excel, there is an option for "% of Parent Total" but I'm not seeing same
as a valid enum.

Any suggestions?


		CTDataField fld = pt
			    .getCTPivotTableDefinition()
			    .getDataFields()
			    .getDataFieldArray(pivotDataFieldIndex);
		
		if (fld != null) {
			fld.setShowDataAs(STShowDataAs.PERCENT);
			fld.setBaseField(baseColumnIndex);
			fld.setBaseItem(baseColumnIndex);
		}



--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-Dev-f2312866.html

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

Re: Pivot Table - Data Column with % of Parent Calculation

["pj.fanning" <fa...@yahoo.com.INVALID>]

That class is generated from a schema.

It is a big task to regenerate those classes based on a newer version of the
schema -  and fix issues and uptake extra features - so we only do this
upgrade every few years.

http://www.ecma-international.org/publications/standards/Ecma-376.htm



--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-Dev-f2312866.html

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

Re: Pivot Table - Data Column with % of Parent Calculation

[marowle <ro...@gmail.com>]

I did a little digging and noticed in the class STShowDataAs, the following
enum values exist:
    static final Enum NORMAL = Enum.forString("normal");
    static final Enum DIFFERENCE = Enum.forString("difference");
    static final Enum PERCENT = Enum.forString("percent");
    static final Enum PERCENT_DIFF = Enum.forString("percentDiff");
    static final Enum RUN_TOTAL = Enum.forString("runTotal");
    static final Enum PERCENT_OF_ROW = Enum.forString("percentOfRow");
    static final Enum PERCENT_OF_COL = Enum.forString("percentOfCol");
    static final Enum PERCENT_OF_TOTAL = Enum.forString("percentOfTotal");
    static final Enum INDEX = Enum.forString("index");

However, I've noticed microsoft has published additional enumerations to
show various Percent_Of_Total options.  The latest updates to this library
seems to be 2018.  Is it possible to update to include the additional
options for ShowDataAs?



--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-Dev-f2312866.html

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org