KoolReport's Forum

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

ColumnMeta Sum #255

Open arthur opened this topic on on Apr 19, 2018 - 22 comments
arthur commented on Apr 19, 2018

Hi,

First of all thanks a lot for all your support and this great framework. As explained sooner, I got two columns, 2 types of sales person, with an amount of sells. Sometime the sell is made by just one of them, and sometime by both. So I would to regroup the 2 types in one column and make the sum of their sells. So here is the code :

$this->src('sales')
 ->pipe(new Group(array(
            "by"=>"Ccial",
            "sum"=>"Marge_FAS_Déclarée",
        )))
->pipe(new ColumnMeta(array(
    "Ccial" => array(
        "name" => "salesPerson"
    )
)))
->pipe(new Sort(array(
            "Marge_FAS_Déclarée"=>"desc"
        )))
        ->pipe(new Filter(array(
           "or",
           array("Date","=","JANVIER"),
           array("Date","=","FÉVRIER"),
           array("Date","=","MARS"),
       )))
->pipe($this->dataStore("SalesSum"));

$this->src('sales')
 ->pipe(new Group(array(
            "by"=>"ACC",
            "sum"=>"Marge_FAS_Déclarée",
        )))
->pipe(new ColumnMeta(array(
    "ACC" => array(
        "name" => "salesPerson"
    )
)))
->pipe(new Sort(array(
            "Marge_FAS_Déclarée"=>"desc"
        )))
        ->pipe(new Filter(array(
           "or",
           array("Date","=","JANVIER"),
           array("Date","=","FÉVRIER"),
           array("Date","=","MARS"),
       )))
->pipe($this->dataStore("SalesSum"));

And on the view I have 

  BarChart::create(array(
        "dataStore"=>$this->dataStore('SalesSum'),
        "width"=>"100%",
        "height"=>"500px",
        "columns"=>array(
            "salesPerson"=>array(
                "label"=>"sales persons"
            ),
      "Marge_FAS_Déclarée"=>array(
                "type"=>"number",
                "label"=>"Marge FAS Déclarée",
                "prefix"=>"€",
                "emphasis"=>true
            )
        ),
        "options"=>array(
            "title"=>"Marge FAS Déclarée",
        )
    ));

It almost work but there are some strange things. I got one of the sales person who doesn't appear, a line with no name which seems to be the total amount of all the sales and a line with no name.

What could be the mistake ?

Thanks !

David Winterburn commented on Apr 19, 2018

Hi Arthur,

When you sum group based on ACC, there are many empty values which sum to that one group. You could use the Filter process to remove those empty ACC values either before or after grouping. When filtering please ensure if a empty value is null or an empty string. Thanks!

arthur commented on Apr 19, 2018

Ok, so in my case that would look like this ? 

$this->src('sales')
 ->pipe(new Group(array(
            "by"=>"ACC",
            "sum"=>"Marge_FAS_Déclarée",
        )))
->pipe(new ColumnMeta(array(
    "ACC" => array(
        "name" => "salesPerson"
    )
)))
->pipe(new Sort(array(
            "Marge_FAS_Déclarée"=>"desc"
        )))
        ->pipe(new Filter(array(
           "or",
           array("Date","=","JANVIER"),
           array("Date","=","FÉVRIER"),
           array("Date","=","MARS"),
           "and"
           array("ACC","!=",null),
       )))
David Winterburn commented on Apr 19, 2018

Please try that to see whether it works for you. Or you could apply the filter before the group process like:

$this->src('sales')
        ->pipe(new Filter(array(
           array("ACC", "!=", null),
           array("ACC", "!=", ""),
       )))
     ->pipe(new Group(array(
            "by"=>"ACC",
            "sum"=>"Marge_FAS_Déclarée",
        )))

Thanks!

arthur commented on Apr 19, 2018

Thanks, but it doesnt work, still have the presentation issue... And this error : Notice: Undefined index: ACC

arthur commented on Apr 19, 2018

After some tests it appears that one of two results appear for the salesPerson...

David Winterburn commented on Apr 20, 2018

Hi Arthur,

Can you please elaborate more? Thanks!

arthur commented on Apr 20, 2018

Ok here is a picture of my chart

As you can see, when I highlight one of the graph, It appears to be the sells of TDEBLONDE, but his name doesnt appear on the left. Here is my full code :

$this->src('sales')


 ->pipe(new Group(array(
            "by"=>"Ccial",
            "sum"=>"Marge_FAS_Déclarée",
        )))
