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. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Hiding of pivot item

Thread ID:

Created:

Updated:

Platform:

Replies:

113609 Oct 1,2013 07:03 PM UTC Oct 4,2013 06:51 PM UTC WPF 3
loading
Tags: PivotGridControl
Sandeep Gaur
Asked On October 1, 2013 07:03 PM UTC

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 [Syncfusion]
Replied On 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 <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
Replied On October 3, 2013 06:40 PM UTC

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 [Syncfusion]
Replied On 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.<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

CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;