KoolReport's Forum

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

Column names in Cube Report #2057

Open Augustine Arthur opened this topic on on May 6, 2021 - 14 comments
Augustine Arthur commented on May 6, 2021

Please how do I arrange the Q3, Q4, Q2, Q1 in the proper order.

Region Total Q3 Q4 Q2 Q1 Head Office ¢1,700 ¢1,700 ¢0 ¢0 ¢0 North East Region ¢5,643 ¢0 ¢0 ¢0 ¢5,643 Northern ¢20,000 ¢0 ¢20,000 ¢0 ¢0 Volta Region ¢40,000 ¢0 ¢40,000 ¢0 ¢0 Oti Region ¢84,000 ¢0 ¢24,000 ¢20,000 ¢40,000

Thank you

Sebastian Morales commented on May 6, 2021

Pls set the "columns" property of your Table, DataTables or Chart widget in your report view like this:

    Table::create(array(
	    ...
	    "columns" => array(
	        "Q1" => array(label => "Quarter 1"),
	        "Q2" => array(label => "Quarter 2"),
	        "Q3" => array(label => "Quarter 3"),
	        ...
	    )
Augustine Arthur commented on Jun 1, 2021

Hi Sebastian, I am still unable to organise my quarters in proper ascending order like Q1, Q2, Q3, Q4

Sebastian Morales commented on Jun 1, 2021

Augustine, pls show us your report code and screenshot of the problem? Tks,

Augustine Arthur commented on Jun 1, 2021

Please these are the files.

Index file (RevQtr.php)

<?php
    require_once "RevQuarters.php";
    $salesYear = isset($_POST['salesYear']) ? $_POST['salesYear'] : array(2017, 2018, 2019, 2020, 2021, 2022);
	$report = new RevQuarters(array(
        'salesYear' => $salesYear
    ));
    echo $report->run()->render(); ?>


Setup file (RevQuarters.php)

<?php
require_once "../load.koolreport.php";
use \koolreport\processes\ColumnMeta;
use \koolreport\processes\Limit;
use \koolreport\processes\Sort;
use \koolreport\processes\TimeBucket;
use \koolreport\processes\RemoveColumn;
use \koolreport\processes\OnlyColumn;
use \koolreport\processes\Filter;
use \koolreport\processes\ValueMap;
use \koolreport\cube\processes\Cube;
use \koolreport\core\Utility;

class RevQuarters extends koolreport\KoolReport
{
	use \koolreport\export\Exportable;
	use \koolreport\clients\Bootstrap;
	use \koolreport\inputs\Bindable;

   use \koolreport\inputs\POSTBinding;
   
    function settings()
    {
        return array(
            "dataSources" => array(
                "dollarsales"=>array(
                    "connectionString"=>"mysql:host=localhost;dbname=mlnrgh",
                    "username"=>"root",
                    "password"=>"",
                    "charset"=>"utf8"    
                ), 
            )
        );
    }
	
    Protected function setup()
	
    {
		//$salesYear = $this->params['salesYear'];
        $salesYear = isset($_POST['salesYear']) ? 
        $_POST['salesYear'] : array(2017, 2018, 2019, 2020, 2021, 2022);
		$node=$this->src('dollarsales')
        ->query("SELECT QUARTER(DateDisbursed) as 'Q', YEAR(DateDisbursed) as 'Y', Region, Beneficiaries, Amount FROM revenuereturns")
        ->pipe(new ColumnMeta(array(
            "Amount"=>array(
                'type' => 'number',
                "prefix" => "¢",
            ),
        )))
        ->pipe(new ValueMap(array(
            'Q' => array(
                '{func}' => function ($value) {
                    return 'Q' . $value;
                },
                "{meta}" => array(
                    "type" => "string"
                ),
            )
        )));
        
        $filters = array('or');
       foreach ($salesYear as $year)
            array_push($filters, array('Y', '=', ''.$year));
        $node = $node->pipe(new Filter($filters));
        
        $node->pipe($this->dataStore('salesFilter'));
        
        $node->pipe(new Cube(array(
            "row" => "Region",
            "column" => "Q",
            "sum" => "Amount"
        )))
        ->pipe(new Sort(array(
            '{{all}}' => 'asc'
        )))
        ->pipe(new Limit(array(
            20, 0
        )))->pipe(new ColumnMeta(array(
            "{{all}}"=>array(
                "label"=>"Total",
            ),
            "Region"=>array(
                "label"=>"Region",
            ),
        )))->saveTo($node2);
        
        $node2->pipe($this->dataStore('salesQuarterProductName'));
        
        $node2->pipe(new RemoveColumn(array(
            "{{all}}"
        )))
        ->pipe($this->dataStore('salesQuarterProductNameNoAll'));
        
        $node2->pipe(new OnlyColumn(array(
            'Region', "{{all}}"
        )))->pipe($this->dataStore('salesQuarterProductNameAll'));
        
    }
}


View Report file (RevQuarter.view.php)

<?php
    use \koolreport\widgets\google;
    use \koolreport\widgets\koolphp\Table;
	use \koolreport\widgets\google\ColumnChart;
	use \koolreport\inputs\Select;
    use \koolreport\clients\bootstrap;
?>

<div class="report-content">
  
    <div class="text-center">
        <h1>Revenue Summary by Regions</h1>
        <p class="lead">
           
        </p>
    </div>
    <?php
        $salesYear = isset($_POST['salesYear']) ? 
            $_POST['salesYear'] : array(2017, 2018, 2019, 2020, 2021, 2022);
    ?>
    <form method="post" class="text-center">
        <div class="form-group">
            <span style="margin-left:10px;">
                <input id="y2017" type="checkbox" name="salesYear[]" value="2017"
                <?php echo in_array(2017, $salesYear) ? 'checked' : '' ?> />
                <label for="y2017">2017</label>
            </span>
            <span style="margin-left:10px;">
                <input id="y2018" type="checkbox" name="salesYear[]" value="2018" 
                <?php echo in_array(2018, $salesYear) ? 'checked' : '' ?> />
                <label for="y2018">2018</label>
            </span>
            <span style="margin-left:10px;">
                <input id="y2019" type="checkbox" name="salesYear[]" value="2019" 
                <?php echo in_array(2019, $salesYear) ? 'checked' : '' ?> />
                <label for="y2019">2019</label>
            </span>
			<span style="margin-left:10px;">
                <input id="y2020" type="checkbox" name="salesYear[]" value="2020" 
                <?php echo in_array(2020, $salesYear) ? 'checked' : '' ?> />
                <label for="y2020">2020</label>
            </span>
			<span style="margin-left:10px;">
                <input id="y2021" type="checkbox" name="salesYear[]" value="2021" 
                <?php echo in_array(2021, $salesYear) ? 'checked' : '' ?> />
                <label for="y2021">2021</label>
            </span>
			<span style="margin-left:10px;">
                <input id="y2022" type="checkbox" name="salesYear[]" value="2022" 
                <?php echo in_array(2022, $salesYear) ? 'checked' : '' ?> />
                <label for="y2022">2022</label>
            </span>
        </div>
        <div class="form-group">
            <button type="submit" class="btn btn-primary">Submit</button>
			<button formaction="exportgood.php" class="btn btn-primary">Download PDF</button>
        </div>
    </form>
    <?php 
	
      Table::create(array(
        "dataStore" => $this->dataStore('salesQuarterProductName'),
      ));
    ?>
    <div class='row'>
        <div class="col-md-6">
            <?php 
            google\BarChart::create(array(
                "dataStore"=>$this->dataStore('salesQuarterProductNameNoAll'),
                "options"=>array(
                'title' => 'Barchart Showing Revenue by Regions',
                'isStacked' => true
                ),
                "width"=>'100%',
                // 'height'=>'400px',
            ));
            ?>
        </div>
        <div class="col-md-6">
            <?php 
            google\PieChart::create(array(
                "dataStore"=>$this->dataStore('salesQuarterProductNameAll'),
                "options"=>array(
                'title' => 'Piechart Showing Revenue by Regions',
                ),
                "width"=>'100%',
                // 'height'=>'300px',
            ));
            ?>
			
        </div>
    </div>

</div>

Export Pdf file (RevQuartersPdf.view.php)

<?php
    use \koolreport\widgets\koolphp\Table;
    use \koolreport\widgets\google\ColumnChart;
	use \koolreport\widgets\google\PieChart;
?>
<html>
    <body style="margin:0.5in 1in 0.5in 1in">
        <link rel="stylesheet" href="../../assets/bootstrap3/bootstrap.min.css" />
        <link rel="stylesheet" href="../../assets/bootstrap3/bootstrap-theme.min.css" />   
       <div class="page-header" style="text-align:right"><i>Revenue Summary</i></div>
        <div class="page-footer" style="text-align:right">{pageNum}</div>
		
        
        <hr/>

<div class="report-content">
   
		<?php
	$salesYear = isset($_POST['salesYear']) ? 
            $_POST['salesYear'] : array(2017, 2018, 2019, 2020, 2021, 2022);
        $salesYearStr = implode(", ", $salesYear);
    ?>
    <div class="text-center">
        <h1>Revenue Summary By Regions  <?php echo $salesYearStr; ?></h1> 
		
	 </h1>
        <p class="lead">
        
        </p>
    </div>
    
    <?php 
      Table::create(array(
        "dataStore" => $this->dataStore('salesQuarterProductName'),
		//WHERE salesYear  !checked;
      ));
    ?>
	
    <div class='row'>
        <div class="col-md-6">
            <?php 
			
            ColumnChart::create(array(
                "dataStore"=>$this->dataStore('salesQuarterProductNameNoAll'),
                "options"=>array(
                'title' => 'Barchart Showing Revenue by Regions',
                'isStacked' => true
                ),
                "width"=>'100%',
                // 'height'=>'400px',
            ));
            ?>
        </div>
        <div class="col-md-6">
            <?php 
            PieChart::create(array(
                "dataStore"=>$this->dataStore('salesQuarterProductNameAll'),
                "options"=>array(
                'title' => 'Piechart Showing Revenue by Regions',
                ),
                "width"=>'100%',
                // 'height'=>'300px',
            ));
			
            ?>
			
        </div>
    </div>

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




Export File (exportgood.php)

<?php
require_once "RevQuarters.php";
$report = new RevQuarters;

$report->run()
->export('RevQuartersPdf')
->pdf(array(
    "format"=>"A4",
    "orientation"=>"portrait",
    "zoom"=>2
))

->toBrowser("Rev_Quarters.pdf", true);
Augustine Arthur commented on Jun 1, 2021

Please this the screen shot.

Sebastian Morales commented on Jun 2, 2021

Pls use this syntax for both your Table and Chart:

    Table::create(array(
	    ...
	    "columns" => array(
	        "Q1" => array(label => "Quarter 1"),
	        "Q2" => array(label => "Quarter 2"),
	        "Q3" => array(label => "Quarter 3"),
	        ...
	    )
Augustine Arthur commented on Jun 18, 2021

Pls when I insert the code in create:Table array the result is as shown below:

My label column does not show. Any help please!!

Please code below is where I inserted the Table array in view.php

<?php
    use \koolreport\widgets\google;
    use \koolreport\widgets\koolphp\Table;
	use \koolreport\widgets\google\ColumnChart;
	use \koolreport\inputs\Select;
    use \koolreport\clients\bootstrap;
?>

<div class="report-content">
  
    <div class="text-center">
        <h1>Revenue Summary by Regions</h1>
        <p class="lead">
           
        </p>
    </div>
    <?php
        $salesYear = isset($_POST['salesYear']) ? 
            $_POST['salesYear'] : array(2017, 2018, 2019, 2020, 2021, 2022);
    ?>
    <form method="post" class="text-center">
        <div class="form-group">
            <span style="margin-left:10px;">
                <input id="y2017" type="checkbox" name="salesYear[]" value="2017"
                <?php echo in_array(2017, $salesYear) ? 'checked' : '' ?> />
                <label for="y2017">2017</label>
            </span>
            <span style="margin-left:10px;">
                <input id="y2018" type="checkbox" name="salesYear[]" value="2018" 
                <?php echo in_array(2018, $salesYear) ? 'checked' : '' ?> />
                <label for="y2018">2018</label>
            </span>
            <span style="margin-left:10px;">
                <input id="y2019" type="checkbox" name="salesYear[]" value="2019" 
                <?php echo in_array(2019, $salesYear) ? 'checked' : '' ?> />
                <label for="y2019">2019</label>
            </span>
			<span style="margin-left:10px;">
                <input id="y2020" type="checkbox" name="salesYear[]" value="2020" 
                <?php echo in_array(2020, $salesYear) ? 'checked' : '' ?> />
                <label for="y2020">2020</label>
            </span>
			<span style="margin-left:10px;">
                <input id="y2021" type="checkbox" name="salesYear[]" value="2021" 
                <?php echo in_array(2021, $salesYear) ? 'checked' : '' ?> />
                <label for="y2021">2021</label>
            </span>
			<span style="margin-left:10px;">
                <input id="y2022" type="checkbox" name="salesYear[]" value="2022" 
                <?php echo in_array(2022, $salesYear) ? 'checked' : '' ?> />
                <label for="y2022">2022</label>
            </span>
        </div>
        <div class="form-group">
            <button type="submit" class="btn btn-primary">Submit</button>
			<button formaction="exportgood.php" class="btn btn-primary">Download PDF</button>
        </div>
    </form>
    <?php 
	
   Table::create(array(
        "dataStore" => $this->dataStore('salesQuarterProductName'),
		"columns" =>array(
	        "Q1" => array("label" => "Quarter 1"),
	        "Q2" => array("label" => "Quarter 2"),
	        "Q3" => array("label" => "Quarter 3"),
		"Q4" => array("label" => "Quarter 4"),
      )));
    ?>
    <div class='row'>
        <div class="col-md-6">
            <?php 
            google\BarChart::create(array(
                "dataStore"=>$this->dataStore('salesQuarterProductNameNoAll'),
                "options"=>array(
                'title' => 'Barchart Showing Revenue by Regions',
                'isStacked' => true
                ),
                "width"=>'100%',
                // 'height'=>'400px',
            ));
            ?>
        </div>
        <div class="col-md-6">
            <?php 
            google\PieChart::create(array(
                "dataStore"=>$this->dataStore('salesQuarterProductNameAll'),
                "options"=>array(
                'title' => 'Piechart Showing Revenue by Regions',
                ),
                "width"=>'100%',
                // 'height'=>'300px',
            ));
            ?>
			
        </div>
    </div>

</div>
```
Sebastian Morales commented on Jun 18, 2021

Since your Cube's "row" => "Region" your label column should be "Region". Just add it to your Table, Chart widgets like this:

      Table::create(array(
            ...
            "columns" =>array(
                "Region" => array(), 
	        "Q1" => array("label" => "Quarter 1"),
	        "Q2" => array("label" => "Quarter 2"),
	        "Q3" => array("label" => "Quarter 3"),
		"Q4" => array("label" => "Quarter 4"),
      )));
Augustine Arthur commented on Jun 18, 2021

Fantastic !. It has worked perfectly. Thank you so much.

Augustine Arthur commented on Jun 18, 2021

Sebastian there is one thing that I have to implement How can I do multiple filter based on "Regions", "Year" in my report and also export same to pdf.

Sebastian Morales commented on Jun 21, 2021

Pls check our list of input controls at this link:

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

I think you could use BSelect or Select2 for your Region and Year filters. Then use those filter values in either your sql query or with our Filter process to filter the data you want. Remember to put the inputs inside a form tag. Rgds,

Augustine Arthur commented on Jun 21, 2021

Thank you. I will keep you informed on progress.

Thank you.

Sebastian Morales commented on Jun 23, 2021

Nice, also pls open a new topic if you have problems with Inputs for other users to better follow issues. Tks,

Augustine Arthur commented on Jun 23, 2021

Okay I will do that.

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

Designed and built with by KoolPHP Inc.

Code licensed MIT.