i'm developing kpi management tool application quality services symfony, doctrine , jquery.
i created 3 entities are:
- application (id, name, ...)
- kpi (id, name, category, ...)
- value (application_id, kpi_id, month, value)
so value table has values like:
id_app | id_kpi | month | value 10 | 1 | january-15 | 40,00 10 | 2 | january-15 | 50,00 10 | 3 | january-15 | true 10 | 4 | january-15 | commentaire 22 | 1 | january-15 | 40,00 22 | 2 | january-15 | 40,00 etc... but need print this:
application| month | kpi 1 | kpi 2 | kpi 3 | kpi 4 | ....... 10 |january-15 | 40 | 50 | true | commentaire | ..... 22 |january-15 | 40 | 40 | false | commentaire | ..... i didn't manage it. don't know if conception , modeling good.
i manage format want (in mysql) query:
$sql = "select a.id, a.name, v.date, group_concat(if(id_kpi = 400, value, null)) 'coverage', group_concat(if(id_kpi = 401, value, null)) 'automation', group_concat(if(id_kpi = 402, value, null)) 'new tc', group_concat(if(id_kpi = 403, value, null)) 'total tc', group_concat(if(id_kpi = 409, value, null)) 'release/month' applications left join piqua_kpi.values v on a.id = v.id_application group a.name, v.date"; but didn't manage same doctrine , object can use.
here approach used create series of crosstab reports client. doing required abstracting @ least of formatting of pivot table. hope helps.
first, example of report: 
in report, county entity (linked address of household) column, , household entities income bracket row entry. cells counts of households contacted in given time period row , column characteristics.
the controller (there 3 pieces):
public function incomeprofileaction(request $request) { $form = $this->createform(new reportcriteriatype()); $criteria = $request->request->get('report_criteria'); $form->handlerequest($request); if ($form->isvalid()) { $response = new response(); $reportdata = $this->income($criteria); $content = $this->profiler($reportdata); $response->setcontent($content); return $response; } return $this->render('manaclientbundle:statistics:report_criteria.html.twig', array( 'form' => $form->createview(), 'extra' => 'profile', 'formpath' => "income_profile", 'title' => 'report criteria', 'criteriaheader' => 'select income profile reporting criteria', )); } private function income($criteria) { $em = $this->getdoctrine()->getmanager(); $xp = $this->container->get('mana.crosstab'); $datecriteria = $xp->setdatecriteria($criteria); $columntype = $criteria['columntype']; $rowlabels = $em->getrepository('manaclientbundle:income')->rowlabels($datecriteria); $collabels = $em->getrepository('manaclientbundle:' . $columntype)->collabels($datecriteria); $data = $em->getrepository('manaclientbundle:income')->crosstabdata($datecriteria, $columntype); $reportdata = [ 'reporttitle' => 'household income', 'reportsubtitle' => 'for period ', 'criteria' => $criteria, 'rowheader' => 'income bracket', 'rowlabels' => $rowlabels, 'collabels' => $collabels, 'data' => $data, ]; return $reportdata; } private function profiler($reportdata) { $xp = $this->container->get('mana.crosstab'); $profile = $xp->crosstabquery($reportdata['data'], $reportdata['rowlabels'], $reportdata['collabels']); $reports = $this->get('reports'); $specs = $reports->getspecs($reportdata['criteria']); return $this->renderview("manaclientbundle:statistics:profile.html.twig", ['profile' => $profile, 'rowheader' => $reportdata['rowheader'], 'rowlabels' => $reportdata['rowlabels'], 'collabels' => $reportdata['collabels'], 'reporttitle' => $reportdata['reporttitle'], 'reportsubtitle' => $reportdata['reportsubtitle'], 'date' => new \datetime(), 'specs' => $specs, ]); } template:
{% set start = specs.startdate|date('f, y') %} {% set end = specs.enddate|date('f, y') %} <h3 style="text-align: center;">{{ reporttitle }}</h3> <h4 style="text-align: center;">{{ reportsubtitle }} {{ start }}{% if (start != end) %} through {{ end }}{% endif %}</h4> <table class="table table-striped table-condensed"> <thead> <tr> <th>{{ rowheader }} {%- collabel in collabels -%} <th style="text-align: right;">{{ collabel }} </th> {%- endfor -%} <th style="text-align: right;">total</th> </tr> <tfoot> <tr> <td>total {% total in profile.total %} <td style="text-align: right">{{ total|number_format }} {% endfor %} <tbody> {% rowlabel in rowlabels -%} <tr> <td>{{ rowlabel }} {%- collabel in collabels -%} <td style="text-align: right"> {%- if profile[rowlabel][collabel] defined -%} {{ profile[rowlabel][collabel]|number_format }}{%- else -%} 0{%- endif -%} {% endfor %} <td style="text-align: right">{{ profile[rowlabel]['total']|number_format }} </tr> {%- endfor -%} </table> the crosstab service:
class crosstab { private $em; public function __construct(entitymanager $em) { $this->em = $em; } /** * * @param array $data = data array * @param array $rowlabels * @param array $collabels * @return array */ public function crosstabquery($data, $rowlabels, $collabels) { $profile = $this->profilearray( $rowlabels, $collabels); foreach ($data $array) { if (!array_key_exists('total', $profile[$array['rowlabel']])) { $profile[$array['rowlabel']]['total'] = 0; } $profile[$array['rowlabel']][$array['collabel']] = $array['n']; $profile[$array['rowlabel']]['total'] += $array['n']; } foreach ($profile $key => $array) { if (!array_key_exists('total', $array)) { $profile[$key]['total'] = 0; } } $profile['total'] = []; foreach ($profile $row => $array) { foreach ($array $key => $value) { if (!array_key_exists($key, $profile['total'])) { $profile['total'][$key] = 0; } $profile['total'][$key] += $value; } } return $profile; } private function profilearray($rows, $cols) { $colkeys = []; foreach ($cols $col) { $colkeys[$col] = 0; } $profile = []; foreach ($rows $row) { $profile[$row] = $colkeys; } return $profile; } /** * * @param string $query * @param array $criteria ['startmonth', 'startyear', 'endmonth', 'endyear'] * @return string */ public function setdatecriteria($criteria) { $startmonth = $criteria['startmonth']; $startyear = $criteria['startyear']; $starttext = $startyear . '-' . $startmonth . '-' . '01'; $endmonth = $criteria['endmonth']; $endyear = $criteria['endyear']; $enddate = new \datetime($endmonth . '/01/' . $endyear); $endtext = $enddate->format('y-m-t'); return "'$starttext' , '$endtext' "; } } county repository:
class countyrepository extends entityrepository { public function collabels($datecriteria) { $str = "select distinct cty.county county cty join contact c on c.county_id = cty.id c.contact_date between __date_criteria__ order county"; $sql = str_replace('__date_criteria__', $datecriteria, $str); $conn = $this->getentitymanager()->getconnection(); $stmt = $conn->executequery($sql); $colarray = $stmt->fetchall(); $collabels = []; foreach ($colarray $array) { $collabels[] = $array['county']; } return $collabels; } income repository:
class incomerepository extends entityrepository { public function rowlabels($datecriteria) { $str = "select distinct i.income income join household h on h.income_id = i.id join contact c on c.household_id = h.id c.contact_date between __date_criteria__ , i.enabled = true order i.id "; $sql = str_replace('__date_criteria__', $datecriteria, $str); $conn = $this->getentitymanager()->getconnection(); $stmt = $conn->executequery($sql); $rowarray = $stmt->fetchall(); $rowlabels = []; foreach ($rowarray $array) { $rowlabels[] = $array['income']; } return $rowlabels; } public function crosstabdata($datecriteria, $profiletype) { $str = "select r.__type__ collabel, i.income rowlabel, count(distinct h.id) n " . "from household h " . "join contact c on c.household_id = h.id " . "left join __type__ r on r.id = c.__type___id " . "left join income on h.income_id = i.id " . "where c.contact_date between __date_criteria__ " . "and i.enabled = true " . "group collabel, rowlabel"; $sql1 = str_replace('__date_criteria__', $datecriteria, $str); $sql = str_replace('__type__', $profiletype, $sql1); $conn = $this->getentitymanager()->getconnection(); $stmt = $conn->executequery($sql); return $stmt->fetchall(); } 