Subtotals

[rafaels86]

Is there a way to create subtotals on rows and columns when exists more than one field in each axis?

Rafael

[nicolaskruchten]

This isn't supported at the moment, no, but it's a good idea. It would enable expanding/collapsing (#25).

The right way to implement this would be to first modify the PivotData structure to contain subtotal information encoded somehow, and then to modify the default table renderer.

[rafaels86]

Right, I have a project that will need this functionality, and I guess this implementation will not occur soon, then can you give some tip to help me developing this? When ready I post the changes.

[cihadturhan]

It's been 4 months. Any progress on this? Because I need to use this in a project. If anyone started yet, I'll give it a try.

[nicolaskruchten]

I don't believe anyone is working on this at the moment.

My recommendation is that you start by making the minimal possible changes to the PivotData class to support subtotal aggregation. I would be happy to accept this as a pull request.

Once that's done, you can develop a new Renderer based on the Table Renderer which displays subtotals. I would be happy to accept that as a separate renderer in its own file, but probably not as an extensive set of modifications to the built-in Table Renderer.

Hope this makes sense, let me know if I can provide any pointers!

[rafaels86]

I even tried, but considering my best programming language is not JavaScript, I developed this in PHP. If you want, I can post the code.

[cihadturhan]

@nicolaskruchten I'll start then. I'll try to make it like igniteui pivotgrid does. However, the problem is I don't know coffeescript. (even changing id to classes was pain for me :)

@rafaels86 could do somehow send me PHP code for a start point for me?

[rafaels86]

Yes, follows:

function PivotTable($arrDados, $arrRows, $arrCols, $arrValues) {
$pivot = array();
$arrLabelsRow = array();
$arrLabelsCol = array();
$inicio = TempoExecucaoInicia();
for ($i = 0; $i < count($arrDados); $i++) { //registros
$chaveRow = "";
for ($lin = 0; $lin < count($arrRows); $lin++) { //linhas
if ($chaveRow)
$chaveRow .= "|";
$chaveRow .= coalesce($arrDados[$i][$arrRows[$lin]], 'null'); //sumariza para cada campo de linha
}
if (array_search($chaveRow, $arrLabelsRow) === false)
$arrLabelsRow[] = $chaveRow;
$chaveCol = "";
for ($col = 0; $col < count($arrCols); $col++) { //colunas
if ($chaveCol)
$chaveCol .= "|";
$chaveCol .= coalesce($arrDados[$i][$arrCols[$col]], 'null'); //sumariza para cada campo de coluna
}
if (array_search($chaveCol, $arrLabelsCol) === false)
$arrLabelsCol[] = $chaveCol;
for ($val = 0; $val < count($arrValues); $val++) { //campos de valores
$chaveVal = $chaveRow."#".$chaveCol."#".$arrValues[$val];
$valor = $arrDados[$i][$arrValues[$val]];
if (!array_key_exists($chaveVal, $pivot))
$pivot[$chaveVal] = $valor;
else
$pivot[$chaveVal] += $valor;
}
}
ClassificaArrayPelaChave($pivot);
// if ($_SESSION['SGUSUARIO'] == 'ADMIN')
// echo TempoExecucaoFinaliza($inicio);
// $inicio = TempoExecucaoInicia();
// print_r($pivot);
$pivotSubtotal = array();
foreach ($pivot as $key => $value) {
$keys = split('#', $key);
$arrRowsSub = split("|", $keys[0]);
$chaveRow = '';
for ($lin = -1; $lin < count($arrRowsSub); $lin++) { //linhas - começa em -1 para na primeira execução pegar vazio (total geral)
if ($chaveRow)
$chaveRow .= "|";
$chaveRow .= $arrRowsSub[$lin]; //sumariza para cada campo de linha
$arrColsSub = split('|', $keys[1]);
$chaveCol = '';
for ($col = -1; $col < count($arrColsSub); $col++) { //colunas
if ($chaveCol)
$chaveCol .= "|";
$chaveCol .= $arrColsSub[$col]; //sumariza para cada campo de coluna
$chaveVal = $chaveRow."#".$chaveCol."#".$keys[2];
if (!array_key_exists($chaveVal, $pivotSubtotal))
$pivotSubtotal[$chaveVal] = $value;
else
$pivotSubtotal[$chaveVal] += $value;
}
}
}
ClassificaArrayPelaChave($pivotSubtotal);
// print_r($pivotSubtotal);
ClassificaArrayPeloValor($arrLabelsCol);
ClassificaArrayPeloValor($arrLabelsRow);
// print_r($arrLabelsCol);
// if ($_SESSION['SGUSUARIO'] == 'ADMIN')
// echo TempoExecucaoFinaliza($inicio);
// $inicio = TempoExecucaoInicia();
$table = "

\n";
$htmlCols = array();
$arrChaveColAnt = array();
for ($i = 0; $i < count($arrLabelsCol); $i++) { //monta as colunas de título
$cols = split("|", $arrLabelsCol[$i]);
$chaveCol = "";
$arrColspan = array(); //guarda qtas colunas devem ser expandidas
for ($col = 0; $col < count($cols); $col++) {
if ($chaveCol)
$chaveCol .= "|";
if (!array_key_exists($col, $arrColspan))
$arrColspan[$col] = 1;
if (count($arrValues) > 1)
if (!array_key_exists(count($cols), $htmlCols))
$htmlCols[count($cols)] = "\n\n";
$chaveCol .= $cols[$col];
$colspan = count($arrValues);

        if (!array_key_exists($col, $htmlCols))
            $htmlCols[$col] = "<tr>\n<td class='pvtColLabel'>Linha</td>\n";
        if ($arrChaveColAnt[$col] != $chaveCol and $arrChaveColAnt[$col] and $col < count($cols)-1) { //primeira ocorrência no grupo e coloca o expand collapse sem ser o último nivel
            $link = "<a href='javascript:ExpandCollapse(\"$arrChaveColAnt[$col]\", \"C\")'>(-)</a>";
            $htmlCols[$col] .= "<td class='pvtColLabel' colspan='$colspan' rowspan='".(count($cols)-$col)."' title='{$arrChaveColAnt[$col]}'>$link Total<br>{$arrChaveColAnt[$col]}</td>";
            if (count($arrValues) > 1) { //precisa criar mais uma linha para os valores
                for ($val = 0; $val < count($arrValues); $val++)
                    $htmlCols[count($cols)] .= "<td class='pvtColLabel'>".$arrValues[$val]."</td>\n";
            }
        }
        else {
            $arrColspan[$col]++; //não é total, então seguiu no mesmo nível
        }
        $htmlCols[$col] .= "<td class='pvtColLabel' colspan='".$colspan."' title='$chaveCol'>{$cols[$col]}</td>\n";
        //ajustar para não fechar a th acima e depois q tiver o colspan total atribuir nela e finalizar
        $arrChaveColAnt[$col] = $chaveCol;
        if (count($arrValues) > 1 and ($col == count($cols)-1)) { //precisa criar mais uma linha para os valores
            for ($val = 0; $val < count($arrValues); $val++) {
                $htmlCols[count($cols)] .= "<td class='pvtColLabel' title='$chaveCol|{$arrValues[$val]}'>{$arrValues[$val]}</td>\n";
            }
        }
        if ($i == count($arrLabelsCol)-1) { //ultima linha
            if ($col < count($cols)-1) { //só cria se não for o ultimo nivel
                $link = "<a href='javascript:ExpandCollapse(\"{$arrChaveColAnt[$col]}\", \"C\")'>(-)</a>";
                $htmlCols[$col] .= "<td class='pvtColLabel' colspan='$colspan' rowspan='".(count($cols)-$col)."' title='{$arrChaveColAnt[$col]}'>$link Total<br>{$arrChaveColAnt[$col]}</td>";
                if (count($arrValues) > 1) { //precisa criar mais uma linha para os valores
                    for ($val = 0; $val < count($arrValues); $val++) {
                        $htmlCols[count($cols)] .= "<td class='pvtColLabel'>".$arrValues[$val]."</td>\n";
                        $htmlCols[$col] .= "<td class='pvtColLabel' rowspan='".(count($cols)+(count($arrValues) > 1 ? 1 : 0))."'>Total<br>{$arrValues[$val]}</td>"; //total geral coluna
                    }
                }
                else
                    $htmlCols[$col] .= "<td class='pvtColLabel' rowspan='".(count($cols)+(count($arrValues) > 1 ? 1 : 0))."'>Total</td>"; //total geral coluna
            }
            $htmlCols[$col] .= "</tr>\n";
            if (count($arrValues) > 1 and ($col == count($cols)-1))
                $htmlCols[count($cols)] .= "</tr>\n";
        }
    }
}

// if ($_SESSION['SGUSUARIO'] == 'ADMIN')
// echo TempoExecucaoFinaliza($inicio);
// $inicio = TempoExecucaoInicia();
$htmlRows = "";
// print_r($arrLabelsRow);
// print_r($pivot);
$arrChaveRowAnt = array();
for ($i = 0; $i <= count($arrLabelsRow); $i++) { //monta as linhas e valores
$rows = split("|", $arrLabelsRow[$i]);
$chaveRow = "";
for ($row = 0; $row < count($rows); $row++) {
if ($chaveRow)
$chaveRow .= "|";
$chaveRow .= $rows[$row];
if ($row == (count($rows) -1)) { //ultimo nivel
$class = '';
$expcol = "";
}
else {
$class = 'color';
$expcol = "(-)";
}
if ($arrChaveRowAnt[$row] == $chaveRow) //não repete se era o mesmo nível do anterior
continue;
$arrChaveRowAnt[$row] = $chaveRow;
$htmlRows .= "

\n";
$htmlRows .= "\n";
$arrChaveColAnt = array();
for ($j = 0; $j <= count($arrLabelsCol); $j++) { //valores - vai uma coluna a mais do que existiria para fazer o total
$cols = split("|", $arrLabelsCol[$j]);
$chaveCol = "";
$arrLastCol = array();
$lastCol = false;
for ($col = 0; $col < count($cols); $col++) {
if ($chaveCol)
$chaveCol .= "|";
$chaveCol .= $cols[$col];
for ($val = 0; $val < count($arrValues); $val++) {
if (($arrChaveColAnt[$col] != $chaveCol and $arrChaveColAnt[$col])) { //coluna de subtotal
$lastCol = $col == count($cols)-1 and count($arrValues) > 1; //quando existe mais de um campo de valor não é possível mostrar diretamente o subtotal do campo e o valor final, pois ficaria o total do primeiro campo de valor no subtotal do segundo campo de valor
$chaveVal = $chaveRow."#".$arrChaveColAnt[$col]."#".$arrValues[$val];
$valor = FormataNumeroMoeda($pivotSubtotal[$chaveVal]);
$htmlRows .= "";
}
if ($col == count($cols)-1) { //só escreve no último nível
$chaveVal = $chaveRow."#".$arrLabelsCol[$j]."#".$arrValues[$val];
if (array_key_exists($chaveVal, $pivot))
$valor = FormataNumeroMoeda($pivot[$chaveVal]);
else
if (array_key_exists($chaveVal, $pivotSubtotal))
$valor = FormataNumeroMoeda($pivotSubtotal[$chaveVal]);
else
$valor = "";
$td = "";
if ($lastCol)
$arrLastCol[$val] = $td;
else
$htmlRows .= $td;
}
else {
if ($val == count($arrValues)-1) //só atualiza a chave depois de montar todos os campos de valor
$arrChaveColAnt[$col] = $chaveCol;
}
}
}
}
if ($lastCol)
for ($val = 0; $val < count($arrLastCol); $val++) {
$htmlRows .= $arrLastCol[$val];
}
$htmlRows .= "\n";
}
}

echo $table;
for ($i = 0; $i < count($htmlCols); $i++)
    echo $htmlCols[$i];
echo $htmlRows;
echo "</table>\n";
if ($_SESSION['SGUSUARIO'] == 'ADMIN')
    echo TempoExecucaoFinaliza($inicio);

}

Linha
$expcol".coalesce($rows[$row], 'TOTAL')."	$valor (+)	$valor (+)

[cihadturhan]

I can't use this code properly. Could you paste your code on viper-7 and click "paste" button then give me the link of result page?

[rafaels86]

I think that is it: http://viper-7.com/0tyjmj

[ken-muturi]

anyone who was able to get through this?

[cihadturhan]

Well, I said I wanted to but I've been so busy with the projects at work. It looks like I can't start for now or a couple of weeks.

[santoshdhanshetti]

Hi Guys, has anyone able to solve this one?

[cihadturhan]

@santoshdhanshetti I tried but had to stop as I'm busy with some other projects.

[hajovonta]

Any progress?

[corredor28]

I decided to put subtotals between parenthesis on each row/col header... not much of an elegant solution (and different from the target of this discussion) but it may help someone:

1 - Put ids and classes on needed headers. I did it by changing the code where the class pvtColLabel is applied in pivot.coffee (of course it can be done outside... I guess):

                th.className = "pvtColLabel"
                # Identify cells that need subtotals
                if parseInt(j) < colAttrs.length-1
                    # Put class and id to be able to iterate through them then
                    th.className += " colLab"
                    th.id = "colLab_" + j + "_" + i
                th.setAttribute("colspan", x)

• And the same goes for rows:

                th.className = "pvtRowLabel"
                # Identify cells that need subtotals
                if parseInt(j) < rowAttrs.length-1
                    # Put class and id to be able to iterate through them then
                    th.className += " rowLab"
                    th.id = "rowLab_" + j + "_" + i
                th.setAttribute("rowspan", x)

2 - Then, since I wanted to show the totals not only in pivotUI but also in pivot mode, I had to add this to the pivotTableRenderer = (pivotData, opts) function (just before the end):

    # Place subtotals where needed
    placeSubtotals = (headerClass) ->
        headerType = (if headerClass.indexOf("row") > -1 then "row" else "col")
        # In case there is more than one pivot table in page, it's useful to put them in divs with unique ids :)
        #containerId = pivotTable.closest("table").parent().attr("id")
        container = $(result);
        classIds = container.find("th." + headerClass).map((i) ->
            @id
        )
        headerIds = classIds.get()
        if headerIds.length > 1
            classSpans = container.find("th." + headerClass).map((i) ->
                if headerType is "row"
                    $(this).attr "rowspan"
                else
                    $(this).attr "colspan"
            )
            headerSpans = classSpans.get()
            # Order headerIds for rows, to make subtotals calculation easier (could be made for columns too)
            if headerType is "row"
                orderedHeaderIds = []
                orderedHeaderSpans = []
                j = 0
                while j < headerIds.length
                    index = headerIds[j].split("_")[1]
                    i = j
                    while i < headerIds.length
                        # If it's not in the ordered list, find out if it must go next
                        if orderedHeaderIds.indexOf(headerIds[i]) is -1 and headerIds[i].split("_")[1] is index
                            orderedHeaderIds.push headerIds[i]
                            orderedHeaderSpans.push headerSpans[i]
                        # Save some loops when done
                        break  if headerIds.length is orderedHeaderIds
                        i++
                    # Save some loops when done
                    break  if headerIds.length is orderedHeaderIds
                    j++
                # Copy results
                headerIds = orderedHeaderIds
                headerSpans = orderedHeaderSpans
            # Iterate through every header putting subtotals
            lastSpan = 0
            oldIndex = 0
            j = 0

            while j < headerIds.length
                # Reset indexes on every header row/col change
                index = headerIds[j].split("_")[1]
                unless oldIndex is index
                    lastSpan = 0
                    oldIndex = index
                # Add totals for every header
                acum = 0
                limit = lastSpan + parseInt(headerSpans[j])
                i = lastSpan
                while i < limit
                    acum += container.find("[data-for=" + headerType + i + "]").data("value")
                    i++
                oldHtml = container.find("#" + headerClass + "_" + oldIndex + "_" + lastSpan).html()
                container.find("#" + headerClass + "_" + oldIndex + "_" + lastSpan).html oldHtml + " (" + acum + ")"
                lastSpan += parseInt(headerSpans[j])
                j++
        return

    placeSubtotals "colLab"
    placeSubtotals "rowLab"

    return result

[cbetech]

Hey, anyone who was able to do this in another row?

[anandakumarramaswamy]

hi nicolas great work done by you..:)
i need subtotals concept in that pivot table..is there any progress in it..?if it means please let me know nicolas ..:)

