KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.

How do I add dynamic columns #1180

Open Ron opened this topic on on Nov 25 - 39 comments

Ron commented on Nov 25

Hello,

I have a data source and I want to add to it two type of columns during the init of the report

  1. Row 1 columns that shows all the dates in a month for example 1|2|3|4|5|6 etc.
  2. below that row it will show the day of each date for example s|m|t|w|t etc.

the rows show be added to the datasource columns (10 columns ) are coming from the data source. columns from the data source should be rowspan=2 and the dynamic date columns show be a separate row each so it should look something like this

Ron commented on Nov 25

btw the red columns shows Saturday. so I need also to be capable to set it dynamically once the report is rendered.

David Winterburn commented on Nov 28

HI Ron,

How do you fill in data for each day column? Please show an example of your data row. Thanks!

Ron commented on Nov 28

Hi David,

Thank you for answering. In the meantime I managed to overcome a problem in my report. instead of trying to generate the date columns (all dates in selected month) dynamically in the report side I did it in MySQL stored procedure side. that way I already receive all the columns ready. You can view the report (although it's data is in Hebrew) in the following link: https://www.parashapp.com/dev/sbm/report now I have the following issues: a. I want to set settings to the columns in the report like for example: align-right or type=string etc for specific columns but I do not want to write it in the columns array because I do not know the names of the columns I get from the report because if it generated dynamically. b. I do I join all the cells of substitute_teacher_name. I do not need it to appear in each row. c. each substitute teacher has two rows or more for teacher_name I need to join the cells the relevant teacher_name. d. I need to add a calculated new row that sum each two rows of a teacher_name and sum the total_hours. Thanks

David Winterburn commented on Nov 29

Hi Ron,

It's a bit hard to understand your description as we could not view your page (it requires a login). Do you mind posting a screenshot/mockup with some drawing to explain your requirement? Thanks much!

Ron commented on Dec 3

OK I tried to make it as simple as possible to understand

Ron commented on Dec 3

This is the current code for the table view

Table::create(array(
            'dataStore'=>$this->dataStore('Substitutes'),
            'columns'=>array(
                '{others}'=>array(
                    'formatValue'=>function($value)
                    {
                        return $value == NULL ? '' : $value;
                    }
                ),
                'total_hours'=>array(
                    'label'=>lang('cost')
                ),
            ),
            'cssClass'=>array(
                'table'=>'table table-hover table-bordered',
                'tr'=>'cssItem',
                'td'=>function($row,$colName)
                {
                    return in_array($colName, array('substitute_teacher_name','id_number','full_address','bank_account','teacher_name','group_name')) ? 'rext-right' : 'text-center';
                },
                'th'=>function($colName)
                {
                    return in_array($colName, array('substitute_teacher_name','id_number','full_address','bank_account','teacher_name','group_name')) ? 'cssHeader text-right' : 'cssHeader text-center';
                },
            ),
            "paging"=>array(
                "pageSize"=>25,
                "pageIndex"=>0,
            ),
            "grouping"=>array(
                "substitute_teacher_name"=>array(
                    "calculate"=>array(
                        "{sumAmount}"=>array("sum","total_hours"),
                    ),
                    "bottom"=>'<b>{substitute_teacher_name}: {sumAmount}</b>',
                ),
            ),
            "excludedColumns"=>array("total_hours")
        ));
David Winterburn commented on Dec 4

Hi Ron,

Regarding your requirement in the screenshot:

  1. At the moment the Table widget hasn't supported creating colspan, rowspan in the header yet. If you use datagrid/DataTables widget there's an available solution for such complex headers.

  2. Again, the Table widget has no way to set its columns' label with a function but you could set a datastore metadata programmatically like this:

//MyReport.php

public function setup()
{
    ...
    ->pipe(new \koolreport\processes\Map(array(
        "{meta}" => function($meta) {
            $colMetas = $meta["columns"];
            foreach ($colMetas as $colKey => $colMeta) {
                $colMetas[$colKey]["label"] = modify($colKey);
            }
            $meta["columns"] = $colMetas;
            return $meta;
        }
    )))
    ->pipe($this->dataStore("myDatastore"));
}
  1. To export a whole table to pdf, just remove paging option in the Table/DataTables widget create in your pdf view file instead of your report view file.

Hope this help! Thanks!

Ron commented on Dec 4

Thanks for you swift replay! Does it means that I need to go Pro? if yes I will purchase it now can you help me achieve the solution? Can I still get the price with the 40% off?

David Winterburn commented on Dec 4

Hi Ron,

You could buy the Datagrid package if you only need DataTables widget or you could by Pro version if you need to use other packages as well.

As for complex table header with rowspan, colspan, we have option in DataTables to enable that. Thanks!

Ron commented on Dec 4

Ok. I will go pro! Can you send me please the solution using the DataTables

Ron commented on Dec 4

Yesterday I got a different price of 218 USD including Perpetual Usage For Developer License. now the price is 298. is there an option to still get this price?

Ron commented on Dec 4

is it possible?

Ron commented on Dec 4

Order complete. is there a more convenient way to get support like instant message or email for pro license?

David Winterburn commented on Dec 5

Hi Ron,

In order to use header hierarchy with datagrid/DataTables please set up your table columns in the following format:

GroupA Cat1.1 - Cat2.1 Cat1.1 - Cat2.2

Then create DataTables like this:

        DataTables::create([
          'dataSource' => $ds,
          'complexHeaders' => true,
          'headerSeparator' => ' - ',
        ]);

You could replace the separator " - " with anything your like, such as " || ", etc. DataTables would divide the headers using the separator and put in rowspan, colspan in th for you.

Please try this with simple data and let us know the result. Thanks!

P.S: To get support you could either post your question here or send email to support@koolphp.net. We will try to answer in the next 24 hours if we find a quick solution. Some hard issues could take more time to solve. Thanks a lot!

Ron commented on Dec 5

Can you show me a sample code how the column header should look like. please note that the second row of the header should be calculated dynamically on report render because it shows the day of week of for each column in the first row that display the date.

Ron commented on Dec 5

You said the the header format should look like this GroupA Cat1.1 - Cat2.1 Cat1.1 - Cat2.2 how do I code it in the columns property

David Winterburn commented on Dec 5

In your specific case, Map process could be useful:

->pipe(new \koolreport\processes\Map(array(
    "{value}" => function($row, $metaData, $index, $mapState) {
        for ($monthday=1; $monthday<=30; $monthday++) {
			$weekday = getWeekday($monthday);
			$row[$monthday . " - " . $weekday] = $row[$monthday];
			unset($row[$monthday]);
		}           
        return $row;
    }
)))
...

You would need to write function getWeekday to compute weekday from monthday. Then try the DataTables set up in my previous post. Thanks!

Ron commented on Dec 5

ok tnx it did the job but now i caused two issues 1. all the columns with the date and weekday were pushed to the end of the table and the last columns coming before it. 2. How do I align label of the columns to be in the middle of the row vertically and horizontally

Ron commented on Dec 5

here is the image.

another two questions please: 1. in the column substitute_teacher_id I want the name of the teacher to appear only one time for each group of rows. meaning I want to rowspan it. and same for the column teacher_id.

  1. eventually in the end I want to export all of it to an excel file. what do I need to do in order for the excel to look exactly like the tadatable.
David Winterburn commented on Dec 5

Hi Ron,

  1. Please try this:
->pipe(new \koolreport\processes\Map(array(
    "{value}" => function($row, $metaData, $index, $mapState) {
        $newRow = [];
        foreach ($row as $k => $v) {
            if (is_numeric($k)) $k = 1*k;
            if ($k >= 1 && $k <= 30) { //i.e $k is month day
                $weekday = getWeekday($k);
		$newRow[$k. " - " . $weekday] = $row[$k];
            } else {
                $newRow[$k] = $row[$k];
            }
        }
                
        return $newRow;
    }
)))
  1. As for centering label, please use CSS:

https://stackoverflow.com/questions/5703552/css-center-text-horizontally-and-vertically-inside-a-div-block

Hope that help!

Ron commented on Dec 5

ok its working tnx. 1. in the column substitute_teacher_id I want the name of the teacher to appear only one time for each group of rows. meaning I want to rowspan it. and same for the column teacher_id. 2. this code is not working now since we shifted from table to datatable

"grouping"=>array(
                "substitute_teacher_id"=>array(
                    "calculate"=>array(
                        "{sumAmount}"=>array("sum","total_hours"),
                    ),
                    "bottom"=>'<b>{substitute_teacher_id}: {sumAmount}</b>',
                ),
            ),
            "excludedColumns"=>array("total_hours")
  1. eventually in the end I want to export all of it to an excel file. what do I need to do in order for the excel to look exactly like the tadatable.
Ron commented 4 days ago

Good morning. still waiting for your response on my last email/post

David Winterburn commented 3 days ago

Hi Ron,

To use grouping in datagrid/DataTables, please use the following format:

DataTables::create(array(
	...
	"options" => array(
		'rowGroup' => [
		  'dataSrc' => [10], //10 is the order of the column you want to group
		  "endRender" => "function ( rows, group ) {
                var salaryAvg = rows
                    .data()
                    .pluck(5) //5 is the order of the salary column
                    .reduce( function (a, b) {
                        return a + b.replace(/[^\d]/g, '')*1;
                    }, 0) / rows.count();
                salaryAvg = $.fn.dataTable.render.number(',', '.', 0, '$').display( salaryAvg );
 
                var ageAvg = rows
                    .data()
                    .pluck(3) //3 is the order of the age column
                    .reduce( function (a, b) {
                        return a + b*1;
                    }, 0) / rows.count();
 
                return $('<tr/>')
                    .append( '<td colspan=3>Averages for '+group+'</td>' )
                    .append( '<td>'+ageAvg.toFixed(0)+'</td>' )
                    .append( '<td/>' )
                    .append( '<td>'+salaryAvg+'</td>' );
            }"
		],
	),
	...
));

