[Solved] Multiple Sheets derived from AOO dbase Truncated

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
User avatar
misitu
Posts: 102
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

[Solved] Multiple Sheets derived from AOO dbase Truncated

Post by misitu »

[solved] from several attempts to work through this, all ending with the same result, it seems that the Basic engine is reusing a variable value from the last instance in the previous loop and there is no way to subvert this. It's NOT a solution but the whole thing is being re-engineered as most of the code yields results as expected.

Apologies, if appropriate, for any time spent on this. That is, unless a solution was ready to hand in the meantime. Thanks
 Edit: It may be useful to leave some details here in case anyone else steps on the same rake.
The whole process was over-elaborate. It contained one main database search loop and two inside. Actually everything worked correctly except that (a) all the charts were limited to 12 entries, coincident with the result of the last loop, and (b) two data ranges were produced, not one.

In re-engineering this, I conflated the first two searches into one by extending the first, a VIEW, to include 1-for-1, the second. Those provided the header information for each Account including line/background and wall and max & min dates and .account balances. Within that was the original third search which provides dated balances for each account.

The effect of this, which was not obvious in my current UNO/AOO Basic repertoire, was that a second RowSet object (cursor) was needed for the inner loop. Pretty much everything came out just fine after that.

I also had some issues with dataRange() and dataRange(0) which kept failing the job with that error message about "Core Reflection" which is particularly opaque. To solve this I was able to find a code snippet on the forum that showed me which was which.

I've achieved the automation I wanted to ! A replacement for the tedious drag and drop which was unsuitable for the other user.

I've attached the result! 
--------------------------------------------------------- David ---------------------------------------------------

Multiple Sheets derived from an AOO Base database are Truncated when passed to Chart

The application is designed to provide a graphical representation of budgeted Cash Flow for a number of our Accounts. I've some experience of automating this stuff but there's a bit of a show stopper in here which has got me truly foxed.

The AOO Basic program cycles through 10 accounts first placing each one's budget in its own sheet and then cycling through each sheet to generate a line diagram over time.

However there are two faults in the graphing process.

The first is that two rather than one data ranges are created by the graph. The second is that only the first 12 entries are used.

The attached diagrams should clarify. The left one shows what the macro produces, i.e. a subset of the full data, and the right one shows what SHOULD BE produced. I simply clicked on the left one to amend the Data Ranges settings.

In the top right corner of each chart is a display showing the end row as the last figure (76 for the proper value on the right, and 12 for the truncation on the left). The numbers shown are: (phase), Account, Start Row, End Row.

Every one of the 10 accounts is graphed with this truncation, to 12 rows in each case.

As to what I've done: suspecting that the graphing process is picking up the last value for the End Row, all the variables that could be suspected have been dimmed as (10) and indexed appropriately.

So, what I'm thinking, is that there is nothing much to do apart from reporting this to the AOO crew, that is, unless there is something I've missed, which I'll be happy to have indicated.

This program is aimed at automating the drag and drop which I already have in place, so getting over this last hurdle would be a tremendous help.

Thanks as usual.

Attachment 1 - illustration of desired result
Attachment 2 - illustration of actual result truncated to 12 lines
Attachment - code [deleted, limit 3 attachments]
Attachment 3 - final results, as expected, as produced

will try later
Attachments
Attachment 3 - final results, as expected, as produced
Attachment 3 - final results, as expected, as produced
As it Should be, full graph
As it Should be, full graph
As Should (50).jpg (48.59 KiB) Viewed 1616 times
As it is, truncated to 12 entries, see Data Range boxes on left
As it is, truncated to 12 entries, see Data Range boxes on left
As Is (50).jpg (45.77 KiB) Viewed 1616 times
OpenOffice 4.1.1
HSQLDB 2.3.4
Windows 7 HP / Windows 10
OOBasic
Post Reply