Charts in Base Forms

[Ratslinger]

Opening note:
This was developed & tested on Linux Mint 18 & Xubuntu 16.04 using LibreOffice 5.3.x. However I have tested this using AOO 4.0 and the sample works with one exception - when selecting an option and redrawing the chart, the checkboxes become invisible. They are still there & selectable & will re-appear with a screen resize or close/open of form. This does not affect the displaying or process of the chart. It does make it a bit more difficult to demo optional settings. Not having worked much with AOO, I decided not to hold this post just for this. I will post a corrected sample when I figure out why this is happening.


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

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.

[Ratslinger]

I have discovered most of the needed settings can be found in the Developers Guide:

https://wiki.openoffice.org/wiki/Documentation/DevGuide/Charts/Common_Parts_of_all_Chart_Types

and also the section following that one.

For more accurate positioning of the chart, insert the chart into a text frame. The text frame is easier to maneuver.

[arfgh]

this is highly interesting !!

[longi]

Hi!
I've just discovered your example, and I think it's exciting.
I got long time ago the way to put charts in a report with the old report builder (not ORB).
At the end a report is a writer document with all tools to be a form.
My code was really weird, but effective.
I was able to show a chart based on different queries or inside a frame, below or above a table, etc.
The main drawback is that you have to make a macro for each one and you can't choose nothing once you finished the macro (unless you want to change the code). So I thought about a dialog, but I don't know how to do that by now.
Here you have several examples in the middle of other things, but with Spanish explanations!

https://forum.openoffice.org/es/forum/viewtopic.php?f=70&t=11605
https://forum.openoffice.org/en/forum/viewtopic.php?f=42&t=79116 (Only one example, but in my own 'special English')

By the way!, If you have some controls which are desappearing, probably you have to play with anchor and position of that control (maybe to group them). As you said, they are in the form, but not visible, so maybe they are below the main picture which is the whole form.
I hope something of this could be useful for you (or for us, if you publish your new results)
Sorry for my English!

Bye!

[Ratslinger]

@longi Thank you for the comments. Have seen your sample from other posts and find some of the code interesting. Also, thanks for the suggestion on control anchors. Don't think this is the problem but will look into it.

[Fly]

it's beautiful sample, but sorting in chart work with months and is obtained 1.2016 early 3.2015. Anybody May be help me fix it?

[Ratslinger]

Hello Fly,

Your question has little to do with this chart sample. It is really a matter of your SQL statement generating the data to be used. You need to sort your data in the SQL:

Code: Select all   Expand viewCollapse view

SELECT * FROM YOUR_TABLE ORDER BY YEAR(yourDate), MONTH(yourDate) ASC

[Fly]

Thanks you for advice, but i try, and try... but macros doesn't work true. that post include *jpg chart.
i think - macros convert "data" to "text" and sorting as "text"

[Ratslinger]

Fly -

i think - macros convert "data" to "text" and sorting as "text"

The macros do nothing with the data you place in the arrays. The sequence of the data is as you place it. The problem is with your SQL. You need to check it by running it as a query and viewing the results. I have used dates in charts before it was ever published here and never had a sequence problem:

[Fly]

Ratslinger, thanks for the answer. but still sorts only by months. if you can help - I will be very grateful.

[arfgh]

the most insteresting would be if some expert fix the charts on the 'report builder extension'....
anyways, can you please share the example that show that beautiful chart up these lines ? ^^

[Ratslinger]

Fly, it appears you do not understand the code in the least. Looking at your sample, you have an arbitrary table & SQL and tried to make it work with the same code as in the sample. This is not possible. You need to rewrite all the pertinent code for the Result set -> retrieval of data, filling of arrays, array sizes and more.

The posted sample has no fix. It has no understandable data (query result) to go with the code being used. You must first understand ALL the code and the role the query plays in the filling of the necessary arrays in order to generate any type of chart. This code will typically be different for each query used.

[Ratslinger]

arfgh,

If you are looking for something to do with "Report Builder" this post is not the place.

[arfgh]

just that, in the last version the report builder cant do charts.... that's the question

[João Luís]

Thanks a lot Ratslinger! This really helped me!