Hiding of pivot item

[Sandeep Gaur]

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>

[Jesus Arockia Sankaran S]

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 <syncfusion:PivotGridControl> Tag,   <syncfusion:PivotGridControl VerticalAlignment="Top" Grid.Column="0" x:Name="pivotGrid1" Grid.Row="2" ItemSource="{Binding ProductSalesData}" LoadWithDefaultPropertyFields="True">                   <syncfusion:PivotGridControl.AllowedFields>                     <syncfusion:FieldInfo Name="UnitPrice" Expression="[Amount] / [Quantity]" FieldType="Expression" />                 </syncfusion:PivotGridControl.AllowedFields>                   <syncfusion:PivotGridControl.PivotRows>                     <syncfusion:PivotItem FieldMappingName="Product" FieldHeader="Product" TotalHeader="Total"/>                     <syncfusion:PivotItem FieldMappingName="Date" FieldHeader="Date" TotalHeader="Total"/>                 </syncfusion:PivotGridControl.PivotRows>                 <syncfusion:PivotGridControl.PivotColumns>                     <syncfusion:PivotItem FieldMappingName="Country" FieldHeader="Country" TotalHeader="Total"/>                     <syncfusion:PivotItem FieldMappingName="State" FieldHeader="State" TotalHeader="Total"/>                 </syncfusion:PivotGridControl.PivotColumns>                 <syncfusion:PivotGridControl.PivotCalculations>                     <syncfusion:PivotComputationInfo CalculationName = "Total" Description = "Summation of values" FieldName = "UnitPrice" FieldHeader="Unit Price" Format = "C" SummaryType="DoubleTotalSum"/>                 </syncfusion:PivotGridControl.PivotCalculations>             </syncfusion:PivotGridControl>
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 <syncfusion:PivotGridControl> Tag,     <syncfusion:PivotGridControl.PivotRows>                 <syncfusion:PivotItem FieldMappingName="Product" TotalHeader="Total"/>                 <syncfusion:PivotItem FieldMappingName="Date" TotalHeader="Total"/>             </syncfusion:PivotGridControl.PivotRows>             <!--Specifying PivotColumns.-->             <syncfusion:PivotGridControl.PivotColumns>                 <syncfusion:PivotItem FieldMappingName="Country" TotalHeader="Total"/>                 <syncfusion:PivotItem FieldMappingName="State" TotalHeader="Total"/>             </syncfusion:PivotGridControl.PivotColumns>             <!--Specifying PivotCalculationValues.-->             <syncfusion:PivotGridControl.PivotCalculations>                 <syncfusion:PivotComputationInfo FieldName="Amount" Format="C" SummaryType="DoubleTotalSum"/>                 <syncfusion:PivotComputationInfo FieldName="Quantity" Format="#,##0"/>             </syncfusion:PivotGridControl.PivotCalculations>   <syncfusion:PivotGrid.ConditionalFormats>    <!-- Adding Conditions. -->                          <syncfusion:PivotGridDataConditionalFormat Name="C1">       <!-- Specifying the Cell Style. -->       <syncfusion:PivotGridDataConditionalFormat.CellStyle>           <syncfusion:PivotGridCellStyle Background="Green" FontFamily="Calibri" FontSize="12"/>       </syncfusion:PivotGridDataConditionalFormat.CellStyle>       <!-- Specifying Conditions. -->       <syncfusion:PivotGridDataConditionalFormat.Conditions>           <syncfusion:PivotGridDataCondition ConditionType="GreaterThan" Value="5000000" MeasureElement="Amount" PredicateType="And"/>           </syncfusion:PivotGridDataConditionalFormat.Conditions>       </syncfusion:PivotGridDataConditionalFormat>    <syncfusion:PivotGridDataConditionalFormat Name="C2">       <!-- Specifying the Cell Style. -->       <syncfusion:PivotGridDataConditionalFormat.CellStyle>           <syncfusion:PivotGridCellStyle Background="Red" FontFamily="Calibri" FontSize="12"/>       </syncfusion:PivotGridDataConditionalFormat.CellStyle>       <!-- Specifying Conditions. -->       <syncfusion:PivotGridDataConditionalFormat.Conditions>           <syncfusion:PivotGridDataCondition ConditionType="LessThan" Value="100000 " MeasureElement="Amount" PredicateType="And"/>           </syncfusion:PivotGridDataConditionalFormat.Conditions>       </syncfusion:PivotGridDataConditionalFormat>           </syncfusion:PivotGrid.ConditionalFormats>     For more details UG(User Guide) Link: http://help.syncfusion.com/ug/wpf/pivot%20analysis/default.htm#!documents/1627conditionalformatting.htm   We would attach a sample for further reference.

 

 