->pipe(new ColumnMeta(array(
    "Ccial" => array(
        "name" => "salesPerson"
    )
)))
->pipe(new Sort(array(
            "Marge_FAS_Déclarée"=>"desc"
        )))
        ->pipe(new Filter(array(
           "or",
           array("Date","=","JANVIER"),
           array("Date","=","FÉVRIER"),
           array("Date","=","MARS"),
       )))
->pipe($this->dataStore("Marge_FAS_Déclarée"));

$this->src('sales')

 ->pipe(new Group(array(
            "by"=>"ACC",
            "sum"=>"Marge_FAS_Déclarée",
        )))
->pipe(new ColumnMeta(array(
    "ACC" => array(
        "name" => "salesPerson"
    )
)))

->pipe(new Sort(array(
            "Marge_FAS_Déclarée"=>"desc"
        )))
        ->pipe(new Filter(array(
           "or",
           array("Date","=","JANVIER"),
           array("Date","=","FÉVRIER"),
           array("Date","=","MARS"),
            "and",
            array("ACC", "!=", null),
           array("ACC", "!=", ""),
       )))

->pipe($this->dataStore("Marge_FAS_Déclarée"));

And for my view file :

BarChart::create(array(
        "dataStore"=>$this->dataStore('Marge_FAS_Déclarée'),
        "width"=>"100%",
        "height"=>"500px",
        "columns"=>array(
            "salesPerson"=>array(
                "label"=>"sales"
            ),
      "Marge_FAS_Déclarée"=>array(
                "type"=>"number",
                "label"=>"Marge FAS Déclarée",
                "prefix"=>"€",
                "emphasis"=>true
            )
        ),
        "options"=>array(
            "title"=>"Marge FAS Déclarée",
        )
    ));

Thanks a lot

David Winterburn commented on Apr 20, 2018

Hi Arthur,

1 . It seems that your chart's height is too short while the default label font is too large to display all names along the vertical axis. Please try to increase the chart's height or reduce the axis font size like this:

BarChart::create(array(
	...
	"options"=>array(
	  	'title' => 'Chart title',
		'chartArea' => array(
			'height' => '90%'
		),
		'vAxis' => array(
			'textStyle' => array(
				'fontSize' => 17 // or the number you want
			)
		),
		'hAxis' => array(
			'textStyle' => array(
				'fontSize' => 17 // or the number you want
			)
		),
	),
	'height'=>'1000px',
));

2 . Regarding the empty/null empty values of ACC, please separate the filter:

->pipe(new Filter(array(
           "or",
           array("Date","=","JANVIER"),
           array("Date","=","FÉVRIER"),
           array("Date","=","MARS"),
            "and",
            array("ACC", "!=", null),
           array("ACC", "!=", ""),
       )))

to 2 filters because at the moment the filter condition looks like (D1 or D2 or D3 and A1 and A2) which is incorrect to filter out empty/null values of ACC. I suggest the following:

->pipe(new Filter(array(
           "or",
           array("Date","=","JANVIER"),
           array("Date","=","FÉVRIER"),
           array("Date","=","MARS"),
       )))
->pipe(new Filter(array(
            array("ACC", "!=", null),
           array("ACC", "!=", ""),
       )))

Please try these and let us know if you still have any problem. Thanks!

arthur commented on Apr 20, 2018

Thanks, so it works for the chart height ! However I still have an issue with the filter. I have this error : Notice: Undefined index: ACC in /home/napsisfrba/www/forecast/chiffres/koolreport/processes/Filter.php on line 132

David Winterburn commented on Apr 20, 2018

Hi Arthur,

Sorry I forgot that you filter the empty/null values after the group process. In this case because the ACC name was changed to salesPerson you should change the column name of the second filter to salesPerson instead. Thanks!

arthur commented on Apr 20, 2018

I still have issues (see picture) - VALIFAX ET FLAIB appear twice: they can be commercial and ACC, that's the reason I think but is there a way to group the both ?

  • I have the total (no name but long bar on the chart) and a name '-'

  • And just a quick question before buying the pro version : We would like to create a web interface where each salesman could connect to session (PHP session) and just see his sales. So is it possible to use a PHP_SESSION to filter an excel file ?

Thanks !

David Winterburn commented on Apr 20, 2018

Hi Arthur,

To merge the commercial and ACC, after the 2 group process you could group once more like this:

...
 ->pipe(new Group(array(
            "by"=>"Ccial",
            "sum"=>"Marge_FAS_Déclarée",
        )))
->pipe(new ColumnMeta(array(
    "Ccial" => array(
        "name" => "salesPerson"
    )
)))
...
 ->pipe(new Group(array(
            "by"=>"salesPerson",
            "sum"=>"Marge_FAS_Déclarée",
        )))
