[Solved] MeanValue & ErrorBarStyle from ChartStatistics

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
flyer48
Posts: 14
Joined: Fri Jun 18, 2021 5:19 pm

[Solved] MeanValue & ErrorBarStyle from ChartStatistics

Post by flyer48 »

I am trying to use the MeanValue and ErrorBarStyle properties of ChartStatistics, and with my current formatting

Code: Select all

Dim Doc As Object
Dim Charts As Object
Dim Chart as Object
Dim Rect As New com.sun.star.awt.Rectangle
Dim RangeAddress(0) As New com.sun.star.table.CellRangeAddress

Doc = StarDesktop.CurrentComponent
Charts = Doc.Sheets(0).Charts

Rect.X = 8000
Rect.Y = 1000
Rect.Width = 10000
Rect.Height = 7000
RangeAddress(0).Sheet = 0
RangeAddress(0).StartColumn = 0
RangeAddress(0).StartRow = 0
RangeAddress(0).EndColumn = 8
RangeAddress(0).EndRow = 1

Charts.addNewByName("MyChart", Rect, RangeAddress(), True, True)
Chart = Charts.getByName("MyChart").embeddedObject
Chart.Diagram = Chart.createInstance("com.sun.star.chart.LineDiagram")
Chart.Diagram.Wall.FillColor = RGB(255,255,255)
Chart.Diagram.HasXAxisGrid = True
Chart.Diagram.HasYAxisGrid = True
Chart.Diagram.YAxis.Min = 0
Chart.Diagram.YAxis.Max = 100
Chart.Diagram.DataRowSource = com.sun.star.chart.ChartDataRowSource.ROWS
Chart.HasLegend = False

Chart.Diagram.HasXAxisTitle = True
Chart.diagram.XAxisTitle.String = "Tests"
Chart.Diagram.HasYAxisTitle = True
Chart.Diagram.YAxisTitle.String = "Result Value"

Chart.Title.String = "From Row 2"

Chart.ChartStatistics.MeanValue = True
Chart.ChartStatistics.ErrorBarStyle = 2
I get the error message "Property or Method not found: ChartStatistics".

When formatted as

Code: Select all

com.sun.star.chart.ChartStatistics.MeanValue = True
I get "Property or Method not found: MeanValue".

From reading the API and looking at the other properties that I have used that works I thought that one of these two would work. How do you format this?

Thanks in advance.
Last edited by flyer48 on Wed Jun 23, 2021 6:05 pm, edited 3 times in total.
OpenOffice 4.1.10 Windows 10
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Using MeanValue and ErrorBarStyle from ChartStatistics

Post by FJCC »

Try

Code: Select all

Chart.Diagram.MeanValue = TRUE
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
flyer48
Posts: 14
Joined: Fri Jun 18, 2021 5:19 pm

Re: Using MeanValue and ErrorBarStyle from ChartStatistics

Post by flyer48 »

The error messages have resolved and the MeanValue line is on the chart, however the error bars are not present on the chart. I have it currently formatted as

Code: Select all

Chart.Diagram.ErrorBarStyle = 2
because the API indicates that ErrorBarStyle uses a Long Integer, with 2 being the indicator for Standard Deviation. How can I format this so that the standard deviation bars ae visible?

Thanks in advance.
OpenOffice 4.1.10 Windows 10
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Using MeanValue and ErrorBarStyle from ChartStatistics

Post by FJCC »

Here is a macro that I recorded with the MRI extension that adds error bars around the mean to an xy chart.

Code: Select all

 oSheets = ThisComponent.getSheets()
  oObj1 = oSheets.getByName("Sheet3")
  oCharts = oObj1.getCharts()
  
  oObj2 = oCharts.getByIndex(0)
  oEmbeddedObject = oObj2.getEmbeddedObject()
  oDiagram = oEmbeddedObject.getDiagram()
  
  oDataErrorProperties = oDiagram.DataErrorProperties
  oDataErrorProperties.ErrorBarStyle = 2
The key is getting access to the DataErrorProperties property.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
flyer48
Posts: 14
Joined: Fri Jun 18, 2021 5:19 pm

Re: Using MeanValue and ErrorBarStyle from ChartStatistics

Post by flyer48 »

When I attempt to run your code it throws "runtime error Object Variable not set" on the last line. I then defined all of the elements as such

Code: Select all

Dim Sheets As Object
Dim Obj1 As Object
Dim Charts As Object
Dim Obj2 As Object
Dim EmbeddedObject As Object
Dim Diagram As Object
Dim DataErrorProperties As Object

Sheets = ThisComponent.getSheets()
  Obj1 = Sheets.getByName("CombinedSynthetics")
  Charts = Obj1.getCharts()
 
  Obj2 = Charts.getByName("Alpha")
  EmbeddedObject = Obj2.getEmbeddedObject()
  Diagram = EmbeddedObject.getDiagram()
 
  DataErrorProperties = Diagram.DataErrorProperties
  DataErrorProperties.ErrorBarStyle = 2