I use this grouping with averaging example from this link:

https://datatables.net/extensions/rowgroup/examples/initialisation/customRow.html

You could easily replace average with sum by removing the dividing by rows.count(). Let me know if you have any question. Thanks!

Ron commented 3 days ago

ok tnxs I will try it. How do I export all this datatable with is same format (complex header) to excel. you can see in the above messages an image with the columns of the datatable.

David Winterburn commented 3 days ago

Hi Ron,

Sorry for missing your excel export question. At the moment our excel Table widget hasn't had the ability to export grouping and complex headers yet. It's in our roadmap for the Excel package but it could take some time to develop, probably several months in the future.

If and when it's tested or released you would get the update automatically with your current KoolReport Pro license. Let us know your thought. Thanks!

Ron commented 3 days ago

David, the only reason I purchased koolreport is in order to generate this report in excel. if I can not make it with the current package so its useless for me.

David Winterburn commented 3 days ago

Hi Ron,

datagrid/DataTables is a html/js/css widget which has features such as complex headers and grouping as I promised.

The ability to export to Excel depends on the Excel package which has its own Table, Chart, PivotTable widgets. At the moment the Excel package has not supported complex headers or grouping yet.

If you need this feature as soon as possible I would suggest using a client-side solution like this:

https://datatables.net/extensions/buttons/examples/initialisation/export.html

