[Solved] Can't import numeric data from Base via Data Pilot

[mattia83]

Hi everybody. I thought I solved my problem of creating graphs from data from database by using Calc, but...

Everything works fine with data pilot as long as I import strings, dates, or integer.

When I try to import Decimal, real, float, I obtain for example:

160,94 becomes 316.40.00

I thought it is a problem of numeric format, so I tried to cast the values in the query as any of the allowed numeric format, but only integer works, and being these data prices I need to store 2 decimals.

Any idea??

Thanks

[Villeroy]

Which type of database? HSQLDB, MySQL, dBase, csv files,...?
WHat happens when you drag the table/column directly into a sheet without building a pilot?
Which type of pilot field? Row, column, page or data field?
Data fields apply some function to your numbers. All other field types turn numbers into text.

A pilot gets the sum/count/average/min/max of numeric data fields X,Y,Z for each combination of column A,B,C,D in row/column/page fields. The resulting data are exactly the same as in the following grouped SQL aggregation:
SELECT A,B,C,D,SUM(X),COUNT(Y),AVERAGE(Z)
FROM TABLE
GROUP BY A,B,C,D
The pilot can display the result in a cross table layout whereas the SQL query returns a flat table.

[mattia83]

Which type of database?

HSQL

WHat happens when you drag the table/column directly into a sheet without building a pilot?

Everything works fine. But this way I can't update data right?

Which type of pilot field? Row, column, page or data field?
A pilot gets the sum/count/average/min/max of numeric data fields X,Y,Z for each combination of column A,B,C,D in row/column/page fields. The resulting data are exactly the same as in the following grouped SQL aggregation:
SELECT A,B,C,D,SUM(X),COUNT(Y),AVERAGE(Z)
FROM TABLE

Ok, I think I misunderstood the functions of datapilot. I used it very nicely to obtain a double entry table. I thought I could use the same function to obtain a more simpler flat table (the one I get from a query) from where extract data for graphs.

Now I'm back to the beginning. Will try again. Thanks for the explanation.

[Villeroy]

Everything works fine. But this way I can't update data right?

Wrong. [Tutorial] Using registered datasources in Calc

[mattia83]

Thanks

[mattia83]

Thanks a lot, one more question: is there a way to keep the format of the cells after refreshing?

[update] Done, there's a flag in Data->Define [end of the update]

[mattia83]

I have a problem:

When I load the spreadsheed the program asks if I want to reload the queries.

Everything works fine, the data are updated except that i can find some of the old values translated a few cells down, and any other cell below will be translated down.

here's a screenshot before and after (I put a series of "x" to show the translation")


Before


After

any idea?

[Villeroy]

In Data>Define set the option to insert/remove cells.

[mattia83]

Does not work..

[mattia83]

I tried to rewrite the query to be imported, but nothing changes, the result of each query is a single cell table.

[Villeroy]

In Calc hit F4 to get the docked data source window.
Browse the queries of your registered database in the the left pane of the docked window.
Drag the query icon from the left pane of the docked window (no object from the right pane!).
Calc should import the whole set of rows and columns as a linked database range.

menu:Data>Define...
select the name that belongs to the imported range of data cells.
Hit the "More" button.
On the bottom of that dialog you should read the datasource name and query name as confirmation that the database range is actually linked to the query.

[mattia83]

I did exactly the same, and the dialog reads the right query.

Still it works perfectly but everytime I reload the file the data are completely messed up.

I wonder what can be.

[Villeroy]

In your screen shot I can not see any database data. There are empty columns and mixed columns with numbers and x.

[mattia83]

E7, that 3 is the same that was inside the area that I imported from Base. The problem is that i can't understand why even if I have the same settings for every area, only some give this strange result.

[mattia83]

After a few days I'm back again. I seem to have solved the problem by joining many queries in one. I created a new query selecting the data I need from the others and imported it in a Calc spreadsheet. You know? No problem. I cannot explain why but the problem seems to happen only when importing a one-cell table and aligning many in the same row. Do you have any explanation? thanks for your patience and precious help.