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://www.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.?
Are you referring to the cells that are showing the actual measure data or are you referring to the column header cells? And does it need to be conditional? For conditional data cells you can handle the CellControlAttached event and programmatically change the cell control Background property based on the cell data. Conditionally changing the row/column header cells is trickier since CellControlAttached doesn't fire for these. You'll probably need to create a style in order to change those. PivotRowHeaderCellControl is the type for the row header cell elements and PivotColumnHeaderCellControl is the type for column header cell elements.
Thanks for your reply. Actually we have to change color of cell value from row /column of pivot grid based on condition. So how to access row /column value when we are using Excel as datasource.?
Need to change column header text eg. Instead of CategoryInfo need to show Category and column header and cell values are not aligned properly. Please let us know about the same.
Please find attached screenshot for reference.
For cell styling, the type of datasource used doesn't really matter. Here's a simple example for how you could do some kind of conditional formatting on that cell:
<Style TargetType="ig:PivotRowHeaderCellControl"> <Style.Triggers> <DataTrigger Binding="{Binding RelativeSource={RelativeSource Self}, Path=Cell.Member.Caption}" Value="Level1"> <Setter Property="Background" Value="Red"/> </DataTrigger> </Style.Triggers></Style>
As for changing the column header text from "CATEGORYINFO" to show "Category" you need to provide metadata to the cube for that field. Please see the following documentation for more information:http://help.infragistics.com/doc/WPF/2016.1/CLR4.0/?page=xamPivotGrid_US_Defining_Hierarchies_And_Providing_Metadata_With_FlatData.html
Scroll down about halfway to get to "Providing Metadata with FlatData".
Thank you so much for your suggestions.
Currently we trying browse excel sheet (FlatDataSource) and bind data dynamically by converting to Data Table by using DynamicTypeBuilder.Please find attached POC,Excel file and pivot grid screenshot for reference.
We need to achieve below things
1. Calculate sum (Grand Total)/Min/Max:- Do we need to do within excel only? Or any other way to achieve.
2. Not able to remove N/A column or rows. So can you please tell us how to achieve progarmatically
Any updates on above query