KoolReport's Forum

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

Select2 with google charts #2727

Open Letalba opened this topic on on Jun 15 - 11 comments

Letalba commented on Jun 15

Hi,

You have an example on how to filter a table with Select2. Can I do the same for Google Charts like "Column Charts", "Donut Charts", "Area Charts", etc....? How?

Sebastian Morales commented on Jun 16

Pls check this example, just replace MultiSelect with Select2:

https://www.koolreport.com/examples/reports/inputs/order_list/

Letalba commented on Jun 21

I've checked that example but I think that's not what I'm looking for. I have a table with data from a sql database, and I can filter that table. What I want to do now is to create some Donut Charts ans Column Charts with the data I'm presenting in the table. So, if I filter the table I want to filter those Charts according to the filter I've used in the table

Sebastian Morales commented on Jun 21

If you can filter your table with Select2 you can filter your charts with Select2, too. Just apply the same filter by Select2 to your charts' data pipes and datastores. If it's not working for you or there's an error, pls post your php code and screenshots. Tks,

Letalba commented on Jun 21

ok, so I have this table that works perfectly fine with the filters:

and I just added this Column Chart:

I don't know why it's showing up like that, with so many empty spaces that shouldn't be there

now let's imagine that in filters I select "serv_internamento", the table will only show me the lines that have that event, but the chart will show like this:

PS: In the table theres some parameters showing "0", that's not supposed to be there as well. They weren't there before I added the Column Chart to my code.

This is my Report.php:

<?php


require_once "/opt/reports/autoload.php";
require_once "/opt/reports/koolreport/amazing/tests/cases/load.koolreport.php";

use \koolreport\dashboard\widgets\Text;
use \koolreport\dashboard\widgets\google\ColumnChart;
use \koolreport\dashboard\widgets\Table;

class Report extends koolreport\KoolReport
{

	use \koolreport\clients\Bootstrap;
	use \koolreport\inputs\Bindable;
    use \koolreport\inputs\POSTBinding;

	protected function defaultParamValues()
    {
        return array(
            "bot_type"=>array(),
            "bot_tame"=>array(),
            "event"=>array(),
        );
    }

	protected function bindParamsToInputs()
    {
        return array(
            "bot_type",
            "bot_name",
            "event"
        );
    }
	
	protected function settings()
    {
		return array(
			"dataSources"=>array(
                "bots"=>array(
                    "connectionString"=>"pgsql:host=1**.0.0.*;port=5**3;dbname=bots;",
                    "dataFormat"=>"table",
                    'username' =>"pos***es",
                    'password' =>"***********",
									),
								)
	);
	}

