Using Base to report from Calc

[seastick]

I have a spreadsheet of daily expenditure. I want to create a report such that I enter a date and get the expenditure for the week ending on that date. Similarly the expenditure is coded as different types, e.g. food or fuel etc and I'd like to be able to report on a specific type of expenditure for a period that can be entered, say a week or a month. Is Base a suitable/appropriate tool to do this? Would it require importing the spreadsheet into Base odr can Base query the spreadsheet directly.
Any advice greatly appreciated.

[RPG]

Hello

You can use base report builder for a spreadsheet. But only for those spreadsheets who are registered as a database.

Romke

[Villeroy]

The data pilot is the most powerful and convenient report engine for spreadsheet lists and database records likewise.
Instead of storing data on a sheet and using a database for reporting you may consider to store data in a database and use spreadsheets for reports.

[seastick]

Thanks,
Data Pilot is great for summarising, but doesn't seem to give me the opportunity to ask for the expenditure for a specific week or category, for example how much was spent on Petrol last week.
I put the data in a spreadsheet as it lets me see the overall result immediately without further input. I just want/need to be able to report on history and/or trends.

[seastick]

Thanks Romke, but I can't see how to register a spreadsheet file as a database, only database files.

[RPG]

Hello

Make a new database
menu --> file -->new -->database

select now connect to an existing database in your case a spreadsheet.

Select later the spreadsheet where your database is.

Now you have two files
a) Your database document in which you have your forms, reports and queries.
b) Your spreadsheet where your tables are.

I hope this is clear.

Romke

[Villeroy]

The data pilot can aggregate a column of dates to years, quarters and months. If you need a week field, just add it to the source data.
=YEAR($A2)&"-"&TEXT(WEEKNUM($A2;2);"00")
with date in A2 adds a sortable text fiels with week numbers like "2006-09"

When using Base or a combination of Base plus data pilot:
SELECT *, YEAR("Date")||'-'|||WEEK("Date",2) AS "Week" FROM "Sheet1"

[seastick]

Thanks RPG; i did this and got a database ok, but there are only 4 out of 5 sheets showing as tables, and only 2 columns of data rather than the 16 there ought to be. Are there constaints that restrict the number of tables. The data that is missing is all (as far as I've checked so far) either numerical or date format.

[RPG]

Hello

I don't know what reason there can be that not all tables are shown. Maybe the table filter is activ and makes not all tables are shown.

The table is when you see the table list
menu --> Tools --> table filter.

I don't know the reason but I assume your tables don't start on A1.

I did test this: when a table is not starting in A1 they are not seen as a table and so you can not seen in in the database.

Romke

[Villeroy]

Datasources from spreadsheet are problematic in version 3.2. Columns with mixed text and numbers give blank fields. Other versions convert the whole column to text in order to get some consistent database field.
Since spreadsheets can be rather chaotic having more than one table on one sheet, non-table data on a sheet, cross-tables, scratch pad ranges, ... you can apply a table filter. Additionally, you can define list ranges (Data>Define...) in order to declare a rectangle of cells as database-like table.

[seastick]

Thanks - it's the having empty A1 cells that seems to be the prolem.
Will the database update dynamically? If not how do I et the database to stay up to date with changes in the spreadsheet?
Thanks for your help.

[Villeroy]

Spreadsheets are difficult data sources. They have nothing in common with databases. This is how I can refresh a spreadsheet connection in version 3.3:
Save the spreadsheet to disk.
Make sure that the database document is not loaded.
Close the connection in the data source window (right-click>disconnect)
Reopen the table and see the changes.

Do yourself a favour:
Create a dedicated directory for dBase files.
Load your spreadsheet.
Isolate all database-like lists to separate sheets (copy&paste first row of column labels above data rows).
Save each list in the dBase file format to the dedicated directory. dBase export saves one worksheet at a time. Each dbf file will be shown as one editable table.
Connect your database file to the dBase directory.

Now you can forget about the database file and edit your data directly in the datasource window.

[RPG]

Hello

When you change to dbase file then this link can help you.

When you still want use spreadsheet then
for columns with mixed text and numbers you can do:
format the column as text and when you have a number start with a high comma

Romke