In order to do this please download the datatables.min.js including all the plugins in this example and replace the file KoolReport/datagrid/DataTables/datatables.min.js with it:

https://datatables.net/download/

Hope this helps! Let us know if there's any difficulty. Thanks!

Ron commented 2 days ago

ok. tnx for the option. I did not try it yet because I am still busy with the other two issues. hope it will eventually work in the excel. in the meantime I need you assistant. How fo I hide a column in the datatable?

Ron commented 1 day ago

??

David Winterburn commented 1 day ago
DataTables::create(array(
	...
	"options" => array(
		'columnDefs' => array(
			array(
				'visible' => false,
				'targets' => [0], //hide the first column
			)
		),
	),
	...
)); 
Ron commented 1 day ago

tnx sir. I have a problem in my header columns label. I do a map process to change the label names to the relevent language and when I var_dum the $meta I see that the column names were changed correctly but when the report is displays I still see the original datasource column names

this is the code

->pipe(new \koolreport\processes\Map(array(
            "{meta}" => function($meta) {
                $colMetas = $meta["columns"];
                $index = 1;
                foreach ($colMetas as $colKey => $colMeta) {
                    if ( $index < 7 || $index > sizeof($colMetas) - 2 ) {
                        $colMetas[$colKey]["label"] = lang($colKey);
                        $colMetas[$colKey]["type"] = "string";
                    }
                    $index++;
                }
                $meta["columns"] = $colMetas;
                //echo "<pre>";
                //var_dump($meta);
                //echo "</pre>";
                return $meta;
            }
        )))
