I need to use values from the PivotGrid filters.

[SANDRO]

I would like to know how to access the values that are typed in all the "Value Filter" and "Label Filter" options, which appear in the PivotGrid filters in the RowGroupDescriptions and ColumnGroupDescriptions parts, since I require those values to perform additional calculations.

I have tried to reach the values but I only reach the Type of condition and the value, I provide the code that I use for what I mention:

foreach (PropertyGroupDescriptionBase gd in this.radPivotGrid1.RowGroupDescriptions)
{
ValueGroupFilter Filtroporvalor = gd.GroupFilter as ValueGroupFilter;                    

if (Filtroporvalor.Condition != null)
{
ComparisonCondition condition = Filtroporvalor.Condition as ComparisonCondition;

var condicionaux = condition.Condition;
var valorthan = condition.Than;

switch (condicionaux.ToString())
{
case "Equals":
//Code that performs additional calculations
break;
case "IsGreaterThan":
//Code that performs additional calculations
break;
case "IsDoesNotEqual":
//Code that performs additional calculations
break;
}
}
}

But I cannot reach the other values, for example if I use "Equals", I would also like to obtain the name of the field that is used to perform that condition and so on for the other options of "Value Filter", that field is the one that is you can select in the window that is displayed when the filter is applied:

Thank you for your support.

[Dinko | Tech Support Engineer]

Hello SANDRO,

To get the filter popup and hide its elements, you can use the GroupDescriptorElementCreating event. In the event handler, you can subscribe to the FilterPopup.PopupOpening event in which you can get each of the filter popup options and set their visibility property depending on your requirement.

this.radPivotGrid1.PivotGridElement.GroupDescriptorElementCreating += PivotGridElement_GroupDescriptorElementCreating;

private void FilterPopup_PopupOpening(object sender, CancelEventArgs args)
{
    PivotGroupFilterPopup popup = (PivotGroupFilterPopup)sender;
    if (popup.GroupDescriptorElement.FieldName != "Month")
    {
        popup.SortAZMenuItem.Visibility = Telerik.WinControls.ElementVisibility.Collapsed;
        popup.SortZAMenuItem.Visibility = Telerik.WinControls.ElementVisibility.Collapsed;
        popup.SortOptionsMenuItem.Visibility = Telerik.WinControls.ElementVisibility.Collapsed;
        popup.ValueFilterMenuItem.Visibility = Telerik.WinControls.ElementVisibility.Collapsed;
        popup.LabelFilterMenuItem.Visibility = Telerik.WinControls.ElementVisibility.Collapsed;
    }   
}

You can use the FieldName property to check which filter popup is currently opening.

As for your third question, the DataProvider itself contains the raw data, row/group/ aggregate descriptions. The RadPivotGrid engine reads these settings and display it. I could not find an option so far that you could get the data shown in the RadPivotGrid control from the LocalDataSourceProvider. Currently, the control can be exported into Excel or PDf. You can, for example, export it to xlsx format and then try to convert to CSV. This is what comes up to my mind. 

Regards,
Dinko | Tech Support Engineer
Progress Telerik

[SANDRO]

Hello, good day, I hope you are feeling very well.

Thank you very much for the answers.

Regarding the third question, I asked it for the following reason:

The person responsible for the process asked me what it is based on or what is the logic that the "Value Filter" filters apply.

Help me understand how filters work on the rows of a PivotGrid, for example, the structure is the next:

Columns = Systems
Rows = Period, Branch
Values = Cost1, Cost2, Cost3

In period there are the following values:
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040

In Branch there are the following values:
Branch_1
Branch_2
Branch_3
Branch_4
Branch_5
Branch_6
Branch_7
Branch_8
Branch_9
Branch_10
Branch_11
Branch_12
Branch_13
Branch_14
Branch_15
Branch_16
Branch_17
Branch_18
Branch_19
Branch_20
Branch_21
Branch_22
Branch_23
Branch_24
Branch_25
Branch_26
Branch_27
Branch_28
Branch_29
Branch_30
Branch_31
Branch_32
Branch_33
Branch_34
Branch_35
Branch_36
Branch_37
Branch_38
Branch_39
Branch_40

When I apply the filters for these rows, I use the following parameters:
Filter for Branch in the "Cost1" column in your "Totals" that are greater than 100.
Filter for Period in the "Cost1" column in your "Totals" that are greater than 400.

The result is:
Period  Branch.         Total
2021     Branch_24   405.18
2025    Branch_24   401.39
2026    Branch_24   681.12
2029    Branch_30   250.12
2031     Branch_30   186.42
2032     Branch_30   158.84
             Branch_36   160.19

