KoolReport's Forum

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

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

Open David Richmond opened this topic on 3 days ago - 2 comments

David Richmond commented 3 days ago

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 8 hours ago

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 14 mins ago

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,

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
None yet

None