asked on July 16, 2024
Hello,
I am evaluating Flexmonster as a potential pivoting tool for an Angular app where I want Flexmonster to take rows of data, which will include a Category, Date, and Amount, and total both the columns and rows and use the sum(Amount) as the aggregate function for the pivot data cells. For example, given the CSV records shown below, I am looking to get a pivot grid like shown below. Does Flex monster support totalling the date column values as shown below? Also, are the specific requirements for CSV format? I noticed that the sum aggregate function was not available for an Amount (ie numeric) field when I tried using one of my CSV files that didn't have any text columns double-quoted and just used the comma delimeter; but, when I used a different file, where all the columns were double-quoted as if they were text columns, then the sum aggregate function was available for my Amount field.
Pivot Grid:
|Category |Jan |Feb|
+-----------------+----+---+
|Auto Maintenance | |130|
+-----------------+----+---+
|Food |300 |350|
+-----------------+----+---+
|Utilities |490 |490|
+-----------------+----+---+
CSV data:
Category,Date,Amount
Auto Maintenance,2/1/2024,130
Food,1/1/2024,150
Food,1/15/2024,150
Utilities,1/1/2024,100
Utilities,1/3/2024,40
Utilities,1/15/2024,250
Food,2/05/2024,150
Food,2/24/2024,200
Utilities,2/1/2024,100
Utilities,2/3/2024,40
Utilities,2/15/2024,250
Thank you,
Russ
⋅ July 16, 2024
Hello, Russ!
Thank you for reaching out to us.
Kindly note that Flexmonster tries to define the data types from the provided data files automatically. If you need to set the data type explicitly, add a mapping object to your dataSource configurations as follows:
report: {
dataSource: {
type: "csv",
filename: "data.csv", //path to your csv data
mapping: {
"Amount": {
"type": "number"
},
//mapping for other fields
},
},
//other report properties
}
We have prepared a JSFIddle illustrating how to set mapping to the provided sample data(converted to JSON format for convenience): https://jsfiddle.net/flexmonster/pwtL5jz6/.
You can read more about mapping in our docs: https://www.flexmonster.com/doc/mapping/.
Please let us know if the suggested approach works well for you.
Looking forward to hearing from you.
Kind regards,
Solomiia