[nicolaskruchten]

I'm not actively working on this feature, no.

[onetwothere]

anyone manage to do this? :)

[nagarajanchinnasamy]

@nicolaskruchten, Thanks a lot for such a useful library.

I have added the support for roll-up summary (sub-total rows) using aggregators and renderer concepts . Its available at SubTotals branch of the fork that I have made

Would like to receive your comments/feedback from friends here before I send the pull request. I plan to add collapse-and-expand feature on top of this. Thanks.

[anandakumarramaswamy]

goooood work Nagaraj :)

[vivianjayakaran]

@nagarajanchinnasamy do you have a sample of how to use your SubTotals branch. Are there any settings to turn it on or off?

[nagarajanchinnasamy]

@vivianjayakaran there are no settings as of now... its directly built into the code. No new code or modification to existing code is needed. You can try any of the provided pivot() or pivotUI() examples... you will get to see them with new summary (sub-total) rows.

To try, please download the code from the link below and run the examples locally under a web-server:

here -> SubTotals branch of the fork

[nicolaskruchten]

@nagarajanchinnasamy thanks for this contribution!

My feedback is as follows:

1. I don't wish to add subtotals to the main Table renderer, so I would ask that any PR you submit contains a new renderer in a new file.
2. I understand that enabling subtotals requires some changes to the PivotData class, so these are the changes that are of greatest interest to me. I will need to take some time to carefully read through the changes you made. I notice that you've modified the most performance-sensitive part of the PivotData class (this bit with the "tight loop" comment) so this will require a bit of performance-testing on very large datasets as well.

