ADOMD DataProvider filtering data

[hamish]

I am using the ADOMD data provider to connect to a SQL Server 2014 Analysis server and I only want certain records to be returned. I have a field called "accnt" which is a string (varchar) field on a dimension in the cube on SQL server. The CUBE may have several thousand different accounts and I want to only to be able to see certain records (e.g. 1 or more specific accounts). I have the following code where I am trying to only get account which has a value of "ABC" in it. Following the documentation I came up with the following however it does nothing irrespective of what I put in the condition.Comparison or the member name. Can someone giv e me an example of how this should be formatted - thank you
.

01.OlapSetCondition condition = new OlapSetCondition();
02.                   condition.Comparison = Telerik.Pivot.Core.Filtering.SetComparison.DoesNotInclude;
03.                   condition.Items.Add("[accnt].&[ABC]");
04.                   AdomdFilterDescription filterDescription = new AdomdFilterDescription();
05.                   filterDescription.MemberName = "[accnt]";
06.                   filterDescription.Condition = condition;
07.
08.                   pivot.AdomdDataProvider.BeginInit();
09.                   pivot.AdomdDataProvider.FilterDescriptions.Add(filterDescription);
10.                   pivot.AdomdDataProvider.ConnectionSettings = connectionSettings;
11.
12.                   pivot.AdomdDataProvider.EndInit();
13.                   pivot.MainGrid.DataProvider = pivot.AdomdDataProvider;
14.                   pivot.FieldList.DataProvider = pivot.AdomdDataProvider;

[Rosen Vladimirov]

Hello Hamish,

Your code seems fine, I've tested it in our application (connected it to our public cube) and everything works as expected. I'm sending you my sample project and a video demonstrating that everything is working as expected on our side. Can you verify the member name and the unique name of the item that you want to filter? You can try the approach from this article, which should help you to determine where is the problem.

Hope this helps.

Regards,
Rosen Vladimirov
Telerik

 

 

[hamish]

Thank you for your response and I have checked the names as you suggested and as far as I can see they are correct. Just to confirm my understanding:
1.  Where I add an item to the condition -  i.e. condition.Items.Addd("[accnt].&[ABC]" that the first field [accnt] is the actually name of the field in the Cube Dimensions? and the part '&[ABC]' is the value I want to filter - i.e. only return me rows where the Dimension Attribute accnt is equal to ABC?
2. Should I set the filter condition before or after the BeginInit or does it not matter?
3. The article you gave me to help debug this applies to xmla and not ADOMD - in  our environment it is simply not possible for me to use http access - can you give me something similar that relates to ADOMD
4. I found the video you sent somewhat confusing. It seemed to indicate that I had to add my filter by adding the filter at the field list level on the user interface (i.e. the filter box in the field list selector). I do not want to do this as I must have the data filtered (i.e. selected accnts only) prior to the data been presented to the user.
Look forward to your comments - thank you
 

[Rosen Vladimirov]

Hi Hamish,

I'm going straight to your questions:

1) When you add items, you have to add the exact name of the item - this name can be found from the OLAP Cube itself - you can use SqlServer Management Studio for example and get the exact name of the item that you want to filter. Please note that even if you see the item as "ABC", this may not be its unique name in OLAP Cube, it can be a number. I've recorded a short video demonstrating some different unique names in Adventure Works Cube - you can see how the visible names are not the same as the unique names. In order to filter the items correctly you have to add the unique name of the item. In case you have set the condition to Includes and you have set correct unique name, only dimensions which contain this value will be returned. Please note that when you use Filter descriptions, the dimension members that are set in the filter are not visible in the UI, they are used just for filtering. In case you want to show them, you have to add them as RowGroupDescriptions/ColumnGroupDescriptions and set a LabelFilter for example. You can check this article for more details on this approach.

2) BeginInit and EndInit are used when you apply multiple changes in your DataProvider - they block all updates until EndInit is called. In your scenario you can put the adding of FilterDescription between BeginInit - EndInit section only in case you apply multiple changes (add other descriptions for example, changing connection settings, etc.).

3) Sorry for this, I didn't notice you have mentioned for this limitation in one of your previous threads. Debugging Adomd is quite difficult, but there are two events that you can handle and check for errors in them. I've modified my project in order to show you how to achieve this.

4) In the video I wanted to show you that the filter I've set is working initially and that's why I removed it after staring the application - to show that the values in RadPivotGrid will be changed. After that I've added the same filter in order to show that the value is the same as the one from the initial start of the application.

Hope this helps.

