Wijmo OLAP PivotGrid Customization

Wijmo OLAP PivotGrid Customization

Posted by: Mahendra on 30 July 2019, 8:08 am EST

• 

  Mahendra

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 30 July 2019, 8:08 am EST

  We are using Wijmo OLAP PureJs.
  
  We have data with three rows

  1. Sales
  2. No.of Items
  3. Avg.Sales per Item

  Data contains Office, Region, Period, Component, Sort fields.

  Below are the cases which we need solution for:

  1. We need “Field Settings” for Cell and not for entire Column
     
     For Sales and No.of Items, we need Summary(Field Settings) to be set as Sum and
     
     for Avg.Sales per Item, it should be Average (Total sales/Total items)
  2. For Sales: Sum(Sales in Apr 2019 in Reg1 + Sales in Apr 2019 in Reg2) : (12000 = 10000+2000)
     
     For No.of Items: Sum(No.of Items in Apr 2019 in Reg1 + No.of Items in Apr 2019 in Reg2) : (115 = 100+15)
     
     For Avg.Sales per Item should be Sum(Sales in Apr 2019 in Reg1 + Sales in Apr 2019 in Reg2)/Sum(No.of Items in Apr 2019 in Reg1 + No.of Items in Apr 2019 in Reg2):(104.37 = 12000/115)
     
     But instead now it is Sum(Avg.Sales per Item in Apr 2019 in Reg1 + Avg.Sales per Item in Apr 2019 in Reg2) : (233 = 100+133.3)
  3. Is it possible to sort the grid based on the “Sort” field availble in the data.
  4. Format: Is it possible to have the format based on the Component.
     
     ex: We need “$” for Sales, “number” for No.of Items and “$” for Avg.Sales per Item. Some cases, “%”
     
     Because of the attachments file size limit, i am adding only html and js files.
• 

  Mahendra

  • Post Options:
  • Link
    Copy

  Posted 30 July 2019, 8:13 am EST

  Adding Attachments
  
  OLAP.zip

• 

  ashwin.saxena

  

  • Post Options:
  • Link
    Copy

  Posted 31 July 2019, 6:43 am EST

  Hi Mahendra,

  We are investigating your use case. We will give you an update as soon as we have further information.

• 

  ashwin.saxena

  

  • Post Options:
  • Link
    Copy

  Posted 1 August 2019, 6:15 am EST

  Hi Mahendra,

  Sorry for the delayed response.

  Regarding 1 & 2:

  The field settings are applied to a field and not on the column or cell level. If you wish to customize a few items in a field, you will need to use the getValue property of PivotField. The getValue function takes a single parameter that represents the data item being evaluated and should return the calculated value for the item. In your case, we can use the getValue to divide the “Avg sales per item” with the total no of items in the current group in which the item belongs and the PivotEngine will show the Sum in the PivotGrid which actually will be the average value.

  Regarding 3:

  You may use the sortComparer property of the field to sort the items according to the “Sort” field.

  Regarding 4:

  You may use the formatItem event of PivotGrid and format the data according to the component.

  Please refer to the sample below that shows all of the above behavior:

  https://stackblitz.com/edit/js-b7hfx8

  Let me know if you have any additional questions.

  API Reference:

  • "* “getValue: https://www.grapecity.com/wijmo/api/classes/wijmo_olap.pivotfield.html#getvalue”
  • “sortComparer: https://www.grapecity.com/wijmo/api/classes/wijmo_olap.pivotfield.html#sortcomparer”
    
    "

  Regards,
  
  Ashwin

• 

  Mahendra

  • Post Options:
  • Link
    Copy

  Posted 1 August 2019, 1:01 pm EST

  Thanks Ashwin for the response.
  
  I will try the options mentioned by you and will get back to you.

  Please donot close the post until i respond back.

  Thanks.

• 

  ashwin.saxena

  

  • Post Options:
  • Link
    Copy

  Posted 1 August 2019, 11:22 pm EST

  Hi Mahendra,

  Yes please take your time to try out the sample. Let us know if you face any issues.

  ~regards

• 

  Mahendra

  • Post Options:
  • Link
    Copy

  Posted 5 August 2019, 8:48 am EST

  Hi Ashwin,
  
  I have tried the sample and it helped me in adding sort and changing the value.
  
  But i tried the same with some modifications and it is not working in case of Field Settings: Sum.
  
  Please find the attachment for the code.
  
  I have added different data with Components as Comp1 and Comp2 and added two more fields N_value(numerator value) and D_value(denominator value).
  
  I need Comp2 value as (Sum(N_value)/Sum(D_value))*100.
  
  The code i added in getField is working fine for Field Settings: Max,Min, Average,First and Last.
  
  But for Sum, the value is changing and not as per the code written in getField method.
  
  I have tried this in “https://stackblitz.com/edit/js-b7hfx8” and same issue exists.(I couldn’t save with the new code)

  Please let us know if any other information is required.

  Thanks.

• 

  Mahendra

  • Post Options:
  • Link
    Copy

  Posted 5 August 2019, 8:49 am EST

  Adding Attachment
  
  wijmo_olap_sample.zip

• 

  ashwin.saxena

  

  • Post Options:
  • Link
    Copy

  Posted 6 August 2019, 1:10 am EST

  Hi Mahendra,

  The getValue property is used to convert the binding value of an item to a single calculated value. The values are still summarized by the PivotEngine according to the aggregate property of the field.
  
  In the case of “Avg.Sales per Item”, instead of adding all the values first, I was dividing each value with the total number of items and then the PivotEngine was summarizing these values ultimately finding the average sales for each item.
  
  But, in the case of “Comp 2”, the getValue callback converts each item to the calculated value of the whole itemsSource instead of just the current item. And the PivotEngine is summarizing this field due to which the data shown on the PivotGrid is not correct.
  
  You will need to change the getValue callback according to the requirement. For the “Comp 2” case, you just need to divide the calculated value by the total number of items in the group:

   var grp = getGroup(mainGrp, item);
  var densum = 0;
  var numsum = 0;
  for (var i = 0; i < grp.items.length; i++) {
  	numsum = numsum + parseFloat(grp.items[i].n_value);
  	densum = densum + parseFloat(grp.items[i].d_value);
  }
  return ((numsum / densum) * 100) / grp.items.length;
  

  Please refer to the updated sample below:

  https://stackblitz.com/edit/js-haddhv

  Regards,
  
  Ashwin

• 

  Mahendra

  • Post Options:
  • Link
    Copy

  Posted 8 August 2019, 6:58 am EST

  Can we add a condition in getValue with FieldSettings selected for Value.
  
  So that, If FieldSettings for Value
  
  in case of Sum i can add
  
  return ((numsum / densum) * 100) / grp.items.length;
  
  in case of Avg, Max,Min,First,Last i can add
  
  return ((numsum / densum) * 100);

  Thanks.

• 

  ashwin.saxena

  

  • Post Options:
  • Link
    Copy

  Posted 9 August 2019, 12:17 am EST

  Hi Mahendra,

  I am sorry but this is not possible due to the design of PivotEngine. We could add a condition in the getValue callback but this will only work for the first time when the page is loaded.

  ~regards