Could you please restore the sub total function?

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 close merge delete

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

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

( 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.

( 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.

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

It is much better if the question is complete. 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.

( 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?

( 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.

( 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?

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

( 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.

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

Sort by » oldest newest most voted

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

more

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.

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

Thanks @Eike for having solved the bug.

( 2020-09-28 17:28:46 +0100 )edit

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

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

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

oDoc = ThisComponent
oSelRange = oDoc.getCurrentSelection()
oSheet = oDoc.getCurrentController().ActiveSheet

row_offset = getRowIdx(oConv.UserInterfaceRepresentation)
start_col = getColIdx(oConv.UserInterfaceRepresentation)

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 )
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 )
start = oConv.UserInterfaceRepresentation
endif
next

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

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

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

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.

( 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.

( 2020-09-28 17:01:02 +0100 )edit