How to add 'Count Distinct' Option in pivot value field settings

Aspose.Cells Product Family
1

Hi Team,

As we are using aspose-cells-8.8.0 jars for java, Can you please confirm is there any feasibility to add ‘Count Distinct’ Option in pivot value field settings through aspose cells java.we were able to add count,sum,etc functions.

Please let us know if you found solution asap.

2

@Jayaraj,

Thanks for your query.

I could not find this option for value field settings in Pivot Table report in MS Excel. How could you add “Count Distinct” option in pivot value field to PivotTable report? If you still think it is possible in MS Excel, you may create a sample Excel file containing a field value with your desired option via MS Excel manually and provide us the file here, we will check how to do it via Aspose.Cells APIs.

Thank you.

3

Hi Team,

We needed ‘Distinct Count’ in pivot excel using aspose-cells-8.8.0 jars in java,
when we select the data sheet we are getting ‘Add this data to the Data Model’ option to get the
‘Distinct Count’ Value fields in 2013 and 2016. Can you please suggest how can we get this using
Aspose jars.

Please refer the below link for your reference

Please let us know As Soon As Possible

4

@Jayaraj,

The option (ConsolidationFunction.DISTINCT_COUNT) should be supported in your mentioned version. Please note that we only support it when the pivot table is created from the template file and the pivot table is created with data model by MS Excel since 2013.

If the pivot table is created without data model of Excel 2013 and “Distinct count” is set, the file will be corrupted.

5

Hi Team,

Thanks for the information,

can you please tell us how we can enable ‘Add this data to the Data Model’ check box while creating pivot to display ‘DISTINCT COUNT’ in pivot excel.

6

@Jayaraj,

I think there is no relevant APIs available to set the option “‘Add this data to the Data Model’” on, so you got to make it on in your template file’s designer pivot table manually in MS Excel.