As seen in Period 2032 there are two records.

But when the person responsible uses another tool using the same parameters to filter, the result is totally different:

Period  Branch        Total
2021    Branch_24   405.18
2023    Branch_24   244.38*
2024    Branch_24   224.53*
2025    Branch_24   401.39
2026    Branch_24   681.12
2029    Branch_30   250.12
2030    Branch_30   216.76*
2031    Branch_30   186.42
2032    Branch_30   158.84
             Branch_36   160.19
2033    Branch_12   136.25*
2034    Branch_3     111.80*

As you can see, the records that are marked with an asterisk at the end are records that are extra, that is, it does not return the same number of records as PivotGrid of Telerik, although the analysis shows that it is logically correct, I would like to know why this happens and So respond to the person in charge so that they do not doubt the tool made with Telerik.

Hoping you can enlighten me, thank you.

[Dess | Tech Support Engineer, Principal]

Hi, SANDRO,

My colleague, Dinko, is out of office today so I will be assisting you with this case today. I am not sure what is the other tool you mentioned with the different results. Could you please specify which is it? As to the filtering in RadPivotGrid, group filtering allows you to add filter conditions to the group descriptions. These conditions specify which of the generated groups will be displayed. There are two types of group filters: Label filters and Value filters. The Label filters apply a specified filter function to the name of the generated groups whereas the Value filters apply a filter function to the GrandTotal value of a given aggregate field for that group.

Note that RadPivotGrid also supports Report filtering. It occurs before the aggregated information is calculated. This may be related to the different results you see in the other tool you mentioned. This type of filtering is useful when you would like to see a report which concerns only records that share a common property, for example a sales report only for a specified country.

If you have any concerns that the displayed filter result is not correct, it would be greatly appreciated if you can provide a complete code snippet to build a sample project locally (you can also use the one that Dinko previously provided) and specify the exact steps of the expected and observed results.

[SANDRO]

Hello good morning.

I hope you are well and I appreciate the follow-up you give to my questions.

Below I present two points:

1. I provide screenshots and an annex to the explanation of the last query:
I would like to understand how the PivotGrid applies the filter when used by value for each row field.

Look in the following image I have the "Periodo" and "Sucursal" in the row fields. In "Sucursal" I apply the filter by value, where "Costo1" is greater than 100.

And it correctly shows me all those that are greater than 100.



Later in "Periodo" I apply the filter by value, where "Costo1" is greater than 400.

It shows records that are greater than 400, but what I can't understand is, why does it show two records that are greater than 100 but less than 400? What I expected would only show those that are greater than 400, but it also shows two records that are not greater than 400, then how does the control discriminate the records? What is the logic or what method does it use to display this result?



2. Try the last code that Dinko provided me:
this.radPivotGrid1.PivotGridElement.GroupDescriptorElementCreating += PivotGridElement_GroupDescriptorElementCreating;

private void FilterPopup_PopupOpening(object sender, CancelEventArgs args)
{
     PivotGroupFilterPopup popup = (PivotGroupFilterPopup)sender;
     if (popup.GroupDescriptorElement.FieldName != "Month")
     {
         popup.SortAZMenuItem.Visibility = Telerik.WinControls.ElementVisibility.Collapsed;
         popup.SortZAMenuItem.Visibility = Telerik.WinControls.ElementVisibility.Collapsed;
         popup.SortOptionsMenuItem.Visibility = Telerik.WinControls.ElementVisibility.Collapsed;
         popup.ValueFilterMenuItem.Visibility = Telerik.WinControls.ElementVisibility.Collapsed;
         popup.LabelFilterMenuItem.Visibility = Telerik.WinControls.ElementVisibility.Collapsed;
     }
}

But it doesn't do anything. Could you better send me a small project with the code that Dinko sent me, to see how it works?

Update: Hello, after testing, the code that Dinko gave me worked, but not the way he put it.

[Dess | Tech Support Engineer, Principal]

Hi, SANDRO,

Thank you for the provided detailed example. According to the provided screenshots, indeed, I can say that it is expected when you add first a value filter for the child group ("Sucursal") "Suma Costo1>100" to filter subgroups which total sum e greater than 100. Then, the parent group is recalculated and the grant total is adjusted accordingly. Then, if you apply a value filter "Suma Costo1>400" for the parent group ("Periodo"), RadPivotGrid is expected to display only groups which total value for the parent group is greater than 400. I confirm that it is an issue with the pivot engine since currently, the available functionality applies the value filters in direction from the parent groups to the child groups. However, the order of applying the filters is important and it may lead to different results even with the same filter values.