	protected function setup()
    {

		// add the variable to query_params to be used as parameter for sql query
		$query_params = array();
        if($this->params["bot_type"]!=array())
        {
            $query_params[":bot_type"] = $this->params["bot_type"];
        }
        if($this->params["bot_id"]!=array())
        {
            $query_params[":bot_id"] = $this->params["bot_id"];
        }
        if($this->params["event"]!=array())
        {
            $query_params[":event"] = $this->params["event"];
        }


		$this->src("bots")->query("select
                bot_type,
                bot_name,
                event,
				bot_id
				from botfacts
				where 1=1
            ".(($this->params["bot_type"]!=array())?"and bot_type in (:bot_type)":"")."
            ".(($this->params["bot_id"]!=array())?"and bot_id in (:bot_id)":"")."
            ".(($this->params["event"]!=array())?"and event in (:event)":"")."
        ")->params($query_params)
        ->pipe($this->dataStore("botfacts"));

        $this->src("bots")->query("select
        bot_type,
                sum(case when bot_type='VOICE' and event='START' then 1 else 0 END) as voice_calls,
                sum(case when bot_type='CHAT' and event='START' then 1 else 0 END) as chat_calls
                from botfacts where bot_type notnull group by bot_type;")
        ->params($query_params)
        ->pipe($this->dataStore("botfacts"));


	}
}

This is my Report.view.php:

<?php

use \koolreport\widgets\koolphp\Table;
use \koolreport\widgets\google\ColumnChart;
use \koolreport\inputs\Select2;

?>
<html>
<body style="width:100%;">
<div class="report-content" style="width: auto;">

  <div class="report-content">
    <div class="text-center">
        <h1>Bot Statistics</h1>
        <div class="text-left">
        <p class="lead">
            Select the filters in the boxes below
        </p>
        </div>
    </div>
  
        <form method="post">
          <div class="row">
            <div class="col-md-6">
              <div class="form-group" style="width:30%;">
                  <b>Select Call Type</b>
                    <?php 
                        Select2::create(array(
                            "multiple"=>true,
                            "name"=>"bot_type",
                            "dataSource"=>$this->src("bots")->query("
                                select bot_type
                                from botfacts
                                where bot_type <> ''
                                group by bot_type
                            "),
                            "attributes"=>array(
                                "class"=>"form-control"
                            )
                        ));
                    ?>
              </div>    
      
              <div class="form-group" style="width:30%;">
                <b>Select Bot</b>
                  <?php 
                    Select2::create(array(
                        "multiple"=>true,
                        "name"=>"bot_id",
                        "dataSource"=>$this->src("bots")->query("
                            select bot_id
                            from botfacts
                            where bot_id <> ''
                            group by bot_id
                        "),
                        "attributes"=>array(
                            "class"=>"form-control"
                        )
                    ));
                  ?>                
              </div>
              
              <div class="form-group" style="width:30%;">
                <b>Select Event</b>
                  <?php 
                    Select2::create(array(
                        "multiple"=>true,
                        "name"=>"event",
                        "dataSource"=>$this->src("bots")->query("
                            select event
                            from botfacts
                            where event <> ''
                            group by event
                        "),
                        "attributes"=>array(
                            "class"=>"form-control"
                        )
                    ));
                  ?>                
              </div> 
               
              <div class="form-group">
                  <button class="btn btn-primary">Submit</button>
              </div>    
            </div>
          </div>
            
        </form>

        <div style="width:25%;">
          <?php
            Table::create(array(
                "dataSource"=>$this->dataStore("botfacts"),
                "columns"=>array(
                    "bot_type",
                    "bot_id",
                    "event"
                ),
                "paging"=>array(
                    "pageSize"=>9
                ),
                "cssClass"=>array(
                    "table"=>"table-bordered"
                )
            ));
          ?>
        </div>

        <div class="row" style="display: flex; flex-direction: row;">
        <div class="column-total" style="flex-grow:1;">
          <?php
            ColumnChart::create(array(
            "title"=>"Total calls by bot",
            "dataSource"=>$this->dataStore('botfacts'),
            "width"=>"100%",
            "columns"=>array(
                "bot_type",
                "voice_calls"=>array("label"=>"Voice Bot"),
                "chat_calls"=>array("label"=>"Chat Bot")
            ),
            "options"=>array(
            "isStacked"=>true
            )
            ));
           ?>
        </div> 
        </div>
        
  </div>

    
</div>
</body>
</html>

Thanks for your availability!!

Sebastian Morales commented on Jun 22

I notice that there're 2 lines of this pipe in your report setup:

->pipe($this->dataStore("botfacts"));
...
->pipe($this->dataStore("botfacts"));

Your datastore contains both filtered data and non-filtered data. Make sure that the datastore used for your chart or table only contains filtered data from filtered query.

Letalba commented on Jun 22

And how do I know if that data is filtered or not?

I've already changed the second pipe and called it "columnChart". I need the filters working to the table to work as well for this column chart.

Sebastian Morales commented on Jun 22

You must use a filtered query and pipe to a datastore for chart as well.

Letalba commented on Jun 22

This is the query I have

$this->src("bots")->query("select
        bot_type,
                sum(case when bot_type='VOICE' and event='START' then 1 else 0 END) as voice_calls,
                sum(case when bot_type='CHAT' and event='START' then 1 else 0 END) as chat_calls
                from botfacts where bot_type notnull group by bot_type;")
        ->params($query_params)
        ->pipe($this->dataStore("colChart"));

My column Chart shows like this:

Now let's say that in my filters above the table, in "events" I choose "serv_internamento". The column chart should show me 2 "CHAT" accordingly to what I have in my database but it keeps showing 2 "VOICE" and 8 "CHAT"

Sebastian Morales commented on Jun 22

It's because your first column which is bot_type has 2 values bot_type='VOICE' and bot_type='CHAT' and you group by bot_type. The result would have 2 rows of 'VOICE' and 'CHAT'. You can not GROUP BY a column and expect multiple rows of same values, such as 2 'CHAT' rows. That's not what GROUP BY means.

Letalba commented on Jun 22

I'm sorry, that's not what I meant. If I don't have any filter the chart should show just like it is in the image. But, when in the filters I choose "serv_internamento" I want the chart to show me only one row, and it should be the CHAT row reaching the value 2 instead of the value 8 like it was before. Why? Because in my database I don't have any VOICE with "serv_internamento", and I only have 2 "CHAT" 's with "serv_internamento".

Sebastian Morales commented on Jun 23

I think you should add WHERE event in (:event) to the select query for your chart for the event filter to work for the chart. Similarly for other filters.

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

None