BASE: Charts in Reports

[branestawm]

I have been working on a pair of macros to try to get around the current weakness (failure) of Report Builder to generate Charts in Base Reports, for SQL databases in particular. I prefer to embed charts in Reports, rather than in Forms, because it is so much easier to add commentary and description to reports intended for printing:

A) Create (or re-create) a Chart report in Report Builder. To allow quick regeneration of Chart reports each time LO is upgraded - and Base chart reports get broken.

The attached Base file testodb0.odb uses built-in HSQLDB and includes two reports:

1. ‘Sales_Report’ created in Report Builder.
2. ‘Sales_Report_by_code’ created by running macro ‘Sales_Report_ORB’ in module ‘Sales_Report_ORB’ - or use custom menu ReportMacros (to the right of Help).

The ‘Sales_Report_ORB’ macro fails to include the horizontal line (actually a thin Rectangle shape) in the Page Header. Does anyone have any ideas on why not?

Apart from this, the two reports look identical when inspected in Report Builder but both suffer the same fate when executed (in LO v5.3.6.1 colours revert to defaults, in LO v6.0.1.1 the Chart does not appear). So this would seem to be a dead end - unless other people have a different experience?

B) Create a Base report directly in Writer, bypassing Report Builder and its integration into LO. To be more resilient against LO upgrades, but report changes will have to be done in code - which can be tedious.

Execute macro macro ‘Sales_Report_Writer’ in module ‘Sales_Report_Writer’ - or use custom menu ReportMacros (to the right of Help). This works well in both LO v5.3.6.1 and LO v6.0.1.1 (TDF builds under OpenSuse Linux x64). I’d be interested to hear how well it works in other versions of LO?

These macros explore many elements of a chart and of report generation which I hope may be of interest to macro coders. Chart type in these macros is a simple Pie Chart, but I also have a Stacked Area report and a grouped XY chart report which I hope to be able to post soon.

I have learnt much from other contributors whom I would like to acknowledge, including ratslinger, Villeroy, longi, Robert Großkopf.

[Ratslinger]

@branestawm Unfortunately nothing in the sample work for me using LO v6.1.0.3 on Mint 18.3.

The Base reports are not generating a chart on the report. This is probably related to bugs - See my answer here.

As for your macros, the “ORB” selection opens a edit window for Report Builder then closes it. Nothing generated. This after commenting out the fact of you loading XRay which I don’t use.

[Ratslinger]

The second ‘macro’ report generates an error stating it cannot find “Normal.ott”. Closing that it then errors with:

BASIC runtime error.
An exception occurred 
Type: com.sun.star.lang.IllegalArgumentException
Message: Unsupported URL <file:///home/MYDIRECTORY/.config/libreoffice/4/user/template/Normal.ott>: "type detection failed".

[branestawm]

Thank you for your test and comments.

From your description, Sales_Report_ORB is working as intended. It creates or replaces report Sales_Report_by_code, which you can then edit or run as normal. It does not leave open Report Builder GUI, and the process runs too quickly to see anything useful during the build. To see a newly created report, simply delete it before running the macro.

[branestawm]

Sales_Report_Writer is based on the user’s default template in Writer (I had to start somewhere). In my system, this file is “Normal.ott”. The macro searches for it in those locations defined in UNO service com.sun.star.util.PathSettings which include the user’s HOME path, using the first file it finds. Your system evidently differs in some way, so I suggest you could simply replace lines 39-44 with ‘strName = whatever-is-the-full-path-and-file-name-for-your-default-template’.

[branestawm]

I have edited testodb0.odb file in my posting to delete report Sales_Report_by_code so as to make it’s creation more obvious, and also to comment out remaining references to Xray.

To (re)create report Sales_Report_by_code, execute macro macro ‘Sales_Report_Writer’ in module ‘Sales_Report_Writer’ - or use custom menu ReportMacros (to the right of Help).

[Ratslinger]

@branestawm Had to create a Normal.ott as my TDF v.6.1 did not even have a template folder. After that the chart was created.