Hello,
I am using Aspose word (version:23.3) java, trying to generate the bar chart with the legend that contains the sum of the grouped data
I tried to create a similar template using Microsoft-word and Aspose LinqEngine, but the sum of the grouped data is not showing.
We need the bar chart and the legend with the grouped data sum like the below example (picture attached)
Can you please help to achieve this?
Thank you.
Please check the attached Template.docx (25.9 KB). You can use the following code to build a report from it:
String json = "{ values: [ "
+ "{ category:\"A\", value1:5 },"
+ "{ category:\"A\", value1:5 },"
+ "{ category:\"A\", value1:5 },"
+ "{ category:\"B\", value2:1 },"
+ "{ category:\"B\", value2:1 },"
+ "{ category:\"B\", value2:1 },"
+ "{ category:\"B\", value2:1 },"
+ "{ category:\"C\", value3:2 },"
+ "{ category:\"C\", value3:2 },"
+ "{ category:\"C\", value3:2 },"
+ "{ category:\"C\", value3:2 }"
+ " ] }";
JsonDataSource dataSource = new JsonDataSource(
new ByteArrayInputStream(json.getBytes(StandardCharsets.UTF_8)));
Document document = new Document("Template.docx");
ReportingEngine engine = new ReportingEngine();
engine.buildReport(document, dataSource, "values");
document.save("Out.docx");
@ivan.lyagin Our Json data is more than just the values array. We have other data in that Json from single value to arrays etc. I see you specified the datasource as “values” like the below which you shared
ReportingEngine engine = new ReportingEngine();
engine.buildReport(document, dataSource, "values");
Our current code is not passing any specific data source, we are just passing document and json data.
engine.buildReport(document, jsonDataSource);
How can we get the sum in that case?
Thanks for your help!
The approach is the same but instead of values, you may use the same collection that you pass to the foreach tag in the chart’s title. This also implies that instead of value1 and others, corresponding properties from your JSON object should be used.
@ivan.lyagin Thanks for your help so far!
So using the same example with more added data to fill in a document how can I iterate through values array and get the sum while still getting other data for other parts of the document.
See sample Json:
String json = "{ name: \"John doe\", ",
+ "age: 18 ,"
+ "values: [ "
+ "{ category:\"A\", value1:5 },"
+ "{ category:\"A\", value1:5 },"
+ "{ category:\"A\", value1:5 },"
+ "{ category:\"B\", value2:1 },"
+ "{ category:\"B\", value2:1 },"
+ "{ category:\"B\", value2:1 },"
+ "{ category:\"B\", value2:1 },"
+ "{ category:\"C\", value3:2 },"
+ "{ category:\"C\", value3:2 },"
+ "{ category:\"C\", value3:2 },"
+ "{ category:\"C\", value3:2 }"
+ " ] ,"
+ "values2: []"+
+ "}";
For data like this, you may use ds.values.sum(...), if providing the data source name as follows:
engine.buildReport(document, dataSource, "ds");
How can we group by the category in this case and get the sum for those groups for bar graph legend.
Example I am looking for the below:
Name: sum:
category A 15
category B 4
category C 8
Currently what I am getting is the following:
category A 15
category A 15
category A 15
category B 4
category B 4
category B 4
category B 4
category C 8
category C 8
category C 8
category C 8
Here is Json:
String json = "{ name: \"John doe\", ",
+ "age: 18 ,"
+ "values: [ "
+ "{ category:\"A\", value1:5 },"
+ "{ category:\"A\", value1:5 },"
+ "{ category:\"A\", value1:5 },"
+ "{ category:\"B\", value2:1 },"
+ "{ category:\"B\", value2:1 },"
+ "{ category:\"B\", value2:1 },"
+ "{ category:\"B\", value2:1 },"
+ "{ category:\"C\", value3:2 },"
+ "{ category:\"C\", value3:2 },"
+ "{ category:\"C\", value3:2 },"
+ "{ category:\"C\", value3:2 }"
+ " ] ,"
+ "values2: []"+
+ "}";
I have modified the original template according to the suggestion in my previous comment: Template_Modified.docx (25.9 KB). It seems working as expected. Here is complete code to build a report from it:
String json = "{ name: \"John doe\", "
+ "age: 18 ,"
+ "values: [ "
+ "{ category:\"A\", value1:5 },"
+ "{ category:\"A\", value1:5 },"
+ "{ category:\"A\", value1:5 },"
+ "{ category:\"B\", value2:1 },"
+ "{ category:\"B\", value2:1 },"
+ "{ category:\"B\", value2:1 },"
+ "{ category:\"B\", value2:1 },"
+ "{ category:\"C\", value3:2 },"
+ "{ category:\"C\", value3:2 },"
+ "{ category:\"C\", value3:2 },"
+ "{ category:\"C\", value3:2 }"
+ " ] ,"
+ "values2: []"
+ "}";
JsonDataSource dataSource = new JsonDataSource(
new ByteArrayInputStream(json.getBytes(StandardCharsets.UTF_8)));
Document document = new Document("Template_Modified.docx");
ReportingEngine engine = new ReportingEngine();
engine.buildReport(document, dataSource, "ds");
document.save("Out.docx");
Please let us know, if something else is expected.
Thanks for your help.
What if we wanted to keep the group legend dynamic. What if the json added/deleted new group?
We wanted to use group by or anyway to construct the legend dynamically for any amount of groups. The shared example has group A, B & C hard coded but what if in the Json we add or remove a new group than it will still show the hard coded group.
see sample below where group A is removed:
"{ name: \"John doe\", "
+ "age: 18 ,"
+ "values: [ "
+ "{ category:\"B\", value2:1 },"
+ "{ category:\"B\", value2:1 },"
+ "{ category:\"B\", value2:1 },"
+ "{ category:\"B\", value2:1 },"
+ "{ category:\"C\", value3:2 },"
+ "{ category:\"C\", value3:2 },"
+ "{ category:\"C\", value3:2 },"
+ "{ category:\"C\", value3:2 }"
+ " ] ,"
+ "values2: []"
+ "}";
issue is that the legend is still showing group A
Thanks,
Abdullah
This is possible only if the maximum number of groups is known. Then, you can define series for all possible groups and remove unneeded ones dynamically as per Including Chart Series Dynamically.
Thanks for the update it did work for us.
One other question if we have another value for an example like the % change how can we concatenate those two values into the Y axis of the bar chart to display something like 123 - 25% see sample picture below:
image.png (1.9 KB)
that % data is going to come from json and it would be for all objects which would contain the % change and value.
String json = "{ name: \"John doe\", "
+ "age: 18 ,"
+ "values: [ "
+ "{ category:\"A\", change: 25%, value1:5 },"
+ "{ category:\"A\", change: 25%, value1:5 },"
+ "{ category:\"A\", value1:5 },"
+ "{ category:\"B\", value2:1 },"
+ "{ category:\"B\", value2:1 },"
+ "{ category:\"B\", value2:1 },"
+ "{ category:\"B\", value2:1 },"
+ "{ category:\"C\", value3:2 },"
+ "{ category:\"C\", value3:2 },"
+ "{ category:\"C\", value3:2 },"
+ "{ category:\"C\", value3:2 }"
+ " ] ,"
+ "values2: []"
+ "}";
so <<y[value + %]>> with some formats
Thanks,
Abdullah
Please note that LINQ Reporting Engine capabilities are restricted by MS Word capabilities. Unfortunately, MS Word does not allow to set data labels in the way you describe.
By the way, y tags are intended for setting series values. By default, data labels display series values, so when the labels contain just these values, it works as expected. But there is no way to alter data labels in the described way using MS Word.
@ivan.lyagin Need help with getting sum of objects based on some condition.
Example below json:
String json = "{ name: \"John doe\", "
+ "age: 18 ,"
+ "values: [ "
+ "{ category:“A”, value:5 },"
+ "{ category:“A”, value:5 },"
+ "{ category:“A”, value:5 },"
+ "{ category:“B”, value:1 },"
+ "{ category:“B”, value:1 },"
+ "{ category:“B”, value:1 },"
+ "{ category:“B”, value:1 },"
+ "{ category:“C”, value:2 },"
+ "{ category:“C”, value:2 },"
+ "{ category:“C”, value:2 },"
+ "{ category:“C”, value:2 }"
+ " ] ,"
+ "values2: []"
+ "}";
I need to get sum of category A than get sum of B and so on…
In this case the number has the same key (value).
Something like the below:
A sum: <<[where(e=> e.category == "A").sum(e=> e.value)]: "0.00">>
B sum: <<[where(e=> e.category == "B").sum(e=> e.value)]: "0.00">>
C sum: <<[where(e=> e.category == "B").sum(e=> e.value)]: "0.00">>
This is not working for us.
Before we achieved the sum but we had different value keys, like value1, value2, value3 but we have the case of value for all.
Thanks for your help.
<<[values.where(e=> e.category == "A").sum(e=> e.value)]: "0.00">> should do the job for the present JSON.
@ivan.lyagin Thanks for your help.
Is it possible to filter an array with some condition and use that as the y axis for a bar chart?
Example array below:
String json = "{ name: \"John doe\", "
+ "age: 18 ,"
+ "values: [ "
+ "{ category:\"A\", value:5 },"
+ "{ category:\"A\", value:5 },"
+ "{ category:\"A\", value:5 },"
+ "{ category:\"B\", value:1 },"
+ "{ category:\"B\", value:1 },"
+ "{ category:\"B\", value:1 },"
+ "{ category:\"B\", value:1 },"
+ "{ category:\"C\", value:2 },"
+ "{ category:\"C\", value:2 },"
+ "{ category:\"C\", value:2 },"
+ "{ category:\"C\", value:2 }"
+ " ] ,"
+ "values2: []"
+ "}";
Can I do something like the below:
<<y[values.where(e=> e.category == "A").value]>>
What does the (where) method usually return?
Thanks
The where method returns a collection of filtered items. So you may use something like that: <<y[values.where(e=> e.category == "A").sum(e=> e.value)]>>.
For more information, see Enumeration Extension Methods. The article contains a link to .NET LINQ extension methods at the top of it, which can be used to learn more about the methods.
How can we just use the value of that filtered collection. See with the sum function we can get that sum but I need to get value data and populate the bar chart. Something like the below:
<<y[values.where(e=> e.category == "A").value]>>
or
<<y[values.where(e=> e.category == "A").select(e=> e.value)]>>
See I tried with all the functions in the link below but it does not populate that section of the bar chart.
https://docs.aspose.com/words/java/appendix-a-enumeration-extension-methods/
How can we get the property (value) data from a filtered collection and plug into y series for bar chart?
Thanks,
Abdullah
Unfortunately, it is not clear what you are trying to achieve. A collection contains several values, so it is not clear what do you mean by “get the property (value) data from a filtered collection”. Do you want to get the first one? If so, then <<y[values.where(e=> e.category == "A").first().value]>> should do the job.
@ivan.lyagin How can we get distinct group names from an array of objects.
Example Json:
String json = "{ name: \"John doe\", "
+ "age: 18 ,"
+ "values: [ "
+ "{ category:\"A\", value:5 },"
+ "{ category:\"A\", value:5 },"
+ "{ category:\"A\", value:5 },"
+ "{ category:\"B\", value:1 },"
+ "{ category:\"B\", value:1 },"
+ "{ category:\"B\", value:1 },"
+ "{ category:\"B\", value:1 },"
+ "{ category:\"C\", value:2 },"
+ "{ category:\"C\", value:2 },"
+ "{ category:\"C\", value:2 },"
+ "{ category:\"C\", value:2 }"
+ " ] ,"
+ "values2: []"
+ "}";
I want to be able to see A, B ,C.
I tried GroupBy, something like this: <<[ds.values.groupBy(p=> p.category)]>><<[Key.category]>>
But it is not working.
How can we achieve this?
Here are some examples to achieve this:
<<foreach [in ds.values.groupBy(p=> p.category)]>><<[key]>> <</foreach>>
<<foreach [c in ds.values.select(p=> p.category).distinct()]>><<[c]>> <</foreach>>