Create Flat Fields In Compact/Classic Form

Hello, Scott,
 
Thank you for providing us with screenshots and the sample data set.
 
We would like to kindly inform that it is possible to display the data in a pivot or a flat form using Flexmonster, but not at the same time.
 
In order to achieve a similar functionality, the data set can be modified in the way it contains appropriate "Forecast" and "Feaso" properties for each existing record. However, in such case their totals have to be calculated on the server side, as well as the calculated value "Feaso +/-".
 
An alternative solution is to create two instances of the component. Each instance will be dedicated to displaying the part of the data set in an appropriate form: the flat table for hierarchies "Notes", "Total Forecast", "Total Feaso", "Feaso +/-", and the compact/classic one for hierarchies "Cost Centre", "Gl Code" and the measure "Feasibility".
 
Please check out an example we have prepared. It uses the data set you have provided and demonstrates the described approach. Furthermore, the calculated measure "Feaso +/-" can be added using the following formula:

"formula": "sum(\\"Forecast\\") - sum(\\"Feaso\\")"

 
We hope such a workaround works for you.
Please let us know if further questions appear.
 
Best regards,
Illia

Hello, Scott,
 
Thank you for your feedback.
 
In case the unique note needs to be attached to every member of the "GL Code", it is possible to achieve the result by including an appropriate property to each member of the initial data set. For example:

[
  {
      "Date": "2019-06-01",
      "Feasibility": 0,
      "Forecast": 0,
      "Actual": 0,
      "GL Code": "6006 - ADVERTISING,SIGNAGE & MEDIA",
      "Cost Centre": "6 - MARKETING",
      "Note": "Note for 6006"
  },
  ...
}

 
In such a case, if the compact form is chosen, the result will be similar to your requirements. Please see an example we have prepared, demonstrating the described approach.
 
However, the mentioned way poses other problems, for example, the possibility to filter by notes. It may be an inconsistent behavior for the majority of use cases.
Therefore, our team would like to kindly suggest considering using another approach in order to implement notes. The note will appear in a tooltip on hovering of an appropriate member. In order to do that, the following steps should be used:

1. The "property" type should be assigned to the "Note" hierarchy. The type servers for setting member properties. This field will not be shown in the Field List. Also, specify the hierarchy it should be associated with: "GL Code".
2. Fill the title attribute of each required cell with a content of an appropriate note. It is achievable using the customizeCell API:

   flexmonster.customizeCell((cell, data) => {
      if (data.hierarchy && data.type == "header" && data.hierarchy.uniqueName == "GL Code") {
        for (let elem of data.rows)
          if (elem.hierarchyName == "GL Code") {
            cell.attr.title = elem.properties["Note"];
            console.log(cell.attr);
          }
      }
    });
   

   The method chooses members of the "GL Code" hierarchy and adds the "title" attribute with an appropriate content for each chosen cell.

 
We have prepared an as well. You are welcome to check it out.
 
More information about data types in JSON, including the property type, can be found in our documentation.
Detailed information about the customizeCell API and its parameters: customizeCell method.
 
We hope one of the proposed workarounds work for you.
Do not hesitate to contact us in case of additional questions.
 
Best regards,
Illia

Hello, Scott,
 
Thank you for providing us with a data set.
 
It did manage to help us understand your requirements better. Even so, our team would like to kindly draw your attention to the fact that your case seems to be a bit different from the use case fully appropriate for a pivot. Therefore, an approach described below is rather the workaround, and it is the best we can propose for now due to the fact that your requirements seem to be out of boundaries of the classic pivot table.
 
In order to avoid doubling of the information and being able to load two data sources (the main one, and the header one) without the necessity to merge them, we can recommend checking out the following approach.
 
First of all, the main data source should be represented without any additional information from the "header" part.
 
Also, it is required to create blank columns which are going to be wrappers for data from the header.
It is achievable using the mapping object. Simply list all hierarchies from the main data set and append desired ones from the header data set. For example, the following code snippet, where the object marked in bold is absent in the main data set.

mapping: {
  "Date": {
    "type": "date string"
  },
  "Feasibility": {
    "type": "number"
  },
  ...
  "Notes": {
    "type": "string"
  }
}

 
Next, use the customizeCell API provided by Flexmonster in order to fill those blank columns with appropriate information from the header. The method takes the function as a parameter and passes the Cell Builder and the Cell Data Object as its parameters.
 
The Cell Data Object contains all information about the cell including all hierarchies from the row it is rendered on. Therefore, it is possible to specify the hierarchy it belongs to and fill it with appropriate information using the text property of the Cell Builder object.
 
