Dear visitor, despite the COVID-19 outbreak, our team continues operating at full speed.

Also, here is the form where you can apply for a special discount and we will contact you with possible options. Stay safe and continue achieving your business goals.

Fill the form
Get Free Trial
Get Free Trial

Create Flat Fields In Compact/Classic Form

Answered
Scott Oliver asked on April 2, 2020

Hello,
 
Hope you are all well and staying safe.
 
We are trying to replicate the attached screenshot “Flexmonster Example After”. The key here is creating multiple “flat columns” before the pivoted data, such as the “Notes” field.
 
Pushing the boundaries further, it would be even better to build flat columns with your calculation tools. For example, using the grand total for each row, I could create a column which multiplies it by an exchange rate, to show the figure in USD. Or simply take two flat columns I have created like “Total Forecast” and “Total Feaso” and minus the difference, as per my screenshot.
 
Of course, we would be happy to modify our JSON data (attached) to suit bringing these flat/static fields through.

Good luck. Loving the product!
 
https://imgur.com/a/VGrFdMb

9 answers

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster April 6, 2020

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

Public
Scott Oliver April 8, 2020

Thanks for the response.

From a layout perspective I can create something very similar using the classic form. However, I am struggling with the data.

My hope was that I could pass through a “header” record with some basic information, in addition to my original data.

{‘GL Code’: ‘0110 – PROPERTY SALES’,’Cost Centre’: ’01 – PROPERTY SALES’,’Notes’: ‘This is a test note’}

This would mean the original data would now look like:

{‘Date’: ‘2020-04-01′,’Feasibility’: 0,’Forecast’: 0,’Actual’: 0,’GL Code’: ‘0110 – PROPERTY SALES’,’Cost Centre’: ’01 – PROPERTY SALES’}

Note: I have removed the “Notes” field from this dataset.

However the data in the notes column doesn’t come through, it simply shows as “(blank)”. Firstly, is there a way to rectify this using the above data method?

The attached screenshot is how I would have imagined FM to behave, splitting the data into two rows as they are grouped differently. This is closer, however still not perfect. In the attached screenshot, I would want. to remove the blank row and summate the numbers. Any thoughts?

Attachments:
Flexmonster.png

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster April 8, 2020

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

Public
Scott Oliver April 8, 2020

Again, I appreciate the feedback and support.
 
Unfortunately storing the information in each piece of data isn’t feasible. Mainly for two reasons:
 

  • The information is stored in the parent record within our database (to provide context this is the GL code record), so we would need to inject each child each time this is modified
  • Secondly when displaying 50,000 records for merely 100 GL codes the size of data would be rather inefficient, hence the hope to display “header” records for each GL code which would store the note

 
Do you know of any way that I can achieve this within FM? I have attached a smaller dataset as an example just in case I haven’t quite explained myself well enough.
 
The key goal here is for us to take header information and place this in front of the pivot. Although the header is a different data source we do have the capability to merge this if required.
 
Side note, unfortunately notes is just an example, we are looking for a number of fields to behave in the same manner, so a tool tip wouldn’t suffice. But I love the creativity!

Attachments:
Sample JSON.txt

Public
Scott Oliver April 9, 2020

Also, is there a way for a calculated value to not behave as a value. I realise that sounds very counter-intuitive but the reason behind this is again, I wouldn’t want the value being distributed by column (date in my example)?
 
I’m looking to take two flat values, say committed costs and uncommitted costs and add them together to create a third flat value. This value wouldn’t be broken up by column/grouping.

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster April 10, 2020

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

Public
Scott Oliver April 14, 2020

Hi Illia,
 
Thank you so much for your continued support. The proposed solution may well just work.
 
Can I trouble you to show me how to add in the uncommitted and committed columns into the customise cell loop (as well as notes)? That way I can I replicate the solution for any other data I need to parse through to the header.
 
Is it possible to unfreeze the headers? I suppose what I am really asking is the notes, uncommitted costs, committed costs and other fields I look to add to the flat array won’t be required to behave as “headers”. I will be loading a lot of this flat data through, so I wouldn’t want these fields to freeze.
 
Also, in your example/solution, the drill through (double click) on the value is now broken. Not showing the information which makes up that value.
 
Lastly, will it still be possible to send through an ID for both the “flat” information as well as the standard data? I’m looking to implement an on click event that returns me the ID of the record selected. I’ve successfully done this for each date, but when doing so for the “header” I am returned with “null”.

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster April 15, 2020

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

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster April 15, 2020

Screenshot:

Attachments:
screenshot.png

Please login or Register to Submit Answer