David Winterburn commented 1 day ago

Could you please post your Table::create setup? There's a chane the setup could override the datastore's column meta.

Ron commented 23 hours ago
DataTables::create(array(
            "dataSource"=>$this->dataStore("st"),
            "options"=>array(
                "searching"=>false,
                "paging"=>false,
                'columnDefs' => array(
        			array(
        				'visible' => false,
        				'targets' => [37], //hide the first column
        			)
        		),
        		'rowGroup' => [
        		     'dataSrc' => [0], //10 is the order of the column you want to group
        		     'endRender' => "function ( rows, group ) {
                         var salaryAvg = rows
                            .data()
                            .pluck(5) //5 is the order of the salary column
                            .reduce( function (a, b) {
                                return a + b.replace(/[^\d]/g, '')*1;
                            }, 0) / rows.count();
                        salaryAvg = $.fn.dataTable.render.number(',', '.', 0, '$').display( salaryAvg );

                        return $('<tr/>')
                            .append( '<td colspan=39>סה״כ שעות מילוי מקום למורה '+group+'</td>' )
                            .append( '<td/>' )
                            .append( '<td>'+salaryAvg+'</td>' );
                    }"
        		],
            ),
            'complexHeaders' => true,
            'headerSeparator' => ' - ',
            'cssClass'=>array(
                'table'=>'table table-hover table-bordered',
                'tr'=>'cssItem',
                'td'=>function($row,$colName)
                {
                    return in_array($colName, array('substitute_teacher_id','id_number','full_address','bank_account','teacher_id','group_name')) ? 'cssItem rext-right' : 'cssItem text-center';
                },
                'th'=>function($colName)
                {
                    return in_array($colName, array('substitute_teacher_id','id_number','full_address','bank_account','teacher_id','group_name')) ? 'cssHeader text-right' : 'cssHeader text-center';
                },
            ),
        ))
Ron commented 21 hours ago

I think I found the bug. when I set 'complexHeaders' => true, then this problem appears but when it set to false then I see the correct column labels as set in the Map process. Can you please look into it.

Ron commented 21 hours ago

this is the setup of the report

function setup()
    {
        $this->src('sbm')
        ->query('CALL getSubstituteTeacherHourListPerMonth(:year, :date)')
        ->params(array(
            ":year"=>$this->params["year"],
            ":date"=>$this->params["date"]
        ))
        ->pipe(new \koolreport\processes\Map(array(
            "{value}" => function($row, $metaData, $index, $mapState) {
                $days = array('א','ב','ג','ד','ה','ו','ש');
                $month = date('m', strtotime($this->params["date"]));
                $year = date('Y', strtotime($this->params["date"]));
                $newRow = [];
                foreach ($row as $k => $v) {
                    if (is_numeric($k))
                        $k = 1*$k;
                    if ($k >= 1 && $k <= 31) { //i.e $k is month day
                        $weekday = date('w', strtotime($year.'-'.$month.'-'.$k));
        		        $newRow[$k. " - " . $days[$weekday]] = $row[$k];
                    } else {
                        $newRow[$k] = $row[$k];
                    }
                }
                return $newRow;
            }
        )))
        ->pipe(new \koolreport\processes\Map(array(
            "{meta}" => function($meta) {
                $colMetas = $meta["columns"];
                $index = 1;
                foreach ($colMetas as $colKey => $colMeta) {
                    if ( $index < 7 || $index > sizeof($colMetas) - 2 ) {
                        $colMetas[$colKey]["label"] = lang($colKey);
                        $colMetas[$colKey]["type"] = "string";
                    }
                    $index++;
                }
                $meta["columns"] = $colMetas;
                //echo "<pre>";
                //var_dump($meta);
                //echo "</pre>";
                return $meta;
            }
        )))
        ->pipe($this->dataStore("st"));
    }
