I want to generate a Column Chart from Expression rule which contains query aggregation on two columns , How can I generate a Column Chart for this.
Below is the code of my Expression Rule
index( a!queryEntity( entity: cons!MBUA_DSE_TESTSITEDATA, query: a!query( aggregation: a!queryAggregation( { a!queryAggregationColumn(field: "site", isGrouping: true()), a!queryAggregationColumn(field: "worktype", isGrouping: true()), } ), pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 50) ), fetchTotalCount: false ), "data", null() )
Below is the code on Interface having Column Chart:
a!localVariables( local!myData: rule!MBUA_readChartView(), { a!columnChartField( label: "Column Chart", categories: { "Category 1", "Category 2" }, series: a!forEach( items: local!myData, expression: a!chartSeries(label: "Chart", data: fv!item.worktype) ), stacking: "NONE", showLegend: true, showTooltips: true, labelPosition: "ABOVE", colorScheme: "RAINFOREST", height: "MEDIUM", xAxisStyle: "STANDARD", yAxisStyle: "STANDARD" ) } )
I am getting No Data available on the chart when I am using "worktype" column , however for "site" column I am getting data
Discussion posts and replies are publicly visible
Hi Stefan,That would also not work .
I want distinct count of sites for each Work Type .Here for ABC , it should give 3 .
for DEF, it should give 2.
I have u got my point now, I think only aggregation will not work here .Please let me know how we can achieve this and create a graph for the same .
OK. Can you set up a synced record on this table? If not, you will need to create a view in the DB. A custom record field concats the workType and the Site to create a combined value. A view in DB needs to do the same. Then you can aggregate on this new field.
Hey Stefan,I achieved this my requirement using another approach but your approach also seems very nice .If I am not wrong , You want me to create a view which will contain a column having concatenated values of 'work type' and 'site' and then I will use that view to query our data.
Approach which I have followed :
I have looped on the distinct 'work type' to count the no of sites on distinct( 'work type' + 'site' ) and then getting the output in a Map and used that Map for items of chart,
Try to use synced records. If that's not an option, use a view. The reason is, that loading data into memory for further aggregation is always a risk to performance, stability and hidden bugs.
Hey Stefan,
I am done with this task with the help of View as well as Looping which I mentioned earlier.Thanks for your prompt replies.
RegardsAbdus Samad
Secondly, we need to group based on the site first, then based on the type of work. After that, you should add a second aggregation which counts the id field as well.