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.