Stacked Column Chart (Using Flex Chart)
You can use this code on top of any Datamart, just adjust the last line of the code with the name of your:
- Datamart Name
- Dimension column name, used for X axis values.
- Dimension column name, used for the series, which forms the stack.
- Measure column name and operation, if you want to summarize the measure, or calculate the average, etc.
The result then looks like this:
Full code example which creates the Flex Chart
def stackedColumnChartDataFromDatamart(datamartTableName, dimensionColumnNameForX, dimensionColumnNameForStack, measureExpression) { def dmCtx = api.getDatamartContext() def salesDM = dmCtx.getDatamart(datamartTableName) def datamartQuery = dmCtx.newQuery(salesDM, true) datamartQuery.select(dimensionColumnNameForX, "dimX") datamartQuery.select(dimensionColumnNameForStack, "dimStacked") datamartQuery.select(measureExpression, "measure") datamartQuery.orderBy(dimensionColumnNameForX, dimensionColumnNameForStack) def result = dmCtx.executeQuery(datamartQuery) def rows = [] for (def it = result.getData().iterator(); it.hasNext();) { def row = it.next() rows << [dimX: (row.dimX), dimStacked: (row.dimStacked), measure: (row.measure)] } return rows } def createStackedColumnChart(rows) { def serieNames = rows.collect { row -> row.dimStacked }.unique() def xValues = rows.collect { row -> row.dimX }.unique() //populate data array def values = [:] for (serieName in serieNames) for (xValue in xValues) values[serieName, xValue] = 0 for (row in rows) values[row.dimStacked, row.dimX] = row.measure //create chart data series def series = [] for (serieName in serieNames) { def data = xValues.collect { xValue -> [name: (xValue), y: (values[serieName, xValue])] } def serie = [name: (serieName), data: (data)] series << serie } //chart definition def chart = [ chart : [ type: 'column' ], xAxis : [ categories: (xValues) ], plotOptions: [ column: [ stacking: 'normal' ] ], series : (series) ] return api.buildFlexChart(chart) } if (api.isInputGenerationExecution()) return return createStackedColumnChart( stackedColumnChartDataFromDatamart("Sales_Data", "Invoice_DateYear", "ProductGroup", "SUM(Quantity)") )
Found an issue in documentation? Write to us.