Regards,
Rosen Vladimirov
Telerik

 

 

[hamish]

Thank you for your prompt reply however I am still a little confused. I have attached a screen print which shows the field from the cube that I want to filter on. I have now amended my code to be as follows and this till does nothing. My questions are:
1. I have changed the name of the unique to be [Dimension].[FieldName] - i.e. in this case [Risk Cube Dimensons].[Accnt]. Following your example this appears the match the Adventures works query you showed.
2.  I still do not understand what you mean by the dimension members are not visible. Are you saying that I cannot have this field (Accnt) visible in my pivot grid?. If so then how to I include it in the dimension and then not show it?
Look forward to your response.

OlapSetCondition condition = new OlapSetCondition();
                  condition.Comparison = Telerik.Pivot.Core.Filtering.SetComparison.Includes;
                  condition.Items.Add("[Risk Cube Dimensions].[Accnt].&[ABC]");

                  AdomdFilterDescription filterDescription = new AdomdFilterDescription();
                  filterDescription.MemberName = "[Risk Cube Dimensions].[Accnt]";
                  filterDescription.Condition = condition;
                  pivot.AdomdDataProvider.FilterDescriptions.Add(filterDescription);

[Rosen Vladimirov]

Hi Hamish,

I cannot guarantee the names you have set for dimension and the unique names are correct, you should check this with some OLAP tool, for example SQL Server Management Studio - you can use the video I've shown in my previous mail for more details how to use it.

 As per the second question - the dimensions that you've added as Filter Descriptions are not visible in RadPivotGrid - the idea of these filters is to filter the groups, without showing the member by which you are filtering. In case you want to show the dimension and filter some if its members, you have to add it as a RowGroupDescription/ColumnGroupDescription and add some filter on them - for example Label Filter. You can check this article for more details. I've also modified my sample project to use RowGroupDescriptions and filter some of the items.

Hope this helps.

Regards,
Rosen Vladimirov
Telerik

 

 

[hamish]

Thank you once again for your reply and I hate to sound completely dense but I am still confused. I have attached another screen print where I have tried to outline my requirements. I have the following specific questions and if possible can you answer each one
1. You say that I cannot include the filtered field (in my case Accnt). Then if I add an additional field to the SQL Server table (lets call it FilteredAccnt) which holds the same value can I use this to filter?.
2. If so  do I need to include this field in the Dimension of the cube? - I presume I must so therefore how do I prevent it from showing in the field selection list.?
3. I have followed your example to see what the unique field name is and I believe I now have it however it still does nothing. Is there any logs or anything else I can look at to try and debug this?
4. I have a extremely simple requirement - the database has a single table and no where near as involved as the Adventure works examples you have been sending - is it possible to have an example using something similar to what I have?

Thank you

[Rosen Vladimirov]

Hi Hamish,

Before answering directly to your questions, I would like to explain some scenarios when using RadPivotGrid and RadPivotFieldList:
1) You cannot use the same dimension in ReportFilter and Rows/Columns. If you try adding it to both Report Filter and Rows for example, the dimension will be included in only one of the groups.
2) As number 1 may sound a limitation, in case you want to apply filter and use the same dimension in Rows for example, you can use Label Filter or Value Filter. This way you can filter the items that will be shown in RadPivotGrid just like you have shown in your attached image. In order to achieve this, you can use the approach I've sent in my previous project.
3) You cannot hide the items absolutely when you use RadPivotFieldList - any user will be able to remove the filter and see the details for the other items.

Now I'm going straight to your questions:
1. and 2.  Adding an additional dimension will require much efforts - you have to add it to your cube, it will be shown in RadPivotFieldList top part where you'll be able to select it. After that you have to add it as Report Filter and filter the items. This whole operation is not needed - you can just use Label Filter in the same way as I've defined in the last project and they'll be filtered.
3. If the items are not filtered, it means the unique name is not correct. However you can check if in your Accnt menu (in rows just click on the arrow and a menu will be shown) there is a check mark in front of Label Filter - in case there is, it means there is a filter - click on the Label Filter and dialog will be opened - the items that you do not want to see in the UI will be unchecked in case you have set correct unique names.
4. I'm using Adventure Works as this is the default database provided by Microsoft for using OLAP Cubes. Also we have this cube public, so when I send you an example you are able to run it and see if the same is working on your side. At the moment I cannot provide a simpler database, but there is no difference in the behavior of RadPivotGrid, no matter the items count is 10 or 10 000.

Hope this helps. Feel free to contact us in case you have any other problems or concerns.

Regards,
Rosen Vladimirov
Telerik

 

 

