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
Can you share the data returned by that expression?
Let's say above is my table , I want a column chart from expression rule which gives the distinct count of "Site" associated with each "Work Type" .means here for ABC , count should be - 3 and for DEF , count should be - 2 on column chart .Many Thanks for your prompt replies.
You need to group by one column, and then count the other. Try to change the aggregation into this:
{ a!queryAggregationColumn(field: "site", isGrouping: true()), a!queryAggregationColumn( field: "worktype", isGrouping: false, aggregationFunction: "COUNT", alias: "count" ), }
Hey Stefan , How can I get Distinct Count of Sites.It is not giving me distinct counts if isGrouping : false
Then group by site first, then group by worktype. Then add another aggregation counting the id field.
I am getting this which is not my expectation .I am expecting distinct count of Site .Here it should give me 3 and 2
Hey Stefan ,Did you get a chance to look on this ?
If you only want to aggregate on "site", remove the "worktype" aggregation.
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