->saveTo($node1);

...
 ->pipe(new Group(array(
            "by"=>"ACC",
            "sum"=>"Marge_FAS_Déclarée",
        )))
->pipe(new ColumnMeta(array(
    "Ccial" => array(
        "name" => "salesPerson"
    )
)))
...
->pipe($node1)
->pipe($this->dataStore("Marge_FAS_Déclarée"));

Regarding the second question, it's possible to save username to $_SESSION and use that $_SESSION variable to use in filter process for any datasource. Thanks!

arthur commented on Apr 20, 2018

Ok, seems to work great. Just to be sure, this coe is ok :

$this->src('sales')

 ->pipe(new Group(array(
            "by"=>"Ccial",
            "sum"=>"Marge_FAS_Déclarée",
        )))
->pipe(new ColumnMeta(array(
    "Ccial" => array(
        "name" => "salesPerson"
    )
)))
->pipe(new Sort(array(
            "Marge_FAS_Déclarée"=>"desc"
        )))
        ->pipe(new Filter(array(
           "or",
           array("Date","=","JANVIER"),
           array("Date","=","FÉVRIER"),
           array("Date","=","MARS"),
       )))
 ->pipe(new Group(array(
            "by"=>"salesPerson",
            "sum"=>"Marge_FAS_Déclarée",
        )))
->saveTo($node1);



$this->src('sales')

 ->pipe(new Group(array(
            "by"=>"ACC",
            "sum"=>"Marge_FAS_Déclarée",
        )))
->pipe(new ColumnMeta(array(
    "ACC" => array(
        "name" => "salesPerson"
    )
)))

->pipe(new Sort(array(
            "Marge_FAS_Déclarée"=>"desc"
        )))
        ->pipe(new Filter(array(
           "or",
           array("Date","=","JANVIER"),
           array("Date","=","FÉVRIER"),
           array("Date","=","MARS"),
            )))

        ->pipe(new Filter(array(
            array("salesPerson", "!=", null),
           array("salesPerson", "!=", ""),
           array("salesPerson", "!=", "-"),
       )))

->pipe($node1)
->pipe(new Sort(array(
            "Marge_FAS_Déclarée"=>"desc"
        )))
->pipe($this->dataStore("Marge_FAS_Déclarée"));

And I still have the total amount, and a line with no name in it

David Winterburn commented on Apr 20, 2018

Hi Arthur,

Please try the filter process before the group one like this:

$this->src('sales')
        ->pipe(new Filter(array(
            array("ACC", "!=", null),
           array("ACC", "!=", ""),
           array("ACC", "!=", "-"),
       )))
...

If this still doesn't work out, please send us a sample of your excel file, we will check how to filter out the empty/null values. Thanks!

arthur commented on Apr 20, 2018

No I still got the issue. Here is the file

David Winterburn commented on Apr 20, 2018

Hi Arthur,

We've downloaded your file but it's empty. If it's possible, please send the file via email to support@koolphp.net. Thanks!

arthur commented on Apr 20, 2018

Yes it's sent !

David Winterburn commented on Apr 20, 2018

Hi Arthur,

It seems there's some intricate bug with the Filter process when a column could have both string and null values. Please replace the empty/null filter process (before group process):

        ->pipe(new Filter(array(
            array("ACC", "!=", null),
           array("ACC", "!=", ""),
           array("ACC", "!=", "-"),
       )))

with the following Map process:

->pipe(new Map(array(
      '{value}' => function($row, $metaData) {
        if ($row['ACC'] != '' && $row['ACC'] != '-' && $row['ACC'] != null)
          return array($row);
      },
    )))

We will update the Filter process to be able to handle your case soon. Thanks!

arthur commented on Apr 20, 2018

Thank you! I still got the - as salesName name

Guillaume COULAND commented on Apr 21, 2018

There must have something on the ACC column that creates this "-" row, it's weird...

PS : I change the account name since we bought the pro version :-)

David Winterburn commented on Apr 26, 2018

Hi Arthur,

If you still have the "minus" column, there might be blank characters around it. Please use the following code:

->pipe(new Map(array(
      '{value}' => function($row, $metaData) {
        if ($row['ACC'] != null && trim($row['ACC']) != '' && trim($row['ACC']) != '-')
          return array($row);
      },
    )))

Thanks!

arthur commented on Apr 26, 2018

Perfect ! thanks !

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
solved
Excel

Designed and built with by KoolPHP Inc.

Code licensed MIT.