We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.

# Hiding of pivot item

Hello,
Say I have 3 columns (like in pivotgridexpressionfielddemo) Amount, Quantity and UnitPrice (Amount/Quantity) .
I have two questions , is it possible to
a) Hide Amount and Quantity (I just want to show UnitPrice ie CalculatedColum)
b) Is it possible to use if statement, like If (Amount >500,1,2) , if yes, can you please share the syntax

<syncfusion:PivotGridControl.PivotCalculations>
<syncfusion:PivotComputationInfo Description="Summation of Unit Price" FieldName="UnitPrice" FieldHeader="Unit Price" Format="C" SummaryType="DoubleTotalSum"/>
<syncfusion:PivotComputationInfo Description="Summation of Amount" FieldName="Amount" Format="C" SummaryType="DoubleTotalSum"/>
<syncfusion:PivotComputationInfo Description="Count of Quantity" FieldName="Quantity" Format="#,##0" />
</syncfusion:PivotGridControl.PivotCalculations>

3 Replies

JA Jesus Arockia Sankaran S Syncfusion Team October 3, 2013 12:27 PM UTC

Hi Sandeep,

 Query Response a)       Hide Amount and Quantity (I just want to show UnitPrice ie CalculatedColum Yes, We would like to let you know that PivotComputationInfo items added under PivotCalculations will be displayed in PivotGridControl.   If we add only UnitPrice under PivotGridContol.PivotCalculation as PivotComputationInfo and excluding Amount, Quantity under this Tag, like this code snippet,   CodeSnippet:   In MainPage.xaml under Tag,                                                                                                                                                                                                                                                                           b)      Is it possible to use if statement, like If (Amount >500,1,2) , if yes, can you please share the syntax We are extremely sorry to inform you that we are not able to understand your requirement correctly. However we can change the CellStyle based on the condition by utilizing the existing Conditional Formatting feature of PivotGridControl.   Code Snippet:   In MainPage.xaml under Tag,                                                                                                                                                                                                                                                                                                                                                                              For more details UG(User Guide) Link:  We would attach a sample for further reference.

Please revert back if you need further assistance.

Thanks,

Jas

Expression_Fields_Demo_7933ee79.zip

SG Sandeep Gaur October 3, 2013 06:40 PM UTC

Hello,

Year ,Quater,Amount,Units,UnitPrice
2006,1,1000,5,(1000/5)
2006,2,1500, 7,(1500/7)
2006,3,1800, 8,(1800/8)
2006,4,2000,9,(2000/9)

What I want is - Total for 2006 - (1000+1500+1800+2000)/(5+7+8+9)
Not (1000/5) + (1500/7) + (1800/8) + (1500/7)+(2000/9)

What I understand (correct if I am wrong), expression is calculated after individual values are calculated,
so thats why I want to hide the values of individual Amount and Units , then I will get the result I want. For If i use  <syncfusion:PivotComputationInfo CalculationName = "Total" Description = "Summation of values" FieldName = "UnitPrice" FieldHeader="Unit Price" Format = "C"SummaryType="DoubleTotalSum"/>
The result wont be correct

My 2nd question was like if in above case my expression was [Amount]/[Unit] , can I have expression which has if conditions, like say I want to have a calculated column T1 which is 5 if Amount > 500 and 2 if Amount < 500

Sorry, I was unable to remove this yellow collor

JA Jesus Arockia Sankaran S Syncfusion Team October 4, 2013 06:51 PM UTC

Hi Sandeep,

 Query Response What I want is - Total for 2006 - (1000+1500+1800+2000)/(5+7+8+9) Not (1000/5) + (1500/7) + (1800/8) + (1500/7)+(2000/9) We are extremely sorry to let you know that as per the current behavior, PivotGridControl don’t have expression field  support for the summary rows and summary columns. Since Summary Rows and Summary Columns  in PivotGridControl calculated from the individual cell value. if in above case my expression was [Amount}/[Unit], can I have expression which has if conditions, like say I want to have a calculated column T1 which is 5 if Amount>500 and 2 if Amount <500 We would like to let you know that PivotGridControl don’t have the build in support to have calculated  column value based on another PivotCalculation item.   We would like to provide sample level work around for the same in sample level. But, in this work around we could not hide Amount and Quantity PivotCalculations.   Workaround: We can create Calculated Column based on another PivotCalculation item, by utilizing PivotGridControl’s CellTemplate feature.   Code Snippet:   In MainWindow.xaml, include the code under,   1. Tag,       2. Tag,                                                                                                                                                                                                                                                                                                                                                                   In MainWindow class MainWindow.xaml.cs file,   void InternalGrid_QueryCellInfo(object sender, Syncfusion.Windows.Controls.Grid.GridQueryCellInfoEventArgs e)         {             rowIdex = e.Cell.RowIndex;             colIndex = e.Cell.ColumnIndex;         }                     private void text_TargetUpdated(object sender, System.Windows.Data.DataTransferEventArgs e)         {              this.pivotGrid1.InternalGrid.QueryCellInfo += new Syncfusion.Windows.Controls.Grid.GridQueryCellInfoEventHandler(InternalGrid_QueryCellInfo);          }   In BackColorConverter.cs file,   class TextConverter : IValueConverter     {         public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)         {             Syncfusion.Windows.Controls.PivotGrid.PivotGridControl pivotGrid = value as Syncfusion.Windows.Controls.PivotGrid.PivotGridControl;             string text = null;             double abs;             int rowIndex = MainWindow.rowIdex;             int colIndex = MainWindow.colIndex;             if (pivotGrid != null)             {                 if (rowIndex >= pivotGrid.PivotColumns.Count + 1 && colIndex >= pivotGrid.PivotRows.Count)                 {                     if (pivotGrid.PivotEngine.PivotValues[rowIndex, colIndex].Value != null)                     {                         if (pivotGrid.PivotEngine.PivotValues[pivotGrid.PivotEngine.PivotColumns.Count, colIndex].FormattedText == "T1")                         {                             if ((double.TryParse(pivotGrid.PivotEngine.PivotValues[rowIndex, colIndex-1].Value.ToString(), out abs) && abs >= 500000d))                             {                                 text = "5";                             }                             else if ((double.TryParse(pivotGrid.PivotEngine.PivotValues[rowIndex, colIndex-1].Value.ToString(), out abs) && abs < 500000d))                             {                                 text = "2";                             }                         }                         else if (rowIndex == pivotGrid.PivotEngine.PivotRows.Count && colIndex == pivotGrid.PivotEngine.PivotColumns.Count + 1)                         {                             text = pivotGrid.PivotEngine.PivotValues[rowIndex, colIndex].Value.ToString();                         }                         else                         {                             text = pivotGrid.PivotEngine.PivotValues[rowIndex, colIndex].Value.ToString();                         }                     }                 }             }             return text;         }         public object ConvertBack(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)         {             throw new NotImplementedException();         }     }   Please find the attached sample for detailed reference.

Please revert back if you need further assistance.

Regards,

Jas

CS_4bacef2c.zip