KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

Pivot 2 table #1262

Open Edward E. Burrowes opened this topic on on Jan 17, 2020 - 3 comments

Edward E. Burrowes commented on Jan 17, 2020

How to create pivot like this 2 table in mysql

thanks

KoolReport commented on Jan 18, 2020

One trick is that you use CalculatedColumn to make an (A-B) column first and then you pipe data through Pivot process with "sum" of (A-B) column. It will give you the desired result.

Edward E. Burrowes commented on Jan 18, 2020

sorry i,m newbie, can you give me example source code thanks for respond

KoolReport commented on Jan 19, 2020

The CalculatedColumn help you to create new column resulted from calculate from other. Here is its documentation. So basically you create new column to hold the result of (A-B).

        ->pipe(new CalculatedColumn(array(
            "a_minus_b"=>"{a}-{b}",
        )))

And then you pipe to the Pivot process:

        ->pipe(new Pivot(array(
            "dimensions" => array(
                "row" => "group_column, sub_group_column",
            ),
            "aggregates"=>array(
                "sum" => "a_minus_b",
            )
        )))

Here is the documentation of Pivot process.

Hope that helps.

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
help needed

Pivot