[nagarajanchinnasamy]

@nicolaskruchten Thanks for your feedback.

1. I've made a new commit to the SubTotals branch with subtotal_renderers.coffee added. Name of the renderers is: subtotal_renderers.
2. Added a new example subtotal.html to demo the subtotal_renderers. An example titled pivotUI() with subtotal rows is provided in the list of examples under Canadian Parliament 2012 Dataset (CSV or JSON)

Pls check and let me know your feedback

[nagarajanchinnasamy]

@nicolaskruchten , friends... I have further enhanced subtotal_renderers with both row-wise and column-wise subtotals. Also added Collapse/Expand functionality for rows. Collapse/Expand for column subtotals is under construction....

Please check Subtotals branch. and provide your feedback. Steps to use:

1. Download pivottable-Subtotals.zip
2. Extract and place pivottable and its subfolders under document root of your local webserver
3. Access the demo using url: http://localhost/pivottable/examples/subtotal.html

Sample screenshot:

[ahmad003]

@nagarajanchinnasamy There is an error in computation of "Sum as Fraction of Rows" and "Count as Fraction of Rows". Do you know how to correct this?

[ahmad003]

I think the "Totals" of Subtotals don't include in computation element in aggregator "fraction Of", cause i check in "subtotal.html" the subtotal "Female" don't divided with itself but with subtotal of "NDP in Quebec", etc... etc... Hope this will pinpoint the problem... Anyway thanks for this great work...

