Your Privacy Matters: We use our own and third-party cookies to improve your experience on our website. By continuing to use the website we understand that you accept their use. Cookie Policy
90
FlatDataSource Excel
posted

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

XamPivotGridSample.zip
Parents
  • 34510
    Offline posted

    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://ko.infragistics.com/community/forums/p/81079/409188.aspx#409188

Reply Children