We have prepared an example using the data set you have provided in order to demonstrate the described approach.
The header is represented as a flatInfoArray array (line 87). The original data set does not contain any additional information, the "Notes" hierarchy was removed as well.
 
Also, our team would like to kindly provide some additional explanation about the function passed to the customizeCell method in the example:

pivot.customizeCell((cell, data) => {
  let related = {};
  let flatInfo = {};
  if (data.type == "header" && data.hierarchy && data.hierarchy.uniqueName == "Notes") {
    for (let row of data.rows)
      related[row.hierarchyName] = row.caption;
    flatInfo = flatInfoArray.filter(function(elem) {
      return elem["Cost Centre"] == related["Cost Centre"] &&
        elem["GL Code"] == related["GL Code"];
    });
    cell.text = flatInfo[0].Notes;
  }
});

 
The related object is going to be used in order to save the unique identifier of the note: appropriate members of the "Cost Centre" and the "GL Code" hierarchies. The flatInfo object is dedicated to storing the object from the header part of the data set related to the chosen cell.
 
The first condition is dedicated to choose only cells from the "Notes" hierarchy and avoid mutation of others. The loop serves for filling the related object with the names of members related to the chosen cell. It iterates over the rows array which stores information about all hierarchies from the row the chosen cell is currently on.
 
Finally, the filtering is done to choose an appropriate record from the header and assign it to the flatInfo object.
The last step is to fill the cell with the value of a Notes property of the chosen record.
 
More information about the customizeCell API in our documentation.
Also, here is the page didicated to the mapping object and its properties.
 
Talking about calculated values, we would like to kindly inform you that such values can not be created and displayed in headers. Therefore, we recommend developing the custom logic of calculating required values and pass them in the way described above, as the simple text/number.
 
Our team sure hopes the proposed solution works well for you.
Please contact us in case other questions occur.
 
Kind regards,
Illia

Hello, Scott,
 
Thank you for your feedback.
 
We have complemented the example provided earlier in the way it displays "Committed Costs" and "Uncommitted Costs" hierarchies in the "flat" part of the grid. The issue with the drill-through feature was fixed as well.
 
The problem with the drill-through you are facing is due to the fact that the customizeCell method applies to the drill-through view as well. However, the difference in structure entails the exception and prevents the component from rendering the grid itself.
In order to avoid such a behavior, the following changes can be made in the provided function:

...
if (flatInfo[0])
      cell.text = flatInfo[0].Notes;
  }
});

As can be seen, the snippet marked in bold serves as an additional validator that prevents the attempt to replace the text property of the cell object with an undefined value.
 
However, in such a case, all "flat" values from the header part will be displayed as "blank" cells in drill-though. They can be hidden using the drillThrough property of the slice object, which allows pre-defining slice for the drill-through view, for example:

...
"drillThrough": [
  "Actual",
  "Cost Centre",
  "Date",
  "Feasibility",
  "Forecast",
  "GL Code",
  "Notes"
]

Detailed information about the slice object and its properties can be found in our documentation.
 
Moreover, the content of the "Details" block in the drill-through view will contain all "blank" members of the record, as shown in the screenshot in attachments.
An example provided earlier demonstrates one of the possible approaches to hide such members: the caption for blank members is changed through the localization:

localization: {
  grid: {
    blankMember: ""
  }
}

And all extra hyphens after the last existing members are removed each time the user drill through the cell:

pivot.on('drillthroughopen', () => {
  let detailsHolder = document.querySelector("#fm-drillthrough-view > div.fm-ui-element.fm-ui.fm-details-container > span:nth-child(1) > b");
  let details = detailsHolder.innerText.split('');
  for (let i = 0; i < headerInformation.length * 2; i++)
    details.pop();
  detailsHolder.innerText = details.join('');
});

The code snippet above uses the drillthroughopen event, which is triggered when the drill-through pop-up window is rendered. More about the event itself in our documentation.
The headerInformation array marked in bold consists of names of hierarchies from the header part. Therefore, getting its length allows calculating the number of symbols which have to be removed from the end of the list.
 
Concerning the possibility to unfreeze headers, our team would like to kindly inform you that it is not possible.
 
Finally, talking about the recordId property, we would like to kindly inform you that in case the clicked cell is not a value, the mentioned property will be set to null. It applies to all headers on the grid, not only the ones added through the mapping property. In case the identifier needs to be implemented for such cells, another approach should be developed.
 
We hope it helps.
Please contact us in case of additional questions on this point.
 
Kind regards,
Illia

Screenshot: