Table grouping and sorting

paulo commented on Sep 30, 2019

Hi, Please I need some help on how to display a table grouped by status. Status is a string, so I have a field called row_number to be able to display the status grouped and sorted in the order that I want. It is also showing the totals top and bottom and repeating the status number and name. I can't figure out how to do this, so for now I created one column result called 'statusNandName to show, but this would list 1-Lead 13-No go – Non Refundable Deposit Received 2-Proposal instead of showing 1, 2, 13. I hope I explained this properly. row_number is the number that defines the order of the status and name is the column that defines the status name.

Here is the code: "grouping" => array(

        "statusNandName" => array(
                        "calculate" => array(
                            "{sumComm}" => array("sum", "comm"),
                            "{sumEst}" => array("sum", "estimated_sale"),
                            "{sumAgent}" => array("sum", "agent"),
                            "{sumClosingCommAdj}" => array("sum", "ClosingCommAdj"),
                            "{sumEarnedComm}" => array("sum", "EarnedComm"),
                          ),
                        "sorting"=>array(
                            "row_number"=>"desc",
                            "startDate"=>"asc"
                        ),
                        "top" => "<b>{statusNandName}</b>",
                        "bottom" => "<td><b>Total {statusNandName}</b></td>

thank you very much!

KoolReport commented on Oct 1, 2019

Hi, I have not fully understood your case yet. Btw, the "sorting" property is at the top level of settings, not inside the grouping:

Table::create(array(
    ...
    "sorting"=>array(
        "orderDate"=>"desc",
        "customerName"=>"asc"
    )
));

If you are using <td> inside the bottom, make sure that the number of <td> is the same with number of columns in table to avoid the span problem.

paulo commented on Oct 1, 2019

Thank you. I've moved this out. Challenge: I have two fields 1. StatusOrder 2. StatusName. How can I order the list and at the same time group by name ? I want my report to show grouped by status name (and/or StatusOrder and StatusName). Here is the example of the status table: StautsOrder StatusName 1 Lead 2 Proposal 3 Proposal Sent 13 No go – Non Refundable Deposit Received

If in the grouping I only use StatusName, it will show out of order. If I only use StatusOrder, it won't show the name. As a workaround, I combined the Order and the nsame as statusNandName, however, when I do that, it considers everything as a string (as it should) but places the #13 after #1 and then #2, #3.

So, I am trying to grouping by StatusOrder, but display the order and name. Maybe, if there is a way that at the top and bottom I can concatenate both, it might solve my issue. Currently, I have: "top" => "{statusNandName}", bottom" => "<td>Total {statusNandName}</td>

I tried: "top" => "{statusOrder} {StatusName}", bottom" => "<td>Total {statusOrder} {StatusName}</td>

and it didn't work. thanks for the td note.I was actually missing one. Fixed now. :)

thank you very much! Paulo

KoolReport's Forum

Table grouping and sorting #1108

Build Your Excellent Data Report