Setting DataField Function as DistinctCount within Pivot table using Aspose Cells for .Net doesnt seems to work version is 20.10
Could you provide use your sample code (runnable) or preferably create a standalone console demo application, zip the project to post us to show the issue, we will check it soon.
PS. please also include template Excel file(s) in the zipped archive.
Hi @Amjad_Sahi ,
Actually i am not creating Template File. I am generating the Data sheet and Pivot table dynamically. The data sheet is created using the ImportData() function using Datatable whereas Pivot sheet is separate sheet having data input from DataRange. Also I am using several Consolidate Function all are working fine. Only issue is with the Distinct Count function.
This my sample code file for generating the Pivot table dynamically depending on database configuration
PivotTable.zip (1.7 KB)
Thanks for the code snippet.
Since you are importing data from some DataTable dynamically which we are not sure about it, so we might not evaluate your issue precisely based on your dataset. So, either provide DataTable definition in code to share it as well or at least provide your generated Excel file (we will try to use your file for data source of the pivot table to evaluate your issue), so we could test your scenario. Also, provide your expected Excel file, you may create your desired table manually in MS Excel and save the file. We will check it soon.
Okay will provide you the data output but can you tell me what could be the possible reason that other functions like Sum,Count,etc are working but on Distinct Count is not working. Is there anything specific required to use this function?
Thanks for your work on providing the sample file(s) to trace the issue. If other functions are working perfectly, then it might be a bug for which we have to reproduce it first and then log a ticket for it for resolution.
Hi @Amjad_Sahi
Please find the attached sample console application and excel output generated by it. The Distinct Count function is not working in the generate Pivot table output. can you please help on thisPivot Table.zip (5.6 MB)
Could you also provide your expected file containing your desired pivot table where you have applied distinct count function manually in MS Excel, re-save the file and provide us, we will check it soon. Also, provide details on how you could perform the task (apply distinct count function on data field in pivot table) in MS Excel manually, this will help us to mimic the behavior with MS Excel.
@Amjad_Sahi,
Please find the excel file containing both manually and code generated pivot table ,refer the worksheet name for the same.Report.zip (35.6 KB)
Steps to apply Distinct Count function:
- Within data Sheet insert pivot table using Insert tab menu
2.Create PivotTable popup list details for pivot generation need to fill following details
a. Select a Table or Range
Add Data Range values e.g. DATA!$A$1:$E$10
b. Choose where you want the Pivot report to be placed
Select New Worksheet
c. Tick the “Add this data to the data modal” checkbox
d. Click Ok button
e. Go to the newly created worksheet, within the Pivot Field List add Row field, Column Field and Data Field
f. Right Click on Data Field and select “Value Field Setting”.
g. Within the Value Field Setting popup, within “Summarize Value Field by” list select “Distinct Count” option
h. Click OK button
This will apply the distinct count function to the pivot data field
We are sorry but Aspose.Cells, currently does not support pivot table with data model. And, “Distinct Count” type calculation is only available when you specify this option “Add this data to the data modal” on. A ticket was already logged with an id “CELLSNET-47214” into our database. Once we support it, we will update you.
Thanks, but i have to achieve the above result via code and not manually.
For the time being I have tried to figure out a way to resolve this issue with workaround which seems to work perfectly fine.
Good to know that you have sorted it out via some workaround. Could you share details on the workaround you used to resolve the issue?
@Amjad_Sahi
Sure will share the detail … By when we can expect the resolution for ticket CELLSNET-47214 ?
As per the below Consolidation Function|Documentation seems the distinct Count function is supported in Pivot table. So can you please help to let me know whether there is anything wrong with my example mentioned above or any specific code needs to be added for using the distinct Count function ??
DistinctCount is supported but as we told you Aspose.Cells does not support pivot table with data model (your underlying pivot table’s data is linked to data modal). We are working over supporting pivot table with data model but since it is a complicated task so we need more time. The ETA for the feature is Q2, 2021.
The link that i have provided is of Aspose.Cells library, also can you please explain what we mean by DistinctCount is supported ??
@Amjad_Sahi Is there any workaround from your side to achieve it for the timebeing? .
I mean if pivot table is not generated using data model as source, Distinct Count attribute will work.
We are sorry but currently there might not be any workaround to cope with it. Anyways, we will check and if there is some other way around, we will let you know.
Thanks @Amjad_Sahi
Can you please provide me any working example for this scenario as mentioned by you
“I mean if pivot table is not generated using data model as source, Distinct Count attribute will work.”
I am afraid there is no workaround to fix your issue. Actually Aspose.Cells can’t refresh and calculate the PivotTable with data model. We will support PivotTable with data model later in Q2, 2021 because this feature is complex and some documents are not complete yet.