Is there any way to group and sum column data in a CGridView

I am interested in grouping data in my CGridView widget. I would like to display all employees in a department grouped together with individual hours displayed. I would like the total hours worked for the department displayed in a row below the last employee of the group.

Is there a way to do this with CGridView?

Here is a good .net example of what I am trying to accomplish in Yii: www.agrinei.com/gridviewhelper/gridviewhelper_en.htm

CGridView Totals or Summary Row - Yii Framework Forum

Nice thread, thank for pointing!!

You are really the memory of this forum!

Hehe, the secret is this new concept which some browsers already support: It’s called “bookmark”

Thanks for the input. This is close to what I want, but it does not appear to give subtotals. Am I correct, or did I miss some details of that post? In other words, I really want to display something like 50 records on one page… The first 10 records belong to one department and the next 10 belong to another, and so forth… At the end of each of the departments 10 records, I would like to have a row that displays the total for just their 10 records.

The link you shared seems to display only totals for all data… Which is great and I need that, but I am wondering if the subtotals is possible to.

Thanks again for your help. I do appreciate the input.

This is not really possible with a CDataGrid (or CDataList), because these lists always render one row per query result row. Plus a header and a footer. There’s no (easy) way to squeeze intermediary rows somewhere in the mid of the result list.

One approach to solve this could be, to use several consecutive CDataGrids, one for each group of people. But this will probably not work so nice and e.g. lead to problems with pagination though.

Or you could create your custom DataGrid class - but this approach requires a good amount of thinking. On the other hand it’s a good practice to get used to the internals of the framework. Extending a class and overriding the parts that don’t fit into your problem is not so hard after all. Even though CDataGrid is one of the more complex ones ;)

Thanks for your input. Last night I started writing a class that extends CGridView and was able to get it to work. There are still some kinks to work out. I overwrote the renderTableRow of the CGridView method and in that method, I look for a custom class I wrote that extends CGridColumn. If the column is my custom class, then I execute a method that writes a header… That class also keeps track of values. When the value changes, it writes a new header and possibly a footer with sums. That again is another custom class.

I am well on my way to solving this and it is a bit of a hack, but I think it will work. As a beginner it is fun to dive into the details of Yii. I will post the code I have written so far in case anyone is interested. It seems like this thread is getting a few hits.

Here is my custom GridView object to this point…




class ChiGridView extends CGridView

{

	public $name;

	private $assets;


	public function init()

	{

		parent::init();

		$file=dirname(__FILE__).DIRECTORY_SEPARATOR.'resources';

		$this->assets=Yii::app()->getAssetManager()->publish($file);

		$this->registerClientScript();

		//Yii::app()->clientScript->registerCoreScript('jquery');

	}

	

	public function registerClientScript()

	{

		$cs=Yii::app()->clientScript;

		$cs->registerCssFile($this->assets.'/chigrid.css');

	}


	public function renderTableRow($row)

	{

		

		foreach ($this->columns as $colNumber => $column)

		{

			if ($column instanceof ChiGridRow)

			{

				$data=$this->dataProvider->data[$row];

				$column->renderTableRow($colNumber, $row, $data, $this->columns);

			}

		}

		parent::renderTableRow($row);

	}

	

	

	public function renderTableBody()

	{

		parent::renderTableBody();

		foreach ($this->columns as $colNumber => $column)

		{

			if ($column instanceof ChiGridRow)

			{

				$column->showFooter($this->columns);

			}

		}

	}

	

}



Here is my ChiGridRow class (Still a work in progress). It extends the CGridColumn class, but it you can see that I am writing an entire row and not just a column if the showHeader property is set to true. In my case, it writes out the data value of my departments on a row all by itself as a header. The rows that follow are the data for those departments.




class ChiGridRow extends CGridColumn

{

	public $visible=true;

	private $last;

	public $attribute;

	public $value;

	public $duplicate = " ";

	public $name;

	public $type;

	public $headerCssClass = "chiheader filtered";

	public $footerCssClass = "chifooter filtered";

	public $showHeader = true;

	public $showFooter = false;

	public $summaryText;

	

	public function renderTableRow($colNumber, $row, $data, $columns)

	{

		if($this->value!==null)

			$value=$this->evaluateExpression($this->value,array('data'=>$data,'row'=>$row));

		else if($this->name!==null)

			$value=CHtml::value($data,$this->name);

		if ($value != $this->last)

		{

			if (isset($this->last))

			{

				$this->showFooter($columns);

			}

			if ($this->showHeader)

			{

				echo "<tr class=\"{$this->headerCssClass}\"><td colspan=\"" . sizeof($columns) . "\">";

				$this->last = $value;

				echo $value;

				echo "</td></tr>";

			}

		}

	}

	

	public function showFooter($columns)

	{

		if ($this->showFooter)

		{

			echo "<tr class=\"{$this->footerCssClass}\">";

			foreach ($columns as $colNumber => $column)

			{

				if ($column instanceof ChiRunningTotalColumn)

				{

					echo "<td>" . $column->getTotal() . "</td>";

					$column->resetTotal();

				}

				elseif ($column instanceof ChiGridRow)

				{

					if (isset($this->summaryText))

						echo "<td>". $this->last . " " . $this->summaryText . "</td>";

					else

						$this->displayEmpty();

				}

				else

					$this->displayEmpty();

			}

			echo "</tr>";

		}

	}

	

	


	

	protected function renderDataCellContent($row,$data)

	{

		echo "<span style=\"visibility: hidden\">" . $data[$this->name] . "</span>";

	}

	


	private function displayEmpty()

	{

		echo "<td></td>";

	}


}



Finally, I have the ChiRunningTotal class that displays summary data at the bottom of each group. If you want to display totals for each group, then this class is needed.




class ChiRunningTotalColumn extends CGridColumn

{

	public $attribute;

	private $total;

	private $grandTotal;

	public $name;

	

	public function renderDataCellContent($row, $data)

	{

		if (is_object($data))

		{

			$this->total += $data->{$this->name};

			$this->grandTotal += $data->{$this->name};

			echo $data->{$this->name};

		}

		else // array

		{

			$this->total += $data[$this->name];

			$this->grandTotal += $data[$this->name];

			echo $data[$this->name];

		}

		

	}

	

	public function resetTotal()

	{

		$this->total = 0;

	}

	

	public function getTotal()

	{

		return $this->total;

	}

}



Lastly, here is the code I use in my view:




$this->widget('application.extensions.chi.ChiGridView', array(

	 'id'=>'reportWidget',

     'dataProvider'=>$dataProvider,

     'columns'=>array(

			array

			(

				'header' => 'Department',

				'class' => 'application.extensions.chi.ChiGridRow',

				'name' => 4,

				'summaryText' => 'Total',

			),

			array('header' => 'Last Name', 'name' => 2),

			array('header' => 'First Name', 'name' => 1),

			array('header' => 'User Name', 'name' => <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='8)' />,

			array('header' => 'ID', 'name' => 7),

			array('header' => 'Punch ID', 'name' => 10),

			

            array(

				'header' => 'Exempt', 

				'name' => 'exempt',

				'type' => 'raw',

				'value' => '$data[9] == 1 ? CHtml::image(Yii::app()->theme->baseUrl . "/images/icons/tick.png", "exempt") : ""',

				'htmlOptions' => array('align' => 'center'),

			),            

     )

     ));



In this example, I don’t show the summary columns, but essentially I just have another row that is a application.extensions.chi.ChiRunningTotalColumn class. That data will display on each row and when the department changes, a summary row will be generated.

how to implement grouping in gridview like the .net example www.agrinei.com/gridviewhelper/gridviewhelper_en.htm