Issues in Chart Series of Report

Question

Hi All, 
 
 I want to display a report like the screen shot. I am facing issue at the time of putting the "data" field in chart series. here I want to do count of Id for each status for each category. Here 3 types of Status is present in the database/record, Open, Closed and Assigned. 
 
 Can anyone help with the code. 
 
 My code is as below which is not working properly. 
 
 load( local!pagingInfo: a!pagingInfo( startIndex: 1, batchSize: -1, sort: a!sortInfo( field: "id", ascending: true ) ), with( local!datasubset: queryrecord( cons!LSCM_RECORD_CASE, a!query( aggregation: a!queryAggregation(aggregationColumns: { a!queryAggregationColumn(field: "id", aggregationFunction: "COUNT"), a!queryAggregationColumn(field: "status", isGrouping: true), a!queryAggregationColumn(field: "category", isGrouping: true), }), pagingInfo: local!pagingInfo ) ), { a!columnChartField( label: "Closed vs Assigned vs Open Cases per Service Type", labelposition: "ABOVE", instructions: "This is the chart for the total number of Closed Cases vs Assigned Cases vs Open Cases for all the agents but for different service types", helptooltip: "", categories: { "Category 1", "Category 2", "Category 3", "Category 4" }, series: { a!chartSeries( label: "Closed", data: {index(local!datasubset.data, "id", null)} ), a!chartSeries( label: "Assigned", data: {index(local!datasubset.data, "id", null)} ), a!chartSeries( label: "Open", data: {index(local!datasubset.data.status, "id", null)} ) }, xaxistitle: "Status: Closed vs Assigned vs Open per Service Type", yaxistitle: "Total Number Of Cases", stacking: "NONE", showlegend: true, showdatalabels: true, allowdecimalaxislabels: false ) } ) ) 
 
 Regards, 
 Sudipta

Deepa_M · Answer

Please try out below code. Hopefully this will solve ur problem. 
 load( 
 local!pagingInfo: a!pagingInfo( 
 startIndex: 1, 
 batchSize: - 1, 
 sort: a!sortInfo( 
 field: "category", 
 ascending: true 
 ) 
 ), 
 with( 
 local!datasubset: todatasubset( 
 { 
 { 
 id: 10, 
 status: "Closed", 
 category: "Category 1" 
 }, 
 { 
 id: 11, 
 status: "Closed", 
 category: "Category 2" 
 }, 
 { 
 id: 15, 
 status: "Closed", 
 category: "Category 3" 
 }, 
 { 
 id: 19, 
 status: "Closed", 
 category: "Category 4" 
 }, 
 { 
 id: 20, 
 status: "Assigned", 
 category: "Category 1" 
 }, 
 { 
 id: 21, 
 status: "Assigned", 
 category: "Category 2" 
 }, 
 { 
 id: 25, 
 status: "Assigned", 
 category: "Category 3" 
 }, 
 { 
 id: 29, 
 status: "Assigned", 
 category: "Category 4" 
 }, 
 { 
 id: 30, 
 status: "Open", 
 category: "Category 1" 
 }, 
 { 
 id: 31, 
 status: "Open", 
 category: "Category 2" 
 }, 
 { 
 id: 35, 
 status: "Open", 
 category: "Category 3" 
 }, 
 { 
 id: 39, 
 status: "Open", 
 category: "Category 4" 
 }, 
 }, 
 local!pagingInfo 
 ), 
 local!CategoryList: union(local!datasubset.data.category, local!datasubset.data.category), 
 { 
 a!columnChartField( 
 label: "Closed vs Assigned vs Open Cases per Service Type", 
 labelposition: "ABOVE", 
 instructions: "This is the chart for the total number of Closed Cases vs Assigned Cases vs Open Cases for all the agents but for different service types", 
 helptooltip: "", 
 categories: { 
 local!CategoryList 
 }, 
 series: { 
 a!chartSeries( 
 label: "Closed", 
 data: { 
 index( 
 local!datasubset.data.id, 
 tointeger(wherecontains("Closed", touniformstring(local!datasubset.data.status))), 
 null 
 ) 
 } 
 ), 
 a!chartSeries( 
 label: "Assigned", 
 data: { 
 index( 
 local!datasubset.data.id, 
 tointeger(wherecontains("Assigned", touniformstring(local!datasubset.data.status))), 
 null 
 ) 
 } 
 ), 
 a!chartSeries( 
 label: "Open", 
 data: { 
 index( 
 local!datasubset.data.id, 
 tointeger(wherecontains("Open", touniformstring(local!datasubset.data.status))), 
 null 
 ) 
 } 
 ) 
 }, 
 xaxistitle: "Status: Closed vs Assigned vs Open per Service Type", 
 yaxistitle: "Total Number Of Cases", 
 stacking: "NONE", 
 showlegend: true, 
 showdatalabels: true, 
 allowdecimalaxislabels: false 
 ) 
 } 
 ) 
 )