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.
The Subtotal function is unrelated to the formatting of the cell.
The person who compiles the spreadsheet can choose whatever formatting they like.
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 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.
It is much better if the question is complete. Al
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, 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?
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, 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?
Reported bug. https://bugs.documentfoundation.org/show_bug.cgi?id=133812, please add there your comment.
Not necessary to add a comment to that bug, it would just be a me-too.
Hi Igorlius,
oeps… sorry never worked with macro’s in Libre Office. I am sure I need to copy this and paste it in somewhere in the macro menu. I tried several options, but they do not work.
@roerdomp, To add the macro goto “Tools” > “Macros” > “Edit Macros...” and paste the entire text into the textarea on thre right, in the new window that opens. Then “File” > “Save” and “File” > “Close”
Do you also want to know how to add the macro to a shortcut ?
I updated the macro to make it less ugly, it now also adds the title for the Subtotals and underlines the results. Is the “Grand total also required for you?” (see 2nd image in the answer)
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
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.
Thanks @Eike for having solved the bug.
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.
Select an area
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
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
tmp = tmp & "+SUM(" & a_forms(i) & ")"
next
cname = end_col & (cint(a_ins(0))+1)
oCell = oSheet.getCellRangeByName(cname)
oCell.setFormula(tmp)
oCell.CharWeight = 150
oCell.CharUnderline = 1 rem 0-18 have meaning
for i = lbound(a_ins) to ubound(a_ins) -1
oSheet.Rows.insertByIndex(a_ins(i),1)
rem bla = Split(a_forms(i), ":")
cname = start_col & (cint(a_ins(i))+1)
oCell = oSheet.getCellRangeByName(cname)
oCell.String = a_groups(i) & " Result"
oCell.CharUnderline = 1 rem 0-18 have meaning
oCell.CharWeight = 150
cname = end_col & (cint(a_ins(i))+1)
oCell = oSheet.getCellRangeByName(cname)
oCell.setFormula("=SUM(" & a_forms(i) & ")")
oCell.CharWeight = 150
oCell.CharUnderline = 1 rem 0-18 have meaning
next
end sub
Hope that helps.
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.
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.