and I get the same error on

Code: Select all

Obj1 = Sheets.getByName("CombinedSynthetics")
.
OpenOffice 4.1.10 Windows 10
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Using MeanValue and ErrorBarStyle from ChartStatistics

Post by FJCC »

That error suggests that there is no sheet named CombinedSynthetics. Can you post an example document?
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
flyer48
Posts: 14
Joined: Fri Jun 18, 2021 5:19 pm

Re: Using MeanValue and ErrorBarStyle from ChartStatistics

Post by flyer48 »

Unfortunately, I cannot post an example document. I switched the code to attempt to call by number

Code: Select all

Dim Sheets As Object
Dim Obj1 As Object
Dim Charts As Object
Dim Obj2 As Object
Dim EmbeddedObject As Object
Dim Diagram As Object
Dim DataErrorProperties As Object

Sheets = ThisComponent.getSheets()
  Obj1 = Doc.Sheets(0)
  Charts = Obj1.getCharts()
 
  Obj2 = Charts.getByName("Alpha")
  EmbeddedObject = Obj2.getEmbeddedObject()
  Diagram = EmbeddedObject.getDiagram()
 
  DataErrorProperties = Diagram.DataErrorProperties
  DataErrorProperties.ErrorBarStyle = 2
and I got the same error.
OpenOffice 4.1.10 Windows 10
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Using MeanValue and ErrorBarStyle from ChartStatistics

Post by FJCC »

The variable Doc is not defined. Try

Code: Select all

Obj1 = Sheets.getByIndex(0)
If you document contains confidential information, delete everything you do not need for the chart and change the chart data to different numbers. Without an example document, it will be very difficult to provide help.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
flyer48
Posts: 14
Joined: Fri Jun 18, 2021 5:19 pm

Re: Using MeanValue and ErrorBarStyle from ChartStatistics

Post by flyer48 »

I have attached the example document. When I ran the macro with the updated code, it gave the same error on

Code: Select all

DataErrorProperties.ErrorBarStyle = 2
Attachments
Example Sheet.ods
(18.46 KiB) Downloaded 182 times
OpenOffice 4.1.10 Windows 10
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Using MeanValue and ErrorBarStyle from ChartStatistics

Post by FJCC »

This code works for me with your example file.

Code: Select all

Dim Sheets As Object
Dim Obj1 As Object
Dim Charts As Object
Dim Obj2 As Object
Dim EmbeddedObject As Object
Dim Diagram As Object
Dim DataErrorProperties As Object

Sheets = ThisComponent.getSheets()
  Obj1 = Sheets.getByIndex(0)
  Charts = Obj1.getCharts()
  Obj2 = Charts.getByName("Alpha")
  EmbeddedObject = Obj2.getEmbeddedObject()
  Diagram = EmbeddedObject.getDiagram()

  DataErrorProperties = Diagram.DataErrorProperties
  DataErrorProperties.ErrorBarStyle = 2
  DataErrorProperties.ShowNegativeError = TRUE
  DataErrorProperties.ShowPositiveError = TRUE
If you still get an error, try starting the macro from the spreadsheet using the menu Tools -> Macros -> Run Macro
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
flyer48
Posts: 14
Joined: Fri Jun 18, 2021 5:19 pm

Re: Using MeanValue and ErrorBarStyle from ChartStatistics

Post by flyer48 »

On both the actual and example dataset, I get the "Object Variable not set" on

Code: Select all

DataErrorProperties.ErrorBarStyle = 2
I do not have a Java Runtime Environment to try the Tools -> Macros -> Run Macro process. Do you know where I can get a JRE to attempt this?
OpenOffice 4.1.10 Windows 10
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Using MeanValue and ErrorBarStyle from ChartStatistics

Post by FJCC »

The link on this page labeled Windows Offline should get you the 32 bit version of Java.

The attached file has the macro embedded in it. Does that work?
Attachments
Example Sheet-1.ods
(19.87 KiB) Downloaded 190 times
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
flyer48
Posts: 14
Joined: Fri Jun 18, 2021 5:19 pm

Re: Using MeanValue and ErrorBarStyle from ChartStatistics

Post by flyer48 »

It worked after installing Java and going through Tools -> Macros -> Run Macro. It also worked before installing Java on the file with the macro embedded.

Thank you for your help!
OpenOffice 4.1.10 Windows 10
flyer48
Posts: 14
Joined: Fri Jun 18, 2021 5:19 pm

Re: [Solved] MeanValue & ErrorBarStyle from ChartStatistics

Post by flyer48 »

When attempting to make this run as a looping function to create standard deviation bars for all of the graphs in the sheet

Code: Select all

