asked on December 3, 2021
We are getting this error message when the data source for a report produces only the header data with no actual data: "Wrong formula format. Please check formula details. The 'sum' aggregation in the 'Amount Remaining' formula is not valid.
The "Amount Remaining" formula is:
"sum(\"Budget\") - sum(\"Encumbrance\") - sum(\"Projected\") - sum(\"Expense\")"
The data returned by the data source is:
Fund Name,Fund Code,Cost Center Code,Cost Center Name,Object Code,Object Code Name,Org Code,Acct PI ID,Acct PI Name,Trx Org Code,Fund Resp Org Code,Budget,Encumbrance,Projected,Expense,Adjustment
The Budget, Encumbrance, Projected, and Expense values are used as measures. For example:
"measures": [
{
"uniqueName": "Budget",
"aggregation": "sum",
"format": "-x71hehs518100"
},
Apparently the fact that there is no data is what is triggering this error message. We expected it to instead just cause the report to show nothing.
I noticed this ticket and upgraded our version of Flexmonster from version 2.8.12 to 2.9.12. When I press ctrl-alt-i, the popup window says "November 2021 (v. 2.9.12) build 11/15/20201 02:45:12". The problem did not go away.
⋅ December 7, 2021
Hello, Mark,
Thank you for writing to us.
The reason you get this error message is that Flexmonster can't define the type of fields used in your formula. If there is no data for these measures in your dataset, Flexmonster can't define whether these fields are numbers. Sum aggregation is not supported for non-numeric fields. Therefore, we suggest using mapping to define the field’s data type. For example,
"mapping": {
"Budget": {
"type": "number"
},
"Encumbrance": {
"type": "number"
},
"Projected": {
"type": "number"
},
"Expense": {
"type": "number"
},
"Adjustment": {
"type": "number"
}
}
Please let us know if it works for you. Feel free to contact us if other questions arise.
Kind regards,
Nadia
⋅ December 10, 2021
I tried this, and it didn't work. I still get the same error message - see screen shot, attached. I've also attached our report definition.
Oh - looks like this ticketing system can only attach one file. I've chosen the report file over the screen shot that I created.
⋅ December 10, 2021
I attached the screenshot of the error message as a separate "answer".
⋅ December 13, 2021
Hello, Mark,
Thank you for your response.
It seems that there's no mapping in your report definition. Kindly note that it should be defined in the DataSource object. For example,
report: {
dataSource: {
type:"csv",
filename: <URL-to-your-file>,
mapping: {
"Budget": {
"type": "number"
},
"Encumbrance": {
"type": "number"
},
"Projected": {
"type": "number"
},
"Expense": {
"type": "number"
},
"Adjustment": {
"type": "number"
}
}
}
}
We hope it helps. You are welcome to write to us in case further questions arise.
Kind regards,
Nadia
⋅ December 16, 2021
I tried this, and still got the same error. See attached json file.
What now?
⋅ December 17, 2021
Hello, Mark,
Thank you for your response.
We have created an example using your report and dataset with the same structure as yours. Please check it on the following JSFiddle: https://jsfiddle.net/flexmonster/2q3uvost/. It seems that everything works as expected.
If the example above won't help, as a workaround, you can use validateFormulas property of the Options object set to false.
Please let us know if it works for you. Feel free to contact us if other questions arise.
Kind regards,
Nadia
⋅ December 20, 2021
This does not work. I still get the same error "Wrong formula format. Please check formula details.
The 'sum' aggregation in the 'Amount Remaining' formula is not valid."
See attachment for the exact version that I am using.
Please note that the error occurs when no data -- other than the header row -- is returned for the report. The JSFiddle that you sent has some data so it does not show the problem.
Meanwhile, I am setting the validateFormulas property to false as a temporary fix, but it is not the right long-term solution.
⋅ December 21, 2021
Hello, Mark,
Thank you for your response.
We prepared another example with the dataset consisting of only header row: https://jsfiddle.net/flexmonster/2q3uvost/33/ . As you can see, everything works as expected.
Our team recommends carefully checking what is different on your side. It would be nice if you could edit the example to describe the issue.
Looking forward to your response. Feel free to contact us if other questions arise.
Kind regards,
Nadia
⋅ December 28, 2021
Hello, Mark,
Hope you are doing well.
We were wondering if you had time to check the new example from our previous response.
Looking forward to your response.
Kind regards,
Nadia
⋅ December 28, 2021
It still fails for me, and I verified that I am running the same code that you provided.
I have attached:
I note that the only the first report request fails. The second and subsequent requests (hitting the refresh button) sometimes but not always shows an empty grid with the header row and no error message -- just as you show in the fiddle.
But if I exit from the report and enter it again, I get the same failure. It is inconsistent.
⋅ December 28, 2021
This ticketing system is buggy -- I cannot edit the above answer a second time. And attaching multiple files is flaky.
I intended to edit the above answer to add a third attachment:
3. Screen shot of network traffic when the failure occurs.
Here is the network response when the failure happens:
Fund Name,Fund Code,Cost Center Code,Cost Center Name,Object Code,Object Code Name,Org Code,Acct PI ID,Acct PI Name,Trx Org Code,Fund Resp Org Code,Budget,Encumbrance,Projected,Expense,Adjustment
⋅ December 30, 2021
Hello, Mark,
Thank you for the details.
We tried to reproduce the described issue, but it seems that this case is not reproducible on our side(it can be checked in the following JSFiddle: https://jsfiddle.net/flexmonster/2q3uvost/33/). Could you please provide us with access to your testing environment where the reported issue can be reproduced? It will help us to continue the investigation.
Regarding the reporting of the unexpected behavior of our ticketing systems:
You have mentioned in this thread that the ticketing system can only attach one file. Kindly note that attaching multiple files is possible. It is required to browse in the pop-up window the files you want to upload from your computer and use Ctrl/Cmd + select to choose multiple files.
Speaking of the issue with editing the answer, our team has checked the described behavior on our side - everything seems to work correctly. If you are still facing difficulties while editing answers, we would be thankful for providing us with a video recording of the reported issue.
Looking forward to your response.
Kind regards,
Nadia
⋅ January 10, 2022
Hello, Mark,
Hope you are doing well.
We were wondering if you managed to resolve the issue.
Looking forward to hearing your feedback.
Kind regards,
Nadia
⋅ January 12, 2022
We cannot give you direct access to our systems for security reasons. What I can do is show you what is happening in a screen-sharing setup. Will that work?
Regarding attaching files - my expectation is that I attach files at separate times, i.e. type some text into the ticketing system, add a file, type some more text, add another file, etc. If your system does not permit that, then at least it should have a comment on the screen showing that users are restricted to adding multiple files in one step.
⋅ January 14, 2022
Hello, Mark,
Thank you for the response.
We have sent an answer via email regarding the original issue.
Thank you for your suggestion about the ticketing system. We will consider it in future system updates.
Kind regards,
Nadia