[hamish]

Thank you for your reply and I think I understand. What you are saying that it is NOT POSSIBLE for me to apply filtering that CANNOT be changed by the user. Even if I manage to finally get items filtered the user will be able to go into the Filter Panel on the Fields and remove and/or change these filters?

[Rosen Vladimirov]

Hi Hamish,

In case you use Report Filter, there's no easy way to prevent the user to remove the filter (you'll have to edit RadPivotFieldList template in order to hide Report Filter box, but this will prevent your users from using another filters).

In case you use Label Filter, there is a possible solution. Here are the steps that you have to do:
1) Handle PrepareDescriptionForField event of your AdomdDataProvider. This event is raised whenever a user drags (or checks) a field in the top part of RadPivotFieldList. We'll need this event for the situation when your users remove the group description ACCNT and later add it again - in this case a new AdomdGroupDescription is created and your filter will not be applied any more. But this event will help you to add the filter, for example:

// Handle the PrepareDescriptionForFieldEvent in order to add your filter when the user drags the dimension from the FieldList tree (at the top) to rows or columns.
provider.PrepareDescriptionForField += provider_PrepareDescriptionForField;

void provider_PrepareDescriptionForField(object sender, Telerik.Pivot.Core.PrepareDescriptionForFieldEventArgs e)
{
    if (e.DescriptionType == Telerik.Pivot.Core.DataProviderDescriptionType.Group)
    {
        var description = e.Description as AdomdGroupDescription;

        // Add the filter only for this Member
        if (description.MemberName == "[Sales Territory].[Sales Territory Group]")
        {
            OlapSetCondition condition = new OlapSetCondition();
            condition.Comparison = Telerik.Pivot.Core.Filtering.SetComparison.DoesNotInclude;
            condition.Items.Add("[Sales Territory].[Sales Territory Group].&[North America]");
            OlapLabelGroupFilter labelGroupFilter = new OlapLabelGroupFilter();
            labelGroupFilter.Condition = condition;
            description.GroupFilter = labelGroupFilter;
        }
    }
}

2) Create Custom ContextMenuBehavior for RadPivotFieldList. As shown in this article, you can modify the context menu that is created for each group. In your case you can modify the menu that is created for your dimension (ACCNT) and remove all filter options:

public class CustomContextMenuBehavior : FieldListContextMenuBehavior
{
    protected override RadContextMenu CreateContextMenu(object dataContext)
    {
        var contextMenu = base.CreateContextMenu(dataContext);
        var groupDesc = dataContext as AdomdGroupDescription;

        if (groupDesc != null && groupDesc.MemberName == "[Sales Territory].[Sales Territory Group]")
        {
            // Remove the separator item
            contextMenu.Items.RemoveAt(3);

            // Remove Label Filter item
            contextMenu.Items.RemoveAt(3);

            // Remove Value Filter item
            contextMenu.Items.RemoveAt(3);

            // Remove Clear Filter item (Clear Filter is created only for groups which have filtered applied.
            contextMenu.Items.RemoveAt(3);
        }

        return contextMenu;
    }
}

After that just set the CustomContextMenu to your RadPivotFieldList:

<pivot:RadPivotFieldList x:Name="radPivotFieldList" Grid.Column="1" DataProvider="{StaticResource ADOMDDataProvider}">
    <pivot:FieldListContextMenuBehavior.Behavior>
        <local:CustomContextMenuBehavior />
    </pivot:FieldListContextMenuBehavior.Behavior>
</pivot:RadPivotFieldList>

So the final result of these operations is that you'll have a filter applied to your Dimension, even if the user removes it and add it back later. The context menu for this dimension will prevent the users from removing the filter.

I've recorded a short video to demonstrate the difference in the context menu for two dimensions - the one for which I've filtered the items and another that uses the default context menu. I've also modified my sample project to use all of the above, so you can test it immediately on your side. Hope this helps.

Regards,
Rosen Vladimirov
Telerik

 

 

[hamish]

Thank you. I have now managed to solve the issue by using the Report filters and I check that whenever a property changes on the data provider I check the number of filters and if my 'reserved filter' is not there I re add it. This has the effect of not allowing the user to remove the 'reserved filter', but can add and remove others of their own. I also removed the menu items from the filter (as you suggested) and it all appears to work exactly as we want it to.

Thank you for you help with this

[Rosen Vladimirov]

Hi Hamish,

I'm glad you have resolved the issue. Feel free to contact us in case you have any other problems or concerns.

Regards,
Rosen Vladimirov
Telerik