Hello Team,
We are using xamPivotGrid with excel as a datasource. We have to create custom measure to calculate percentage on grouping of column named "LEVELINFO"
and "SOURCEINFO". And need to show percentage as last column in grid.
Formula:- (TOTAL_COUNT - EXCEPTION COUNT)/TOTAL_COUNT.
So can you please guide us on the same ?
Please find attached zip file which contains sample POC and excel file
Hi omkar,
Calculated measures would allow you to do this however these only work with XmlaDataSource, not FlatDataSource. For a FlatDataSource you will need to do the calculation in the data itself which means adding that formula to the Excel worksheet first so that it can calculate the value. Then once you give the worksheet to the FlatDataSource it will see the new formula column values and incorporate them into the measures form which you can select them.
If you can't edit the excel file itself to add in the calculation then what you can do is load the excel file into our Excel framework using Workbook.Load(). Once loaded you can programmatically add your own calculated values to the worksheet cells. When that is finished you can then save the modified Workbook to a Stream and then pass that to the ExcelDataConnectionSettings.
For hiding the N/A cells in the pivot grid you have to set the AllowNullDataLevelMember property on the LevelDescriptor. Please see the following forum:http://es.infragistics.com/community/forums/p/81079/409188.aspx#409188
Thank you for your reply.
Can we change column cell value background color when we are using excel as flatdatasource.?