After discussing the case with the team, I can confirm that it is a limitation of our pivot engine.

I have logged it in our feedback portal by creating a public thread. You can track its progress, subscribe for status changes, and add your comments on the following link - feedback item.

I have also updated your Telerik points.

 

As to the second question, it seems that with the updated comment it works as expected. If you need any further assistance please let me know.

[SANDRO]

Thank you very much for your attention, hoping that the incident is resolved.

To avoid erroneous results, for the moment I will have to disable the ""Label Filter, Value Filter, More Sort Options, Sort A-Z, Sort Z-A"".

And to completely cover this temporary solution, could you tell me how I disable the "Label Filter, Value Filter, More Sort Options, Sort A-Z, Sort Z-A" options from the row and column filter context menu of the PivotFieldList control?



Because the PivotFieldList control causes the same issue as the PivotGrid control.

Thank you very much for everything and for the Telerik points and have a wonderful day.

[Dess | Tech Support Engineer, Principal]

Hi, SANDRO,

I have escalated this item to our developers. However, such a functionality for applying value filters from the child group to the parent group is a heavy task and I can't give an exact time frame when it will be included in the roadmap and officially released. You can increase its priority by adding a vote for it. If you follow the item, you can get notified for any status changes. 

As to the context menu options in RadPivotFieldList, I have prepared a sample code snippet which I believe will be helpful for the specific case: 

            this.radPivotFieldList1.RowLabelsControl.VisualItemFormatting += RowLabelsControl_VisualItemFormatting;

        private void RowLabelsControl_VisualItemFormatting(object sender, VisualItemFormattingEventArgs args)
        {
            PivotFieldListVisualItem visualItem = args.VisualItem as PivotFieldListVisualItem;
            if (visualItem!=null)
            {
                PivotFieldListItemButton btn = visualItem.FindDescendant<PivotFieldListItemButton>();
                if (btn!=null)
                {
                    foreach (RadItem item in btn.Items)
                    {
                        if (item.Text.Contains(PivotGridLocalizationProvider.CurrentProvider.GetLocalizedString(PivotStringId.SortOptionsContextFromAtoZMenuText)) ||
                            item.Text.Contains(PivotGridLocalizationProvider.CurrentProvider.GetLocalizedString(PivotStringId.SortOptionsContextFromZtoAMenuText)) ||
                            item.Text.Contains(PivotGridLocalizationProvider.CurrentProvider.GetLocalizedString(PivotStringId.ContextValueFilterOptionsMenuText)) ||
                              item.Text.Contains(PivotGridLocalizationProvider.CurrentProvider.GetLocalizedString(PivotStringId.ContextLabelFilterOptionsMenuText)) ||
                              item.Text.Contains(PivotGridLocalizationProvider.CurrentProvider.GetLocalizedString(PivotStringId.SortOptionsContextMoreSortingOptionsMenuText)) 
                            )
                        {
                            item.Visibility = ElementVisibility.Collapsed; 
                        }
                        else
                        {
                            item.Visibility = ElementVisibility.Visible;
                        }
                    }
                }
            }
        }

 

Before:

After:

I hope this helps.

[SANDRO]

Hello Dess.

Thank you very much for the sample code you provided me, it worked perfectly.

Regarding the PivotGrid problem, I am already following the report, I have also already added my vote, but I see that only I have voted, if it is by votes, I think it will take an indefinite time, unfortunately.

Thank you very much for your support, have a wonderful day.

[Dess | Tech Support Engineer, Principal]

Hi, SANDRO,

I am glad that the suggested solution fits the scenario you need to cover. As to the votes and the public item, indeed, the customers demand is taken into consideration and the more votes an item gathers, the higher its priority becomes. However, the overall impact and product strategy are also important when prioritizing and planning the items for the roadmap. This item is escalated to the Product Manager for the future strategy and we will consider it when planning the roadmap.

[Dinko | Tech Support Engineer]

Hello SANDRO,

Thank you for the provided details.

In general, you can get the current applied group descriptor from the RowGroupDescriptions collection. You can access the required fields the following way. 

PropertyGroupDescriptionBase description = (PropertyGroupDescriptionBase)this.radPivotGrid1.RowGroupDescriptions[0];
var valueGroupFilter = description.GroupFilter as ValueGroupFilter;
var propertyAggregateDescription = localDataProvider.AggregateDescriptions[valueGroupFilter.AggregateIndex];
var propertyName = (propertyAggregateDescription as PropertyAggregateDescription).PropertyName;
var valueCondition = valueGroupFilter.Condition;
var than = ((ComparisonCondition)valueCondition).Than;
var condition = ((ComparisonCondition)valueCondition).Condition;

