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?
Well ... worktype does not seem to be a number.
Please explain what you expect so we can make suggestions of how to change the 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.