[Solved] Spreadsheet charts one page multiple sources

[misitu]

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

[Villeroy]

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

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: viewtopic.php?f=101&t=90121
You can create pivot tables (aggregated cross tables) from database row sets and bind charts to the pivot tables.

Code: Select all

Sub refreshPivots()
for each sh in ThisComponent.getSpreadsheets()
   for each pv in sh.getDataPilotTables()
    pv.refresh()
  next
next
End Sub

[misitu]

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

[Villeroy]

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.

[misitu]

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.

[misitu]

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

	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!