New User: Adding a Filter to a Line Chart

David Richmond commented on Jul 22, 2021

I have a question, I have a line report that is working ok, but its too difficult to read as is i would like an html dropdown filter provided for the "Month" or perhaps a different column NOT used in the report but contained in the dataset like "Station Name"

I also added a data table to the bottom of the chart (which is great) and would like to have one dropdown filter both the chart and the datatable. -- The search ability of the datatable is fantastic and I'm using that now already.

Whats the best way to do this?

index.php:

<?php

require_once "../koolreport/core/autoload.php"; require_once "MyReport.php";

$report = new MyReport; $report->run()->render();

MyReport.php

<?php

$mssqldriver = '{ODBC Driver 13 for SQL Server}';

class MyReport extends \koolreport\KoolReport {

function settings()
{
    return array(
        "dataSources"=>array(
            "myDB"=>array(
                "connectionString"=>"odbc:Driver=$mssqldriver;server=10.10.10.X; Database=XXX",
                "username"=>"USER",
                "password"=>"PASSWORD",
                "charset"=>"utf8"
            ),
        )
    ); 
}

protected function setup()
{
    $this->src("myDB")->query("
		  SELECT Month, StationName, isnull(Water,0) as WaterBill, isnull(Power,0) as PowerBill, isnull(Gas,0) as GasBill FROM qryIntranet_WebApplication_Reports_StationExpenses_XTAB
      ")
    ->pipe($this->dataStore("result"));
}

} MyReport.view.php

<?php use \koolreport\widgets\google\LineChart; ?> <html>

<head>
    <title>Station Expenses</title>
</head>
<body>
	<?php
		LineChart::create([
		"dataSource"=>$this->dataStore("result"),
			"columns"=>[
			"Month"=>["type"=>"string"],
			"WaterBill"=>["type"=>"number"],
			"PowerBill"=>["type"=>"number"],
			"GasBill"=>["type"=>"number"],
		]
	]);
	?>
</body>

</html>

David Richmond commented on Jul 25, 2021

I found the plugins for the widget but the dropdowns shows too many records, how can I make the dropdown show only "distinct" values?

for instance it shows 59 values for Mar, Apr,Jun, July

that is because of the way the crosstab query is written -- which is right -- but i only want Mar,Apr,Jun,July in there

Sebastian Morales commented on Jul 26, 2021

Pls use the sql query select distinct month in your report setup:

    $this->src("myDB")->query("
		  SELECT DISTINCT MonthFROM qryIntranet_WebApplication_Reports_StationExpenses_XTAB
      ")
    ->pipe($this->dataStore("distinctMonths"));

Then in your report view, use $this->dataStore("distinctMonths") for your dropdown input (for example, Inputs' BSelect or Select2):

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

Bind the month dropdown input to the report's params like in the above example. Finally use this month param in your main data sql query to limit data returned.

Let us know if you need further detail. Tks,

KoolReport's Forum

New User: Adding a Filter to a Line Chart #2226

Build Your Excellent Data Report