Python and Calc template integration with a chart in it

[jampyapp]

Hi there!

Jam.py framework is using LO templates for creating reports. This can be seen in here:
https://demo.jam-py.com/
Click on Invoices, Reports, Print Invoice

This is working fine. What I do not understand is how to add an Chart on the template which will actually display data!
The area Chart IS displayed, but it is empty and showing only Legend with data. Even the Legend is not accurate representation. So it is getting some data, but not all to actually show the chart. I think the problem is a moving rows. The chart has data range which should accommodate for moving number of rows, but even with one single invoice item, so one row, it does not show anything. Obviously, an Invoice might have a number of items. So the data range changes, and the legend cell location changes as well. The legend is simple, subtotal, tax, total. Just that. At the bottom of the template. Oh, btw, the framework passes to template this 3 variables, so that is working fine. Looks like we could even pass the legend variables to a template, so all needed data can be passed to a template. But how would chart consume that?

According to Jam docs:
Reports in Jam.py are band-oriented.
Each report template is divided into bands. To set bands use the leftmost column of a template spreadsheet. In the Invoice report template there are three bands:
title, detail and summary.
In addition, templates can have programmable cells.
For example, in the template of Invoice report the I7 cell contains the text %(date)s.
Programmable cell begins with%, then follows the name of the cell in the parenthesis which is followed by character s.

All I need is a simple Chart positioned below with:
SUBTOTAL, TAX, FREIGHT
$x $y $z

But because x-amount of rows, the data range is a moving “window”.

Talked with the Jam Developer, he says no go. Any ideas from the community? Ready to raise a bounty Sorry for the long post.

Thx

PS
Added Chart on Invoice template in here:
https://conv.jampyapplicationbuilder.com/demo/
Now you can see the chart is actually showing items, instead of SUBTOTAL, TAX, FREIGHT in Legend.
Example is Invoice for Dan MIller (11/21/2018) - it has only one row and correctly displays Legend. No chart though.

[jampyapp]

Hey,
spent more time on this, looks like API is inserting an single quote on every numerical. Hence Chart does not understand this.
However, removing a single quote won’t fix the data range, which is a moving “window” with more items added on an Invoice.
We do not need Jam.py example to demonstrate this. All we need is an arbitrary number of rows and the summary below all rows to make a Chart with. Like this in a spreadsheet:

UNIT PRICE, AMOUNT
|$0.99|$0.99|
|$0.99|$0.99|
|$0.99|$0.99|
|$0.99|$0.99|
|$0.99|$0.99|
.
. < n-number of rows!
.
|SUBTOTAL|$7.92| ←
|TAX|$0.40| ← This is what we make a Chart with, so what is the data range and category?
|TOTAL|$8.32| ←

[Villeroy]

@jampyapp

So it is getting some data, but not all to actually show the chart. I think the problem is a moving rows.

Tools>Options>Calc>General: “Expand references when new rows/columns are insterted”

When unchecked
Insertion of rows at first row moves the reference down. A1:A99 becomes A2:A100.
Insertion of rows directly below a reference does nothing.
Insertion of rows anywhere between 2nd and last row expands the reference… A1:A99 becomes A1:A100.

When checked
Any insertion of rows from the first until the row directly below expands a reference. A1:A99 becomes A1:A100.
Precondition: The reference nees to include more than one row. A formula =SUM(A1:F1) which sums up a single row will not expand but =SUM(A1:F2) will.

Same rules apply to columns.

[jampyapp]

Thank you for reply,

will have a look again after back from holidays!
I do remember doing this though. It might be a different behavour when doing it over API and when doing the same in open Calc.

Will see…

Best wishes

[Villeroy]

@jampyapp

It might be a different behavour when doing it over API and when doing the same in open Calc.

Nope. It behaves consitently. The attached document installs a Python macro which turns this global option on in order to insert/remove rows.
InsertCalcRows_LO.odt (23.3 KB)

[jampyapp]

Hello,

back from holidays. I checked the “Tools>Options>Calc>General”
This is working fine within the LO Calc with ODS template. If I add a row(s) above the Chart, it will work ok.

However, this is not the same when dong it headless. This can be seen as per above. Any Invoice with more than one row will not show Chart. Only Invoice for Dan MIller (11/21/2018) will show correctly 0 since only one single row, I’m guessing.

The template is here:
https://conv.jampyapplicationbuilder.com/demo/static/files/invoice.ods

I’m keen to pay a bounty for this, it is just out of my knowledge. Anyone?

Thx

PS
Just to clarify, there is NO LibreOffice on the above server, hence Tools>Options>Calc>General does not apply.
So any Report/Print Invoice, will just download generated ODS with Chart keeping original Data Range.

But this is the same behavior on my test server, which HAS LO installed and running it headless. The data Range is just not updated to accommodate for more rows. Please see this happening with any Invoice, the Data Range is always the same for Chart. Cheers

[jampyapp]

Ok, made some progress, looks like if I put the Chart ON TOP of the page, than the chart will pickup all rows and even the legend will be accommodated.

Maybe I can live with that. Cheers and all the best.