[nagarajanchinnasamy]

@ahmad003 Thanks for the feedback... I will look into this issue and get back. Thanks again.

[nagarajanchinnasamy]

@ahmad003 I do not see any issue with the aggregators. For e.g., in the picture shown below, after selecting Sum as Fraction of Total and Age, the overall total is 15603 and for Females in Age Bin 60, the subtotal value is 883. Converting this to percentage ((883/15603)*100)) yields 5.7% as shown rightly in the pivot. Or, am I missing something? Please confirm.

[ahmad003]

Thank you @nagarajanchinnasamy for reply my question... And, yes, for Sum as Fraction of Total there is no error, but if you use Sum as Fraction of Rows, you will see there is an error like this...

[ahmad003]

And if you continue to make subtotals for column, most likely there will be an error in Sum as Fraction of Columns... Thank you once again for fast reply... I hope this will help you in developing this project...

[ahmad003]

If you see the image bellow, the result of Sum as Fraction of Rows for subtotal Alberta is divided by total of Conservative in Alberta... *(240/180)100 = 133.3... When i check the result of Sum as Fraction of Rows for Female, it divided by total of NDP in Quebec... *(3,210/920)100 = 348.9...

[nagarajanchinnasamy]

@ahmad003 , Yes... I see the issue now. Let me check and get back to you. Thanks for clarification.

[nagarajanchinnasamy]

@ahmad003 the issue is fixed now in both as Fraction of Rows and as Fraction of Columns

Please check-out the latest version from here...

Thanks again for your feedback.

[ahmad003]

Thank you once again @nagarajanchinnasamy... You're Awesome... For my reference, is there a way to make default expand/collapse to expand,.. And for future development, I think it's necessary to make tool to expand/collapse all rows, to hide/show subtotals for row and column... when your data so many, you don't want to click one by one to expand/collapse all rows, isn't it...

Anyway Great Work !!

[nagarajanchinnasamy]

@ahmad003 Thanks :-)

Yes, I plan to add rendererOptions to control expansion/collapsing of pivot when it is drawn initially. I am still thinking what the option could be. Of course, expandAllRows or expandAllColumns are easy ones. Just wondering if more finer control can be given or not!!!

I have enabled issues tab in the forked repository here in case if you want to discuss subtotal_renderers specific issues.

[nagarajanchinnasamy]

@ahmad003 I have added an option collapseRowsAt as a rendererOptions that takes one of the row attributes as it's value and collapses rows at that level.E.g.:

rendererOptions: {
    collpseRowsAt: "Province"
}

Default behavior is to expand all rows.

Please check subtotal.html example at the forked repository here...

Thanks for your feedback.

