Charts in Base forms

This is a method to display charts in a Base form.

The GOOD:

This is not a newly created charting method. The process uses the existing chart code already used for creating charts in other modules.

The BAD:

The only way to currently get this to work is through macros. There is no UI for the process (although I have a few ideas about that). If you know nothing of macro coding, look at the pretty pictures, say “That’s nice” and go on to other things.

The UGLY:

Working with the chart API is one pain-in-the-neck! At first, I believed I could document the items necessary to create various displays and functions. The more I looked, the more I found. It would take a book (maybe a small one) to document all the settings and dependencies for the various charts. Instead, I decided to release what I have found so others may utilize it.

If you are looking for how to do something, you’ll have to dig into the API yourself as I have been doing. For this you really need to use MRI or XRay or other methods (including on-line documents) and probably some trial & error type testing. Most of my discoveries were obtained by creating a chart in Calc and using MRI to see where the chart change was set in the deep underground. The macros provided in the sample give you a large advantage as to where to start looking. The larger problem is how some settings are not usable unless other settings are turned on. Case in point - “floor” & “wall” settings are only available with 3D diagrams.

The Sample:

The included sample has three different forms each with a different chart (and some optional settings). Unlike Calc where the data for the chart comes from cells, the data here is created in arrays; one each for data, row titles, and column titles. The chart itself is positioned using a TextCursor (see OOME for how to on this). If the data for the chart is changed, the chart must be re-drawn to display the changed information (duh!).

There are some comments in the macros to help with the process. It is important to note that certain processing should not be interspersed in the code which creates the chart. Various results occur when this is done such as crashing, incorrectly sized charts, incomplete charts and more. This is noted in the code. Each sample utilizes a unique setting or two and may include commented out settings which have been tried but not incorporated into the chart.

Area chart sample:

This sample gets its data from use of an embedded SQL statement in the macro. No user options available.

Bar chart sample:

This sample uses hard coded data for the display (not what anyone would typically want). Did this for demo purposes only. There are options available to change the display of the chart. Choose a selection and use the button to create the chart again with the changes.

Line chart sample:

This sample gets its data from queries already saved in the query section of Base. This code is in the Base documentation. Never had a use for it before but it works quite well and saves hassle of embedded SQL and outside testing. This sample also has user option available. Choose a selection and use the button to create the chart again with the changes.

There are numerous ways of presenting user options. A little imagination goes a long way. For example, in the line chart example you can have input fields for FROM and THROUGH dates for data selection.

Bottom Line:

As with all code and samples in this forum or anywhere else obtained freely, USE AT YOUR OWN RISK! There are no guarantees or implied completeness. I have only had this working for a week or so and even though testing shows good results you can never test enough. A wise man once told me “There is never enough time to thoroughly test but there always seems to be enough time to fix problems”.

This process is not something you can grasp overnight. It took on & off looking for months to get where it is now and as stated before, there is a lot more to discover in the API. Too many people rush into something needing an immediate answer. There are not a lot of answers for this directly available. Time, patience, and research is needed.

Edit 4/30/17: Change SQL for proper processing (bad habit of mine).

SAMPLE: ChartInBase.odb

Edit 5/1/15: The following sample inserts the chart into a TextFrame and allows better positioning.

Frame Sample: ChartInFrame.odb

2 Likes

Very impressive! Thanks for this code @Ratslinger

Great work! I voted it, thank you.

@librebel & @charlie.it Thank you for the kind remarks. So much to apply to this yet. FYI, I have found better control on positioning by placing this inside a text frame. Allows much better control. See post for sample.

I have discovered that most of the chart settings are discussed in the AOO Developers Guide - click here. Also look at the section following that one for more.

can load the base files, but on click on tables or forms i get: ‘The connection to the data source “14936747746269369” could not be established.’

silly fault of mine i think, but which?, missed some download? but which?

i’m totally new to base, sorry …

reg.

b.

@newbie-02,

See these posts for possible solutions:

No Connection to HSQLDB Embedded Database

how to fix No SDBC driver was found for the URL ‘sdbc:embedded:hsqldb’.

no need to say that i hate! this part of configuration …

firefox x64 doesn’t support java check,

LO 6.2.8.2 winx64,

installed acc. LO config:

jre 11.0.2_231, 32-bit?

added 1.8.0_251, 64-bit,

(control panel has also installed jdk 11.0.2 64-bit,)

regardless which i choose and restart:

click on tables in your sample:

‘General error: org.hsqldb.lib.FileSystemRuntimeException: java.io.IOException:’

:frowning: :frowning: :frowning:

Has nothing to do with Firefox. You should have the 64-bit Java installed following this given link → FAQ. Also make certain after Java is installed that LO settings are enabled. See → I have been trying to create a new database in base but it keeps telling me I have a problem with Java.

If you continue to have problems, this should be a new question (although there are over one hundred answers on this topic - Java). It is not specifically about this post, but rather a problem with general execution of HSQLDB Base files.

sorry, was somewhat slow to come to that idea, works on my linux machine … :slight_smile:

thus your work is ok, cooperation of win, LO, java and me is somewhat difficult … :frowning:

Thank you very much for this solution, it helped a lot. The Text Frame version really helps positioning. I just couldnt make it delete the frame when deleting the chart. I called the frame "FrameID"and then added to the DeleteChart sub:

oFrame = oEmbeddedObjects.getByName(FrameID)
oFrame.dispose

but I keep getting NoSuchElementException

I’m a doing something wrong?

ty!

This works for me:

oTextFrames = ThisComponent.getTextFrames()
oFr1 = oTextFrames.getByName(sFrameName)
oFr1.dispose()
1 Like

solved it like this:

Sub DeleteFrame
DIM oDoc, oTextFrames, oFrameDT AS Object

oDoc = ThisComponent
oTextFrames = oDoc.getTextFrames
If oTextFrames.hasByName("FrameID") Then
	oFrameDT = oTextFrames.getByName("FrameID")
	oFrameDT.dispose()
EndIf

End Sub