KoolReport's Forum

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

Only use AccumulativeColumn if the column exists #2916

Open Philippe Moser opened this topic on on Dec 22, 2022 - 7 comments

Philippe Moser commented on Dec 22, 2022

How can accumulate a column, only if the column really exists?

In my setup() I want to create following datastore:

 $this->src("warehouse_datasource")
        ->query("
                SELECT   dim.tDate.DateMonthNameDE, COUNT(fact.tJob.JobID) AS amount, dim.tDate.DateYear, dim.tDate.DateMonth
                FROM     fact.tJob INNER JOIN
                         dim.tDate ON fact.tJob.JobCreatedDateID = dim.tDate.DateID
                WHERE   (fact.tJob.JobCompanyID = :CompanySiteCompanyID
                     AND (:JobCompanySiteID     = -1 OR fact.tJob.JobCompanySiteID = :JobCompanySiteID)
                     AND (:JobOwnerAccountID    = -1 OR fact.tJob.JobOwnerAccountID = :JobOwnerAccountID)
                     AND (:JobTypeID            = 255 OR fact.tJob.JobTypeID = :JobTypeID)
                     AND (:JobPositionID        = 255 OR fact.tJob.JobPositionID = :JobPositionID)
                     AND (:JobEducationID       = 255 OR fact.tJob.JobEducationID = :JobEducationID)
                     AND ( dim.tDate.DateYear >= :YearMin AND dim.tDate.DateYear <= :YearMax)
                    )
                GROUP BY dim.tDate.DateYear, dim.tDate.DateMonthNameDE, dim.tDate.DateMonth
                ORDER BY dim.tDate.DateYear, dim.tDate.DateMonthNameDE
                            ")
        ->params(array(
            ":CompanySiteCompanyID"=>$this->params["CompanySiteCompanyID"],
            ":JobCompanySiteID" =>$this->params["JobCompanySiteID"],
            ":JobOwnerAccountID"=>$this->params["JobOwnerAccountID"],
            ":JobTypeID"        =>$this->params["JobTypeID"],
            ":JobPositionID"    =>$this->params["JobPositionID"],
            ":JobEducationID"   =>$this->params["JobEducationID"],
            ":YearMin"          =>$this->params["YearRange"][0],
            ":YearMax"          =>$this->params["YearRange"][1],
        ))

        ->pipe(new AccumulativeColumn(array(
            "running_amount"=>"amount"
        )))
        ->pipe($this->dataStore('time_jobs_created'));

In my query I have some conditions. Thats why there are sometimes no data available. Therefore, the column "amount" will not always be created for counting the row.

After the query i use AccumulativeColumn to accumulate the amount of the counted columns. But when there is no amount column the AccumulativeColumn.php throws a: 'Undefined array key "amount"' exception.

I already tried to use the ->pipeIf() method, but I don't know how I can check if a column exists or not. How could I do that?

Sebastian Morales commented on Dec 26, 2022

This error seems to be because when data is empty, there's no metadata for column "amount". The process should check if "amount" metadata existed first before processing. You can try this workaround to see if it fixes the error:

        ->pipe(new \koolreport\processes\ColumnMeta(array( //add this ColumnMeta process before AccumulativeColumn
            "amount"=>array()
        )))
        ->pipe(new AccumulativeColumn(array(
            "running_amount"=>"amount"
        )))

Let us know the result. Tks,

Philippe Moser commented on Jan 11

I tried it. But it doesn't work. I still have the same exception.

Sebastian Morales commented on Jan 12

How about setting a dummy "amount" value if it doesn't exist like this:

        ...
        ->pipe(new \koolreport\processes\Map(array(
            "{value}" => function($row) {
                if (!isset($row["amount"])) $row["amount"] = 0; 
                return $row;
            }
        ))) 
        ->pipe(new AccumulativeColumn(array(
            "running_amount"=>"amount"
        )))

It will solve the error but I'm not sure about the meaning you want.

Philippe Moser commented on Jan 12

It doesn't work because in the case of the exception. I don't have a single row.

What I really want is: If the dataset is empty after the query, I want stop processing.

After that I want to render a chart with a dummy row. For Example: "DateYear" = 2023, "amount" = 0 or just a text like "There are no data available for this parameter"

Do you know how I can check if there is not a single row before I call the AccumulativeColumn process?

This is my chart


LineChart::create(array(
                        "title"=>"Gesamt erstellte Jobs",
                        "dataSource"=>$this->dataStore('time_jobs_created'),
                        "columns"=>array(
                            "DateYear"=>array(
                                "label"=>"Monat"
                            ),
                            "running_amount"=>array(
                                "label"=>"Gesamt erstellte Jobs",
                                "type"=>"number",
                            )
                        ),
                        "options" => array(
                            "backgroundColor"=>"transparent",
                            "hAxis" => array(
                                "showTextEvery" => 12,
                            ),
                        )
                    ));
Sebastian Morales commented on Jan 16

If you want to know if a certain pipe inside a report setup has any data you can use the following method:

function  setup() {
    $this->src(...)
    ->pipe(...)
    ->pipe($this->dataStore("tmp"))
    ->requestDataSending();
    
    $emptyData = $this->dataStore("tmp")->count() === 0;
    ...
}
Philippe Moser commented 3 days ago

This worked for me:

// Jobs.php

$this->src("warehouse_datasource")
        ->query(...)
        ->params(...)
        ->pipe($this->dataStore("time_jobs_created"));
// Jobs.view.php

use \koolreport\processes\AccumulativeColumn;
...

if ($this->dataStore('time_jobs_created')->count() !== 0) {
                        LineChart::create(array(
                            ...
                            "dataSource"=>$this->dataStore('time_jobs_created')->process(new AccumulativeColumn(array(
                                "running_amount"=>"amount"
                            ))),
                            ...
                        ));
                    } else {
                        echo "There is no data available for this condition.";
                    }

I was not able to prepare the whole dataStore in the setup(). Because after I have the boolean $emptyData, I'm not able to reprocess a saved dataStore and saving it as another dataStore.

But thank you for your help :)

Sebastian Morales commented 20 hours ago

That's a good solution. Tks, Philippe.

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