David Winterburn commented 6 hours ago

Hi Ron,

Yes, you are correct. The complex headers feature must modify/split the table's header content and because it performs modification on the column name, the column label isn't shown. Even if we want to show the label, says a column is {name: "group 1 - cat 1.1", label: "my column label"} which text should be shown for header cat1.1?

If you really want to to localize the headers I would suggest changing the column name directly and not the column label, i.e apply the lang() function in the first Map process. Thanks!

Ron commented 4 hours ago

how to I join the two map processes. actually if I can change the meta in the first process then I do not need the second one. can you please tell me how do I change the label of the meta from the first map process?

->pipe(new \koolreport\processes\Map(array(
            "{value}" => function($row, $metaData, $index, $mapState) {
                $days = array('א','ב','ג','ד','ה','ו','ש');
                $month = date('m', strtotime($this->params["date"]));
                $year = date('Y', strtotime($this->params["date"]));
                $newRow = [];
                foreach ($row as $k => $v) {
                    if (is_numeric($k))
                        $k = 1*$k;
                    if ($k >= 1 && $k <= 31) { //i.e $k is month day
                        $weekday = date('w', strtotime($year.'-'.$month.'-'.$k));
        		        $newRow[$k. " - " . $days[$weekday]] = $row[$k];
                    } else {
                        $newRow[$k] = $row[$k];
                    }
                }
                return $newRow;
            }
        )))
David Winterburn commented 3 hours ago

Hi Ron,

One Map process allows for both "{value}" and "{meta}" properties. However, in your case I think we could change the 2nd Map process like this:

        ->pipe(new \koolreport\processes\Map(array(
            "{value}" => function($row, $metaData, $index, $mapState) {
                $days = array('א','ב','ג','ד','ה','ו','ש');
                $month = date('m', strtotime($this->params["date"]));
                $year = date('Y', strtotime($this->params["date"]));
                $newRow = [];
                foreach ($row as $k => $v) {
                    if (is_numeric($k))
                        $k = 1*$k;
                    if ($k >= 1 && $k <= 31) { //i.e $k is month day
                        $weekday = date('w', strtotime($year.'-'.$month.'-'.$k));
        		        $newRow[$k. " - " . $days[$weekday]] = $row[$k];
                    } else {
                        $newRow[$k] = $row[$k];
                    }
                }
                return $newRow;
            }
        )))
        ->pipe(new \koolreport\processes\Map(array(
            "{value}" => function($row, $meta) {
                $colMetas = $meta["columns"];
                $index = 1;
                $newRow = [];
                foreach ($colMetas as $colKey => $colMeta) {
                    if (! isset($row[$colKey])) continue;
                    if ( $index < 7 || $index > sizeof($colMetas) - 2 ) {
                        $newKey = lang($colKey);
                        $newRow[$newKey] = $row[$colKey];
                    } else {
                        $newRow[$colKey] = $row[$colKey];
                    }
                    $index++;
                }
                return $newRow;
            }
        ))) 

Let me know how it works. Thanks!

Ron commented 15 mins ago

its does the job BUT it removes all the date columns beside one. please see picture

KoolReport Is Free and Open-Source!

KoolReport is a professional php reporting framework which saves you tons of time to construct dynamic data report & dashboard.

  • Connect to various datasources such as MySQL, SQL Server, MongoDB or even from CSV or Excel file.
  • Contain series of powerful built-in data processes for your data manipulation.
  • Generate stunning charts and graphs to help you communicate data insights to your audiences effectively.
  • Integrate seamlessly with any php frameworks such as Laravel, CodeIgniter, Symfony.
Download Now and register our tutorials to get started!

Download KoolReport
None yet

None