[Solved] Spreadsheet charts one page multiple sources

Creating a macro - Writing a Script - Using the API

[Solved] Spreadsheet charts one page multiple sources

Postby misitu » Wed Sep 27, 2017 5:12 pm

TLDR: Macro to read 10 views from AOO BASE database and display as 10 charts "en echelon" on first spreadsheet.
Run through a master table containing colours and text for each account (1 to 10) and for each account collect data from a View ending in 1 to 10.

This is to provide an easy visual presentation of cash flows. I am stuck on only showing two charts as follows.

I've got a series of views established as sources to an OO spreadsheet. Every time I open the spreadsheet I have to activate the data source and import from each view into its own pair of columns which provide the source to its appropriate chart. There are 10 of these views.

I'm working on creating a macro to do this.

The end result will be a spreadsheet with a chart for each view on the first sheet, a list of view parameters on the second, and the data loaded from each view on sheets 3 to 10.

I can manage to load the data for sheets 3-10 but am getting stuck on the chart. My current code for the chart aims at drawing the 10 charts on sheet 1 but it only draws 2 charts, *partially - no dataset lines* and then halts with "IndexOutOfBoundsException.. DataSeries index invalid".
Attachments
Spreadsheet (live figs redacted).ods
I removed live data from the CFLA__1 and CFLA__2 sheets. The other sheets CFLA__3 to CFLA_10 have not been generated because the macro stops after doing the first 2.
(21.22 KiB) Downloaded 33 times
Partial front sheet 50.jpg
Showing the first sheet only (tabs for the others are shown) as an image
Error Message from Macro.jpg
Error Message
Error Message from Macro.jpg (29.3 KiB) Viewed 1029 times
Last edited by misitu on Sat Sep 30, 2017 1:54 am, edited 1 time in total.
OpenOffice 4.1.1
HSQLDB 2.3.4
Windows 7 HP / Windows 10
OOBasic
User avatar
misitu
 
Posts: 89
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

Re: Spreadsheet charts one page multiple sources

Postby Villeroy » Wed Sep 27, 2017 6:14 pm

No need for big macros.
Create one query for each chart.
Drag the query icons from left pane of the data source window into the spreadsheet, side by side or on separate sheets.
Link charts to the import ranges.
You may hide the sheets with import ranges.
Create a toolbar or menu entry linked to this macro:
Code: Select all   Expand viewCollapse view
Sub refreshDBRanges()
For each dbr in ThisComponent.DatabaseRanges
  dbr.refresh()
next
End Sub

The charts will adjust to the changing sizes of the refreshed database ranges if property "Insert and Remove Rows" of the database range is set. By default it is.

LibreOffice 5.4 comes with pivot charts: https://forum.openoffice.org/en/forum/v ... 01&t=90121
You can create pivot tables (aggregated cross tables) from database row sets and bind charts to the pivot tables.
Code: Select all   Expand viewCollapse view
Sub refreshPivots()
for each sh in ThisComponent.getSpreadsheets()
   for each pv in sh.getDataPilotTables()
    pv.refresh()
  next
next
End Sub
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25758
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Spreadsheet charts one page multiple sources

Postby misitu » Wed Sep 27, 2017 8:19 pm

Thank you, Villeroy.

"Been There Done That". I was trying to advance ... I want my other half to be able to "click and there you are, new charts": most non IT folks don't want to bother with all that drag and drop stuff. What you mentioned was my prototype, hand built set of tools, already working and much hated. What I *want* is run macro, generate PDF. I know how to do that. I have already produced PDF with "big macro" chart generators.

What I would *really* like is to figure out what is stopping my macro at two charts. If I leave out the chart processing I can fill up all the sheets one per account. If I put it in I only get 2 half charts and 2 account sheets.

Clearly there is some basic misunderstanding of mine operating here.

I do have Xray although I'm not very good at using it. I don't have MRI because it has installed broken and won't uninstall so I am leaving this alone until I reload Windows at some remote point.

I guess therefore that Xray might help but as I say I'm not very good with it so some clues would help.

Thanks for trying to help, though,
David
OpenOffice 4.1.1
HSQLDB 2.3.4
Windows 7 HP / Windows 10
OOBasic
User avatar
misitu
 
Posts: 89
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

Re: Spreadsheet charts one page multiple sources

Postby Villeroy » Wed Sep 27, 2017 9:48 pm

I thought that YOU prepare the spreadsheets and charts for your audience. They are the ones who open a spreadsheet and without a single click the on-open-macro shows them all the updated charts. I have several of these sheet reports with pivot tables and charts opened by a simple hyperlink button on database forms. One click on a push button, the spreadsheet opens and a tiny macro updates all the relevant data in it.

I support a group of 100% non-techies using a couple of form letter templates. When they are going to send a serial letter (about 50 to 200 receipients) I am the one who prepares the list to be merged (can be anything, a query or something copied from various sources into a sheet). Then I tell them to use template "Form Letter A" with the query named "Addresses_2" in "Data Source Y". They open the template, call Edit>Exchange Database... pick the source and query, start writing whatever they need to write. This works very well simply because I showed them how a well prepared list merges into a well prepared template, how they can open a template and how they can switch the new document from one list to another list. Making the templates and the lists with the right column headers is my job.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25758
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Spreadsheet charts one page multiple sources

Postby misitu » Thu Sep 28, 2017 2:41 am

Really, I am stuck on an understanding UNO issue. Clearly I have an index out of bounds but with all the complications of the UNO structures it's not easy to know where to start. Also there is alway a remote chance that one of the under the hood implementations is incomplete in the OO suite. I have encountered such defects (line colouring for example) so it would be good to have the assurance that this is not the case. This could only be ruled out if I could fix my misunderstanding which has caused the loop to quit at 2 instead of 10. Obviously is chart related.

In the absence of any more immediate responses I am going to split the chart code into a separate subroutine so it's easier to play with.
OpenOffice 4.1.1
HSQLDB 2.3.4
Windows 7 HP / Windows 10
OOBasic
User avatar
misitu
 
Posts: 89
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

Re: Spreadsheet charts one page multiple sources

Postby misitu » Sat Sep 30, 2017 1:53 am

From Villeroy,
The error message about the "invalid data series index" should be as clear as possible for you. I can only guess that you are trying to access obj.getByIndex(x) although there is no element x in obj.

Indeed, I had a dim without the array suffix and replaced with
Code: Select all   Expand viewCollapse view
   Dim RangeAddress(10) As New com.sun.star.table.CellRangeAddress

which cured that problem.

I now have 10 coloured rectangles. I still need to make the lines follow the data but that is for another day.

Thanks!
OpenOffice 4.1.1
HSQLDB 2.3.4
Windows 7 HP / Windows 10
OOBasic
User avatar
misitu
 
Posts: 89
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 4 guests