Using ODBC Data To Create A Chart

[chedrcheez]

I downloaded and installed LibreOffice for the first time just now on a Catalina Mac. I want to connect to my Oracle database and use the data to create a chart.

In Base, I can query my data easily, that part works great!

When I try to bring the data into a form, the Wizard can see the fields and creates the correct number of records in the form, but all of the records are empty – no data is imported or displayed.

Ultimately, I would like to use the data to create a chart. I do not see any tools for creating a chart in Base, and so I imagine that if I can get the data in to Base … can Base data be connected to Calc so I might create the chart there?

[Villeroy]

Forms are for input mainly.
Reports should support charts. Unfortunately, this feature is broken.
You can easily link query results to spreadsheet ranges.
However, queries return record sets, and charts with more than one data series require cross-tables with chart series in different columns.

Record set

Category	Value
A	12
B	20
C	31
A	11
B	21
C	33

Chart Data

A	B	C
12	20	31
11	21	33

It is possible to generate a pivot table from a record set and then create a pivot chart with many data series from the pivot’s column fields.
And some database engine support pivot tables as well.

[RobertG]

@Villeroy

Unfortunately, this feature is broken.

Which version of LO do you use? And: Is it a version packed by Linux distribution?
I could execute reports and see charts in a report with

Version: 7.5.5.2 (X86_64) / LibreOffice Community
Build ID: ca8fe7424262805f223b9a2334bc7181abbcbf5e
CPU threads: 6; OS: Linux 5.14; UI render: default; VCL: kf5 (cairo+xcb)
Locale: de-DE (de_DE.UTF-8); UI: de-DE
Calc: threaded

[Villeroy]

May be it is fixed by now. It used to be broken whenever I tried.

Version: 7.5.5.2 (X86_64) / LibreOffice Community
Build ID: ca8fe7424262805f223b9a2334bc7181abbcbf5e
CPU threads: 4; OS: Linux 5.15; UI render: default; VCL: x11
Locale: de-DE (de_DE.UTF-8); UI: en-US
Calc: threaded

P.S. tested a minimal chart with a most basic record set and it works indeed.

[iplaw67]

You don’t explain how you’re trying to bring your query data into the form.

If you can successfully query your data in Base, it should be possible to create a form that displays that data, within the constraints of the available form controls, although as @Villeroy and @RobertG have mentioned, DB Forms are not the place for Charts created dynamically from query data. Currently, such functionality only seems to be found in DB Reports provided via the report builder, or via the Data Pilot in Calc with named DB ranges (and probably via supported scripting language programming into a suitable document).

[RobertG]

One hint: Have created forms, which could also show charts. The English Base Guide 7.3 is a little bit old, but German Base Handbuch contains a description for this and also an example database. But: You will need much macro code for this…

[Villeroy]

Pivot_Month_Person_Category_Subtotals.ods (76.8 KB)
Shows a record set on the left side and a pivot table with chart on the right. The record set could be any kind of data source, imported into the sheet or not. IMHO, this is the most convenient way to create a chart from database data because the record set is transposed into a columnar layout. If you do not want any aggregation by sum/count/average/etc, just include the primary key, so you get one row for each distinct record.
menu:File>New>Database…
Connect to existing database
Type: ODBC
Specify the data source.
[X] Register the database
Save the database and add a query for the data to be plotted.

In Calc:
menu:Data>Pivot>Create…
[X] Create from registered data source, select the source and query.
Drag the values to be plotted into the box “Data Fields”.
Drag categories you want to split into separate chart series to “Column Fields”
Drag x-axis data to “Row Fields”.
Finally you click any cell in the resulting pivot table and create a chart.

[chedrcheez]

Thanks to you and the others for your suggestions. Using this suggestion, I was able to import the data into Calc and create a chart! Success! I appreciate the help for us newbies.