Ask Your Question
1

Could you please restore the sub total function?

asked 2020-09-27 10:25:00 +0100

roerdomp gravatar image

updated 2020-09-27 10:25:51 +0100

Hi,

I love LibreOffice. But for a couple of years I am forced not to upgrade to the latest version. I use Calc a lot and it misses since a couple of years now, the important sub total function of getting the subtotal results in bold/underscore/italics. This means a large worksheet with subtotals become unreadable. Is this acknowledged as a bug ??? Is it done on purpose ?? I would love to upgrade but I can not work with a non functional sub total view.

edit retag flag offensive close merge delete

Comments

The Subtotal function is unrelated to the formatting of the cell.

The person who compiles the spreadsheet can choose whatever formatting they like.

Earnest Al gravatar imageEarnest Al ( 2020-09-27 10:54:30 +0100 )edit

Hi Earnest,

This is not what I am referring to. I know how to format a cell. I you have a couple of hundreds of sub total results in one sheet, this not very practical to format every subtotal by hand. This was an automated function ... like on the pics of this explanation of the subtotal function, like in the example half way this page:link text

So in earlier versions the subtotal results were shown in bold/underscore/italics. In the current version you get nothing. Try it please or I can send you an example.

roerdomp gravatar imageroerdomp ( 2020-09-27 11:08:54 +0100 )edit

@roerdomp is referring to the Data | Subtotals tool; in AOO the results cells created by the tool have a style Result which has a bold, italic font with single underline. My LO 6.4.6.2 assigns no style to the result cells.

robleyd gravatar imagerobleyd ( 2020-09-27 11:36:00 +0100 )edit

It is much better if the question is complete. Al

Earnest Al gravatar imageEarnest Al ( 2020-09-27 12:18:34 +0100 )edit

Sorry if I was not clear enough. @robleyd: correct, this was the situation, but not anymore. The current lack of any style result makes larger sheets with a lot of subtotal results unreadable.

roerdomp gravatar imageroerdomp ( 2020-09-27 12:40:00 +0100 )edit

@roerdomp, i think i could write you a (shortcut) macro that could provide you with a similar result as the old subtotal function. Would that be of interest to you?

igorlius gravatar imageigorlius ( 2020-09-27 15:26:09 +0100 )edit

Any solution is welcome Igorlius! For work I am now still on Ubuntu 16.04 which still uses an older version of LibreOffice, which still supports the style results in subtotals. But I need to upgrade soon. Of course I hope this issue can be fixed in a regular LibreOffice update, so there is no need for a work around.

roerdomp gravatar imageroerdomp ( 2020-09-27 15:40:15 +0100 )edit

@roerdomp, I'll have to ask some further question, The macro would not create a view / overlay, but actually add rows (for the subtotals) so removing the results would not be as simple. Would that be a problem?

igorlius gravatar imageigorlius ( 2020-09-27 15:52:49 +0100 )edit

Reported bug. https://bugs.documentfoundation.org/s..., please add there your comment.

m.a.riosv gravatar imagem.a.riosv ( 2020-09-27 16:11:46 +0100 )edit

@igorlius I don't think this would be a problem. The subtotal function adds a row as well.

roerdomp gravatar imageroerdomp ( 2020-09-27 16:38:29 +0100 )edit

2 Answers

Sort by » oldest newest most voted
1

answered 2020-09-27 16:13:29 +0100

erAck gravatar image

updated 2020-09-27 16:34:04 +0100

This appears to be the fallout of some UX style rework that removed the predefined Result and Result2 styles. If you observe carefully, when travelling the cells the Stylist does not display any style name for the inserted result cells, not even Default Cell Style. The Subtotal tool tried to assign a style but the style is not present so display falls back to the parent or default style. This is clearly a bug and was recently reported as such, tdf#133812.

You can work around by creating a cell style inherited from the Default Cell Style and name it Result before using the Subtotal tool. To have it available in new documents add it to the default Calc document template.

If you feel confident and know what you're doing it would even be possible to make it available for all users by editing the installation's share/calc/styles.xml

edit flag offensive delete link more

Comments

Thank you! I will try that as well. Glad to read this is actually regarded as a bug! I hope this means it will be resolved i a future update. I will try the work around as well.

roerdomp gravatar imageroerdomp ( 2020-09-27 16:42:08 +0100 )edit

Thanks @Eike for having solved the bug.

m.a.riosv gravatar imagem.a.riosv ( 2020-09-28 17:28:46 +0100 )edit
1

answered 2020-09-27 22:17:53 +0100

updated 2020-09-28 17:55:41 +0100

Hi,

as promised, here is the macro . it is by fare the most ugly and least efficient thing i have written (as a macro) but it works. I'll come back tomorrow and clean it up a bit more. Just wanted to get it out there.

  1. Select an area
    image description

  2. Run the macro (for example via a shortcut key) image description

Some Notes:
- The first row is ignored (since it is table header)
- The left most column of the selection defines the groups
- The right most column will be summed for the subtotals
- The sumtotal values are generated by inserting a Formula, so changing the values will also update the subtotals
- The subtotals currently only get a bold text style, if you want something else, request it in the comments.

And finally here the macro (addSubTotals) you'll have to insert it into tools -> macros -> edit macros. and bind it to for example a shortcut

rem helper
function getRowIdx(cellname as string) 
    tmp = ""
    for i = 1 to len(cellname)
        c = mid(cellname, i, 1) 
        select case c
            case "0","1","2","3","4","5","6","7","8","9"
                tmp = tmp & c
        end select
    next        

    rem Print tmp
    getRowIdx = cint(tmp)
end function

rem helper
function getColIdx(cellname as string) 
    tmp = ""
    for i = 1 to len(cellname)
        c = mid(cellname, i, 1) 
        select case c
            case "0","1","2","3","4","5","6","7","8","9"
            case else
                tmp = tmp & c
        end select
    next        

    rem Print tmp
    getColIdx = tmp
end function


sub addSubTotals

    oDoc = ThisComponent
    oSelRange = oDoc.getCurrentSelection()
    oSheet = oDoc.getCurrentController().ActiveSheet
    oConv = oDoc.createInstance("com.sun.star.table.CellAddressConversion")

    oConv.Address =  oSelRange.getCellByPosition( 0, 0 ).getCellAddress()
    row_offset = getRowIdx(oConv.UserInterfaceRepresentation)
    start_col = getColIdx(oConv.UserInterfaceRepresentation)

    oConv.Address =  oSelRange.getCellByPosition( oSelRange.Columns.getCount() - 1, 0 ).getCellAddress()
    end_col = getColIdx(oConv.UserInterfaceRepresentation)
    rem Print start_col & "," & end_col

    start=""
    group=""

    inserts = ""
    formulas=""
    groups=""

    rem iterate through rows
    for i = 1 To oSelRange.Rows.getCount() -1
            oCell = oSelRange.getCellByPosition( 0, i ) 
            if ( oCell.String <> "" ) then
                    if ( group <> "") then
                        rem get last cell of group in last column
                        oCell2 = oSelRange.getCellByPosition( oSelRange.Columns.getCount() - 1, i - 1 )
                        oConv.Address = oCell2.getCellAddress()
                        formulas = start & ":"  & oConv.UserInterfaceRepresentation & ","    & formulas
                        inserts = getRowIdx(oConv.UserInterfaceRepresentation) & "," & inserts 
                    endif
                    rem save group
                    group=oCell.String
                    groups= group & "," & groups
                    rem get first cell of group in last column
                    oCell2 = oSelRange.getCellByPosition( oSelRange.Columns.getCount() - 1, i ) 
                    oConv.Address = oCell2.getCellAddress()
                    start = oConv.UserInterfaceRepresentation
            endif       
    next

    if ( group <> "") then
        oCell2 = oSelRange.getCellByPosition( oSelRange.Columns.getCount() - 1, i-1 )
        oConv.Address = oCell2.getCellAddress()
        formulas = start & ":"  & oConv.UserInterfaceRepresentation & "," & formulas
        inserts = getRowIdx(oConv.UserInterfaceRepresentation) & "," & inserts 
    endif

    a_ins = Split(inserts,",")
    a_forms = Split(formulas,",")
    a_groups  = Split(groups,",")

    rem TODO add grand total 
    oSheet.Rows.insertByIndex(a_ins(0),1)

    cname = start_col &   (cint(a_ins(0))+1)
    oCell = oSheet.getCellRangeByName(cname)
    oCell.String =  "Grand Total" 
    oCell.CharUnderline = 1 rem 0-18 have meaning
    oCell.CharWeight = 150 

    tmp="=0"

    for i = lbound(a_ins) to ubound(a_ins) -1 ...
(more)
edit flag offensive delete link more

Comments

Thanks for all your efforts in this! I did not expect this kind of support ;-) .

I was able to create the macro called AddSubTotals. And yes, when I run it, this creates something, but it does not work like I need it to work. The final salary costs I would now report to my boss would at least double for our organisation ;-) That is because the macro disregards how I want the results to be grouped and it doubles the total result. Hard to explain. What I am producing is a sheet which shows he salary costs of about 200 employees, per month this year, and total grouped by name and the same sheet grouped by project they are working on. The subtotal function has no problem with doing that. But the macro adds a bold and underlines subtotal.

roerdomp gravatar imageroerdomp ( 2020-09-28 16:12:55 +0100 )edit

Good that you got the macro working. I'll add the correct grand total calculation shortly.

[update] Now the correct Grand Total is added. If there is anything else you need, just ask.

igorlius gravatar imageigorlius ( 2020-09-28 17:01:02 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-09-27 10:25:00 +0100

Seen: 118 times

Last updated: Sep 28