[nagarajanchinnasamy]

@ahmad003 in addition to the collapseRowsAt option, I will also expose a function to be invoked at runtime to expand/collapse rows/columns. I would not want to change the standard user interface to provide the tool to expand/collapse rows/columns. Instead, the application can have its own UI, when clicked, can call the exposed function.

I will also see if I can provide a function to show/hide subtotals

[ahmad003]

@nagarajanchinnasamy, its awesome.
I think you can make new aggregation function like % of Parent Row Total and % of Parent Column Total in excel (option Show Value as), that you can make subtotals output...

[nagarajanchinnasamy]

@ahmad003 Thanks.

% of Parent Row/Column is a good suggestion. Willl add and let you know. Thanks again.

[ahmad003]

@nagarajanchinnasamy, can subtotals generate without UI?

[nagarajanchinnasamy]

@ahmad003 sorry I did not understand what you mean by:

can subtotals generate without UI?

[ahmad003]

@nagarajanchinnasamy, Sorry..

I mean to change this code in example/subtotal.html in line 38

        $("#output").pivotUI(mps, {

to

        $("#output").pivot(mps, {

but when i try, the subtotals didn't generate..

[nagarajanchinnasamy]

@ahmad003 got it. I will check. Thanks for the feedback.

[alejcas]

Are you gona PR this on the nicolaskruchten repo?

[nicolaskruchten]

@nagarajanchinnasamy I've put some thought into how we can modify this library to allow your fine work to be compatible. My proposal is that we accept a new parameter which would allow the developer to override the PivotData class altogether, instead of just the processData function, so that someone could actually choose to store the data differently as well as process it differently. If I made this change, would you consider modifying your code to follow?

[nagarajanchinnasamy]

Definitely @nicolaskruchten.... My pleasure 👍 Please confirm once this change is done. I will modify the code accordingly and submit a PR.

[nagarajanchinnasamy]

@nicolaskruchten, as u had suggested, I've created SubtotalPivotData class that extends PivotData. Overridden ProcessRecord function. Introduced a link in index.html to the example subtotal.html.

Submitted PR #506. Please review and let me know of your feedback.

Also, the special character used for displaying triangleLink does not seem to appear correctly on my browser (chrome). Screenshot:

[nagarajanchinnasamy]

@nicolaskruchten , @ahmad003 , @anandakumarramaswamy , @vivianjayakaran and friends, happy to inform you that Subtotal.js is available for download as an NPM module or Bower component.

Subtotal.js is hosted in GitHub as pivottable-subtotal-renderer

@nicolaskruchten , In setting up Subtotal.js, I have shamelessly copied the structure, style and some portions of text and code as is from Pivottable.js repository. Hope you don't mind.

Thanks again to all of you for your feedback and support 👍

[nicolaskruchten]

All right, I think we can finally consider this issue resolved! The official answer in the FAQ is now "use Subtotal.js" :)