Dim Sheets As Object
Dim i As Long
Dim Obj1 As Object
Dim Charts As Object
Dim Obj2 As Object
Dim EmbeddedObject As Object
Dim Diagram As Object
Dim DataErrorProperties As Object

For i = 0 To 9
Sheets = ThisComponent.getSheets()
  Obj1 = Sheets.getByIndex(0)
  Charts = Obj1.getCharts()
  Obj2 = Charts.getByName(i)
  EmbeddedObject = Obj2.getEmbeddedObject()
  Diagram = EmbeddedObject.getDiagram()

  DataErrorProperties = Diagram.DataErrorProperties
  DataErrorProperties.ErrorBarStyle = 2
  DataErrorProperties.ShowNegativeError = TRUE
  DataErrorProperties.ShowPositiveError = TRUE
  Next i
an "Object variable not set" is thrown on

Code: Select all

DataErrorProperties.ErrorBarStyle = 2
.
The original works in the example sheet posted in this thread, but as soon as the any change is made to either the dataset or the code to get the right chart name, that error is thrown.
Here is my graph creating macro for reference

Code: Select all

Dim Doc As Object
Dim Charts As Object
Dim Chart as Object
Dim Sheets As Object
Dim Obj1 As Object
Dim Charts2 As Object
Dim Obj2 As Object
Dim Rect As New com.sun.star.awt.Rectangle
Dim RangeAddress(0) As New com.sun.star.table.CellRangeAddress
Dim CellRange As Object
Dim Sheet As Object
Dim i As Long

Doc = StarDesktop.CurrentComponent
Sheet = Doc.Sheets.getByIndex(0)
CellRange = Sheet.getCellRangeByName("A1:GZ1")
Charts = Doc.Sheets(0).Charts


For i = 0 To 5
Rect.X = 8000
Rect.Y = 1000 + 1000 * i
Rect.Width = 10000
Rect.Height = 7000
RangeAddress(0).Sheet = 1
RangeAddress(0).StartColumn = 0
RangeAddress(0).StartRow = 2 * i 
RangeAddress(0).EndColumn = CellRange.computeFunction(com.sun.star.sheet.GeneralFunction.COUNT) - 1
RangeAddress(0).EndRow = 2 * i + 1 

Charts.addNewByName(i, Rect, RangeAddress(), True, True)
Chart = Charts.getByName(i).embeddedObject
Chart.Diagram = Chart.createInstance("com.sun.star.chart.LineDiagram")
Chart.Diagram.Wall.FillColor = RGB(255,255,255)
Chart.Diagram.HasXAxisGrid = True
Chart.Diagram.HasYAxisGrid = True
Chart.Diagram.YAxis.Min = 0
Chart.Diagram.YAxis.Max = 100
Chart.Diagram.DataRowSource = com.sun.star.chart.ChartDataRowSource.ROWS
Chart.HasLegend = False
Chart.Diagram.SymbolType = 0

Chart.Diagram.HasXAxisTitle = True
Chart.Diagram.XAxisTitle.String = "Tests"
Chart.Diagram.HasYAxisTitle = True
Chart.Diagram.YAxisTitle.String = "Result Value"
Chart.Title.String = "From Row" & i+2  

Chart.Diagram.MeanValue = True
Next i
OpenOffice 4.1.10 Windows 10
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: MeanValue & ErrorBarStyle from ChartStatistics

Post by FJCC »

I doubt you want to do

Code: Select all

Obj2 = Charts.getByName(i)
Since you are using a numeric index, I would expect

Code: Select all

Obj2 = Charts.getBIndex(i)
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
flyer48
Posts: 14
Joined: Fri Jun 18, 2021 5:19 pm

Re: MeanValue & ErrorBarStyle from ChartStatistics

Post by flyer48 »

Even with that change it is throwing the same error.
OpenOffice 4.1.10 Windows 10
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: MeanValue & ErrorBarStyle from ChartStatistics

Post by FJCC »

I made a file with two line charts on the first sheet and this macro added error bars to both charts.

Code: Select all

Dim Sheets As Object
Dim i As Long
Dim Obj1 As Object
Dim Charts As Object
Dim Obj2 As Object
Dim EmbeddedObject As Object
Dim Diagram As Object
Dim DataErrorProperties As Object

For i = 0 To 1
  Sheets = ThisComponent.getSheets() 'Could be outside of the loop
  Obj1 = Sheets.getByIndex(0)  'Could be outside of the loop
  Charts = Obj1.getCharts()  'Could be outside of the loop
  Obj2 = Charts.getByIndex(i)
  EmbeddedObject = Obj2.getEmbeddedObject()
  Diagram = EmbeddedObject.getDiagram()
  Diagram.ErrorBarStyle = 2
  DataErrorProperties = Diagram.DataErrorProperties
  DataErrorProperties.ShowNegativeError = TRUE
  DataErrorProperties.ShowPositiveError = TRUE
Next i
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Post Reply