asked on August 31, 2020
Hi,
We are looking to create some space between certain columns in a report. Is there a way to do accomplish this through the Flexmonster front-end without touching the `dataSource`?
One way we've tried to accomplish this is by adding a calculated value that subtracts from itself, always equaling 0.
The problem with this approach is that we'd like the values to be blank, not '0'.
Is there any way to accomplish this on the front end?
Thanks.
⋅ September 1, 2020
Hello, David,
Thank you for writing to us.
A possible solution is to create a calculated value that divides a value by zero. Then you could apply Flexmonster number formatting and set the divideByZeroValue parameter to " ", for example:
var pivot = new Flexmonster({
container: "#pivot-container",
componentFolder: "https://cdn.flexmonster.com/",
report: {
dataSource: {...},
"slice": {
"rows": [...],
"columns": [{...}],
"measures": [
{
"uniqueName": "Spacer",
"formula": "0/0",
"caption": "",
"format": "space"
},
...
]
},
"formats": [
{
"name": "space",
"divideByZeroValue": ""
}
]
}
});
As a result, an empty column will be displayed on the grid.
We have prepared a JSFiddle example for illustration: https://jsfiddle.net/flexmonster/024wbtpd/
Please let us know if this works for you.
Kind regards,
Vera
⋅ September 1, 2020
Thanks, Vera. This is a nice solution. I guess the only problem is that if someone tries to edit the calculated field via the GUI the blank name gets overwritten with "Formula #1".
I ended up adding a blank column to the SQL data and using index("spacer") to make the calculated values blank. Is this desired behavior for the Index aggregation? I couldn't find any documentation about the calculation anywhere.
⋅ September 2, 2020
Hello, David,
Thank you for your reply.
We are glad to hear that you have found a solution.
As for the index aggregation:
The index aggregation is based on the following formula: ( cell value * grand total ) / ( row total * column total )
Since all of the cells are empty, then it is expected that the result of the index aggregation will also be an empty cell.
Another solution could be to use the customizeCell hook to make the "Spacer" cells empty.
Also, we would like to mention that it is possible to add an empty column via the mapping property of the dataSource object. This way it won't be required to modify your data set.
We have prepared a JSFiddle example for illustration: https://jsfiddle.net/flexmonster/95fva1Lo/
In the example, we also disabled the filter and context menu for the "Spacer" field.
Please let us know if this helps.
Kind regards,
Vera