Filter pivot data dynamically

[Vladimir]

Hi,

I have a problem figuring out how to filter data for pivot grid dynamically. E.G. I want to preview data in specific date range but dynamically not by user clicks. Is it possible to done this from javascript by filtering datasource or using configurator. When I choose in pivot configurator date range it query OLAP normally so the question is how to do this dynamically.
In attachment is example of initial and wanted state.

[Georgi Krustev]

Hello Vladimir,

In general, you can define filters using the filter option of the DataSource instance (the PivotDataSource inherits from DataSource). You can see the suggested approach in action in this "how-to" demo, which shows how to filter a dimension.

Regards,
Georgi Krustev
Telerik

 

 

[Vladimir]

Hello Georgi,

Yes that's OK, but i need to filter deeper. This filter is filter part but I need to manipulate dynamically with include fields part (See picture at attachment for better understanding).

Regards,
Vladimir

[Georgi Krustev]

Hello Vladimir,

Thank you for the clarification. In that case, you will need to use the "in" operator and specify which members to include:

filter: [{
  field: "[Date].[Calendar]",
  operator: "in",
  value: "[Date].[Calendar].[Calendar Year].&[2005],[Date].[Calendar].[Calendar Semester].&[2005]&[2],[Date].[Calendar].[Calendar Semester].&[2007]&[1],[Date].[Calendar].[Calendar Semester].&[2008]&[2]"
}]

Check the updated how-to demo.

We will update the documentation in future to make the filter usage easier.

Regards,
Georgi Krustev
Telerik

 

 

[Vladimir]

I run into another problem. Can i get measures, rows, levels and other metadata values from javascript i.e. dynamically.
e.g. I want to get all member name from [Location].[States]. So dynamically I would query something("[Location].[States]") and output would be an array of members ("[Location].[States].&["Colorado"], [Location].[States].&["Washington"], ...") or something like that.

[Georgi Krustev]

Hello Vladimir,

I would suggest you use the discover method to get the required information from the cube. The method uses the XMLA standard to build the proper request.

Regards,
Georgi Krustev
Telerik

 

 

[Vladimir]

Yes it does the trick. The last question is how to apply more then one field in filter. e.g. timeFilter, salesFilter etc... (as many as I want) 

filter: [{
  field: "[Date].[Calendar]",
  operator: "in",
  value: "[Date].[Calendar].[Calendar Year].&[2005],[Date].[Calendar].[Calendar Semester].&[2005]&[2],[Date].[Calendar].[Calendar Semester].&[2007]&[1],[Date].[Calendar].[Calendar Semester].&[2008]&[2]"
},
{
  field: "[Some].[Another filter]",
  operator: "in",
  value: "...
}]

[Georgi Krustev]

Hello Vladimir,

In general, the data source will convert the applied array of filters into object that has "and" logic between filters. Something like this:

filter: {
  logic: "and",
  filters: [{
    field: "[Date].[Calendar]",
    operator: "in",
    value: "[Date].[Calendar].[Calendar Year].&[2005],[Date].[Calendar].[Calendar Semester].&[2005]&[2],[Date].[Calendar].[Calendar Semester].&[2007]&[1],[Date].[Calendar].[Calendar Semester].&[2008]&[2]"
  },
  {
    field: "[Some].[Another filter]",
    operator: "in",
    value: "...
  }]
}

If you would like to add filters dynamically, then you will need to modify that structure and re-set the source filter using filter method.

Regards,
Georgi Krustev
Telerik