You can check my project which I used to test your case. Give it a try and let me know if it works for you.

Regards,
Dinko | Tech Support Engineer
Progress Telerik

[SANDRO]

Hello, I hope you are very well, sorry for the delay in responding, I appreciate the support.

I already consulted the project that you sent me and if it worked for me, it was an excellent support, also thanks to that, I was also able to reach the values of "From" and "To" when the options "Between" and "NotBetween" are chosen.

I take the opportunity again to request additional support, how can I reach the values of "Top", the quantity, the items and why field.

 

And I would also like to know how I would get the values that indicate that it is in ascending or descending order.

And how can I reach all the options of the filters that are by "Label Filter"?

 

And I take this opportunity to ask, to reach the filters of all the values of the column section, would it be in the same way?

 

Sorry for so many requests, thank you very much.

 

[Dinko | Tech Support Engineer]

I am happy to hear that the suggested approach is working for you. As for the rest of the questions, I will follow up them below:

• I take the opportunity again to request additional support, how can I reach the values of "Top", the quantity, the items and why field.

You can use a similar approach here. Depending on the selected in the in the 3 drop down, the GroupFilter property will have different objects: Items(GroupsCountFilter), Percent(GroupsPercentFilter) , Sum(GroupsSumFilter). You can cast the object depending on its type. For example: 

PropertyGroupDescriptionBase description = (PropertyGroupDescriptionBase)this.radPivotGrid1.RowGroupDescriptions[0];
var sortOrder = description.SortOrder;
if (description.GroupFilter is GroupsCountFilter)
{
    var valueGroupFilter = description.GroupFilter as GroupsCountFilter;
}

if (description.GroupFilter is GroupsPercentFilter)
{
    var valueGroupFilter = description.GroupFilter as GroupsPercentFilter;
    var selection = valueGroupFilter.Selection;
    var value = valueGroupFilter.Percent * 100d;
    var propertyAggregateDescription = localDataProvider.AggregateDescriptions[valueGroupFilter.AggregateIndex];
    var propertyName = (propertyAggregateDescription as PropertyAggregateDescription).PropertyName;
}
if (description.GroupFilter is GroupsSumFilter)
{
    var valueGroupFilter = description.GroupFilter as GroupsSumFilter;
}

In the above code snippet, I have demonstrated how the get the selected values when the percent filter is applied. You can use a similar logic for the rest filters.

• And I would also like to know how I would get the values that indicate that it is in ascending or descending order.

The sort order can be obtained from the SortOrder of the PropertyGroupDescriptionBase class.

PropertyGroupDescriptionBase description = (PropertyGroupDescriptionBase)this.radPivotGrid1.RowGroupDescriptions[0];
var sortOrder = description.SortOrder;

• And how can I reach all the options of the filters that are by "Label Filter"?

Here you want to get the available options or the one set by the user. Can you elaborate more on your question here?

• And I take this opportunity to ask, to reach the filters of all the values of the column section, would it be in the same way?

Yes, the approach is similar. The difference here is that you need to use the ColumnGroupDescriptions instead. However, the approach of getting the selected filter options is very similar. In my project, the first element in the ColumnGroupDescriptions collection is a date-time object which means that the DateTimeGroupDescription will be used.

DateTimeGroupDescription columnGroupFilter = (DateTimeGroupDescription)this.radPivotGrid1.ColumnGroupDescriptions[0];
var groupFilter = columnGroupFilter.GroupFilter;

I hope that I was able to cover most of your questions.

[SANDRO]

Hello good morning.

I thank you for your patience and attention in supporting me with my doubts and yes, you covered most of my questions, later I will prepare a question where I do not explain myself well, but at this moment I want to ask you about something that became a priority for the project and that will related to the topic we have addressed.

I would like to know:

1. How can I disable the "Label Filter, Value Filter, More Sort Options, Sort A-Z, Sort Z-A" options from the row and column filter context menu?

 

2. With this code I disable the filtering of rows and columns:
radPivotGrid1.AllowGroupFiltering = false;

Can the previous line of code be applied to only one field in the rows or columns?

3. I have a PivotGrid which receives data from a LocalDataSourceProvider, this is how I assign it:

this.radPivotGrid1.DataProvider = this.provider;

It is important to get the long data frame, which I think is in the provider, as it helps create the wide data frame which is displayed in the PivotGrid. Is it possible to get the data from the provider already with the filters applied before they are passed to the pivotgrid and if possible, can they be exported to CSV?

Thank you very much for everything again.