KoolReport's Forum

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

Multiple Data sources for 1 table #18

Closed Hallie Gromek opened this topic on on Jun 5, 2017 - 17 comments

Hallie Gromek commented on Jun 5, 2017

I am hoping you can show me how to make this work using the Cube function.

I need to create a grid like the checkmark grid below. Green, blue are standalone master tables (so I can always have all Dates and Test options shown) then I have a test_taken table (check marks) that would show where the meet.

How do I use different tables for the Columns and Rows and data with the Cube package? in the picture below for example I need the blank dates to also show.

Test_taken Table

KoolReport commented on Jun 5, 2017

Do you mean, you want Cube to create the first table (table with check marks) from the data of second table (test_taken)?

I have not seen the "green" table that you mentioned.

Please let me know the format of source data and the result format you want to achieve.

Hallie Gromek commented on Jun 5, 2017

Hi Sorry,

I have 3 tables one with Dates, tests and test_taken. I want Dates to be the column heads and tests for Row leaders and test_taken the data. I need it to show the dates or tests that do not have data in the test_taken table. Does this make sense?

below is the green and blue

KoolReport commented on Jun 6, 2017

It is possible, you need to create fake data to insert into table:

<?php
class TestReport extends \koolreport\KoolReport
{
    function settings()
    {
        return array(
            "dataSources"=>array(
                "real_data"=>array(
                    ...
                )
                "fake_data"=>array(
                    "class"=>'\koolreport\datasources\ArrayDataSource',
                    "dataFormat"=>"associate",
                    "data"=>array(
                        array("test_taken"=>"Amy","test_date"=>"1/15/2017","test"=>null),
                        array("test_taken"=>"Amy","test_date"=>"2/15/2017","test"=>null),
                        array("test_taken"=>"Amy","test_date"=>"3/15/2017","test"=>null),
                        array("test_taken"=>"Amy","test_date"=>"4/15/2017","test"=>null),
                        array("test_taken"=>"Amy","test_date"=>"5/15/2017","test"=>null),
                        array("test_taken"=>"Amy","test_date"=>"6/15/2017","test"=>null),                        
                    )
                )
            )
        );
    }

    function setup()
    {
        $this->src('real_data')
        ->pipe(new Cube(
            "row'=>"test",
            "column=>"test_date",
            "count"=>"test_taken"
        ))
        ->saveTo($cube);

        $this->src('fake_data')
        ->pipe($cube);

        $cube->pipe($this->dataStore('end_data'));
    }
}

What we have done above is create a fake data contain all possible test date and test is null. When those data go through Cube they will create the missed date column.

In the setup() function, you see that the real data is piped to Cube, and it is saved to $cube. Then the fake data is piped to $cube as well. The result is like you pour two bottle of water into the same big cup(which is Cube). Then the result will be saved to __end_data__ data store ready to be populated in table.

Please let me know if you need further assistance.

Regards,

Peter

David Winterburn commented on Jun 6, 2017

Hi Hallie,

The cube table can only show rows (e.g: tests) and columns (dates) with existing data (test_date_taken). So if you wanted to show even rows and columns with non-existing data you would have to create zero data for those rows and columns like this:

<?php 
$node = $this->src("test")
->query("SELECT date, test, 0 as 'taken' FROM date_table, test_table
JOIN
SELECT date, test, 1 as 'taken' from test_taken");
->pipe(new Cube(array(
      "row" => "test",
      "column" => "date",
      "sum" => "taken"
    )))
->pipe($this->dataStore("testTaken"));
?>

After the cube process, the non-existing test-taken will be 0 while existing test-taken will be 1 (or 2, 3 or more depending on your test_taken data). Finally, you could use the Table widget to show the data store. Please let us know you have any problem. Thanks!

Hallie Gromek commented on Jun 6, 2017

Thank you!! I got this to work, Could you show me how to convert the 1 into a Check mark image and the 0 do not display?

KoolReport commented on Jun 7, 2017

Hi Hallie,

You may use the ValueMap process after Cube to convert

->pipe(new ValueMap(array(
    "test_taken"=>array(
        0=>"",
        1=>"<i class='fa fa-check'></i>",
        "{meta}"=>array("type"=>"string")
    )
)));

Above code will convert the 0 to empty string and 1 to check icon ( if you use awesome font), also the meta of column will change type from number to string.

Hope that helps.

Hallie Gromek commented on Jun 9, 2017

I can't get the checks or empty string to produce. All I get is a new column "taken". Here's the actual code I am working with. Can you help me? Any advice is appreciated! `

->pipe(new Cube(array(
  "row" => "Type",
  "column" => "territory",
  "sum" => "taken"
)))

->pipe(new ValueMap(array(
"taken"=>array(
    0 => "",
    1 => "<i class='fa fa-check'></i>",
    "{meta}"=>array("type"=>"string")
)
)))


![](https://cdn.koolreport.com/assets/images/editor/c1/image593a97d82d3dc.png)
KoolReport commented on Jun 10, 2017

Hi Hallie,

Do you add the FontAwesome library to the view? If not then the <i class="fa fa-check"></i> will appear empty

We have release KoolReport 1.27.6. If you have installed this version you can do this:

<?php
class MyReport extends \koolreport\KoolReport
{
    use \koolreport\clients\FontAwesome;
    ...

}

Please let me know if it works.

Hallie Gromek commented on Jun 12, 2017

still not populating, I installed the newest version, add the use. code above. Code below and how the grid showing.

	->pipe(new Cube(array(
      "row" => "Type",
      "column" => "territory",
      "sum" => "taken"
    )))
	
	->pipe(new ValueMap(array(
    "taken"=>array(
        0 => "",
        1 => "<i class='fa fa-check'></i>",
        "{meta}"=>array("type"=>"string")
    )
	)))
KoolReport commented on Jun 12, 2017

We will try to make an working example for you.

KoolReport commented on Jun 13, 2017

Hi Hallie,

We have created a better solution for you, better than using the ValueMap (actually difficulty to work). Please download this zip file: example_valuemap.zip.

Please do not bother the name of "ValueMap", the example does not use ValueMap process.

After download please do:

  1. Unzip the file
  2. You will find another zip file contain Table.php and Table.tpl.php. This two file is new version of koolphp\Table. Please copy into koolreport\widgets\koolphp folder
  3. Now you can run the example, please correct the require path in TestValueMap.php so that it can refer to koolreport library.

In this solution, we have add for Table the function formatFunction in which you can set custom function to format the value or to display value in the way you want.

To apply to your report, please remove all ValueMap process, and apply the formatFunction to Table as we use the the view of report.

Please let me know if you get it work.

Regards,

KoolPHP Inc

Hallie Gromek commented on Jun 13, 2017

It is not working with a Cube function? the Rows are not populating. Can you show me and example with a Cube?

KoolReport commented on Jun 15, 2017

Sorry for my late rely, I will come back today with an example.

KoolReport commented on Jun 16, 2017

Dear Hallie,

Follow up on the problem on forum of KoolReport: https://www.koolreport.com/forum/topics/18

I would like to send you the new version KoolReport 1.30.6 and the example of showing custom value on table cell.

In the example we use fa-circle to display circle but you can change to fa-check to display check mark.

Basically, in koolphp table, you can have this settings:

"columns"=>array(
    "type",
    "{others}"=>array(
        "formatValue"=>function($value)
        {
            return $value>0?"<i class='fa fa-check'></i>":"";
        }
    )
)

The "{others}" means all other columns. The formatValue is function to convert value of cell to any string.

Please let us know if you need any further assistance.

Best regards,

Karl

Hallie Gromek commented on Jun 16, 2017

Awesome!! this is working!! thank you!! One last question, Can I center the DOTS?

KoolReport commented on Jun 16, 2017

You do this:

Table::create(array(
    ...
    "cssClass"=>array(
        "table"=>"table table-hover table-bordered",
        "th"=>function($name){
            return $name!="type"?"text-center":"";
        },
        "td"=>function($value,$name){
            return $name!="type"?"text-center":"";
        }
    )
));
Hallie Gromek commented on Jun 16, 2017

Worked like a charm!! Thank you!

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

Cube