[OLAP] Need a way to filter data in crosstab PivotGrid

[OLAP] Need a way to filter data in crosstab PivotGrid

Posted by: james.ingham on 23 October 2019, 7:47 pm EST

• 

  james.ingham

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 23 October 2019, 7:47 pm EST - Updated 3 October 2022, 5:44 pm EST

  Dear Support,

  We have built a crosstab in PivotGrid by adding rows and columns. However we didn’t figure out how to filter values from the cross grid. It looks like all filters only apply to the underlying data from Values field, regardless of any Rows/Columns fields you specified.

  Here’s a jsfiddle demo:
  
  http://jsfiddle.net/runninghare/acrtzw2y/17/

  

  614×672 50.4 KB

  Is there any way to do this?

• 

  manish.gupta

  

  • Post Options:
  • Link
    Copy

  Posted 25 October 2019, 4:14 am EST

  Hi James,
  
  As we understand, you would like to hide highlighted row in case Australia has no sales using default Filter or Custom filter or on other events.

  If yes, we are sorry, we are unable to find a way. In this case, you may apply the filter on Sales Person field by unchecking the Sue from ValueFilter.

  Please confirm your requirement in case it does not fulfil your requirement.

  Regards,
  
  Manish Gupta

• 

  james.ingham

  • Post Options:
  • Link
    Copy

  Posted 27 October 2019, 6:11 pm EST

  That’s right, we want to hide the highlighted row with Sales Person = Sue. However it is not as simple as unchecking Sue from ValueFilter because it should be based on the condition “Australia sales = NULL”, not because his name is Sue.

  Basically our requirement is to apply filters AFTER the pivot grid is created, so I don’t think it’s going to work to set filters on those fields themselves.

• 

  abhishek.dutta

  

  • Post Options:
  • Link
    Copy

  Posted 28 October 2019, 1:05 am EST

  Hi James,
  
  The only way I could think of is to apply the Filter on the underlying collection view to filter out the Null values for the specific field.

  Please refer to this sample: ( Filtering Section)
  
  https://demos.wijmo.com/5/purejs/collectionviewintro/collectionviewintro/

• 

  james.ingham

  • Post Options:
  • Link
    Copy

  Posted 28 October 2019, 7:23 pm EST

  Hi Abhishek,

  This might work in some cases but doesn’t look like an ideal solution, because you have to program UI yourself in this standard cross-tab pivot grid, and it only supports value filters but not the condition filters.

  Is this something Wijmo may consider implementing in OLAP? Wijmo’s OLAP solution is fantastic and we don’t want to stop using it simply because of this missing feature.

• 

  ashwin.saxena

  

  • Post Options:
  • Link
    Copy

  Posted 11 November 2019, 12:42 am EST

  Hi James,

  This is the limitation of this solution that is why we did not provide you with this solution earlier. Moreover, we have added an enhancement request for your requirement with the internal tracking id 406082. We will update you as soon as we will hear from the dev team.

  Regards,
  
  Ashwin

• 

  ashwin.saxena

  

  • Post Options:
  • Link
    Copy

  Posted 4 February 2020, 7:56 am EST

  Hi James,

  The devs have said that you may use the filters property of CollectionView class to add cross-filtering:

  // create a filtered CollectionView
    let view = getData(1000);
    view.filters.push((item) => {
      return item.country != 'Australia' || item.sales != null
    });
   
    var ngPanel = new wjcOlap.PivotEngine({
      itemsSource: view, // use the filtered view as a source
      rowFields: ["Sales Person"],
      columnFields: ["Country"],
      valueFields: ["Sales"]
    });
  

  ~regards