Please revert back if you need further assistance.

 

Thanks,

Jas

Expression_Fields_Demo_7933ee79.zip

[Sandeep Gaur]

Hello,
Thanks for your answer. My sitchuation is like this -

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

[Jesus Arockia Sankaran S]

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.<Grid.Resources> Tag,   <backgroundConverter:TextConverter x:Key="textconver" />   <Style x:Key="valStyle" x:Name="Valuecellstyle" TargetType="{x:Type syncfusion:PivotGridTemplateCell}">                 <Setter Property="MinHeight" Value="25"/>                 <Setter Property="Template">                     <Setter.Value>                         <ControlTemplate TargetType="{x:Type syncfusion:PivotGridTemplateCell}">                             <StackPanel  Grid.Column="1" Orientation="Horizontal" Background="{Binding ElementName=pivotGrid1, Converter={StaticResource backcolor}, NotifyOnTargetUpdated=True}" TargetUpdated="StackPanel_TargetUpdated">                                                                    <TextBlock Grid.Column="1" Margin="3,4,2,0"  x:Name="text"                                            Text="{Binding ElementName=pivotGrid1, Converter= {StaticResource textconver},NotifyOnTargetUpdated=True}"                                            TextWrapping="Wrap"                                            VerticalAlignment="Top" FontFamily="Segoe UI"  FontSize="12"  TargetUpdated="text_TargetUpdated"                                            />                             </StackPanel>                         </ControlTemplate>                     </Setter.Value>                 </Setter>             </Style>   2.<Syncfusion:PivotGridControl> Tag,   <syncfusion:PivotGridControl Grid.Row="2" Margin="5" ShowGroupingBar="False" x:Name="pivotGrid1" ItemSource="{Binding ProductSalesData}" LoadWithDefaultPropertyFields="True">               <syncfusion:PivotGridControl.AllowedFields>                 <syncfusion:FieldInfo Name="UnitPrice" Expression="[Amount] / [Quantity]" FieldType="Expression"  />             </syncfusion:PivotGridControl.AllowedFields>               <syncfusion:PivotGridControl.PivotRows>                 <syncfusion:PivotItem FieldMappingName="Product" FieldHeader="Product" TotalHeader="Total"/>                 <syncfusion:PivotItem FieldMappingName="Date" FieldHeader="Date" TotalHeader="Total"/>             </syncfusion:PivotGridControl.PivotRows>             <syncfusion:PivotGridControl.PivotColumns>                 <syncfusion:PivotItem FieldMappingName="Country" FieldHeader="Country" TotalHeader="Total"/>                 <syncfusion:PivotItem FieldMappingName="State" FieldHeader="State" TotalHeader="Total"/>               </syncfusion:PivotGridControl.PivotColumns>                       <syncfusion:PivotGridControl.PivotCalculations>                   <syncfusion:PivotComputationInfo CalculationName = "Amount" Description = "Summation of values" FieldName = "Amount" Format = "C" SummaryType="DoubleTotalSum"/>                 <syncfusion:PivotComputationInfo CalculationName = "Amount" Description = "Summation of values" FieldName = "Quantity" Format = "#,##0"/>                 <syncfusion:PivotComputationInfo  FieldHeader="T1" FieldName="T1" Description="Conditional value" CalculationName="Total" SummaryType="DoubleTotalSum" Format="#,##0"/>                 <syncfusion:PivotComputationInfo  CalculationName="Total"  FieldName = "UnitPrice" FieldHeader="Unit Price"   SummaryType="DoubleTotalSum" Format="C" Description="Summation of values"/>             </syncfusion:PivotGridControl.PivotCalculations>                 <syncfusion:PivotGridControl.ValueCellStyle>                 <syncfusion:PivotGridCellStyle Style="{StaticResource valStyle}"/>             </syncfusion:PivotGridControl.ValueCellStyle>             <syncfusion:PivotGridControl.SummaryCellStyle>                 <syncfusion:PivotGridCellStyle Style="{StaticResource valStyle}"/>             </syncfusion:PivotGridControl.SummaryCellStyle>         </syncfusion:PivotGridControl>   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