[Solved] Pivot table in BASE

Creating tables and queries

[Solved] Pivot table in BASE

Postby primuspaul » Thu Oct 29, 2015 6:30 am

I have an xls file with the following rows of data:

AREA SUBJECT DATE AMOUNT

AREA is the general area of a subject, which is more specific. It is also dated and there is an amount. I want to use BASE to link to the excel file and create a pivot table like this:

Code: Select all   Expand viewCollapse view
                                  Date 1                 Date 2                   Date 3
Area                                  AMOUNT           AMOUNT                AMOUNT
   Subject                            AMOUNT           AMOUNT                AMOUNT


Basically, it will list the areas, then give the subcategories of subjects, and list out the amount totals for year year.
Last edited by primuspaul on Fri Oct 30, 2015 3:39 am, edited 1 time in total.
OO 4.1.1 Windows 7 32bit
primuspaul
 
Posts: 6
Joined: Wed Oct 28, 2015 6:09 am

Re: Pivot table in BASE

Postby Villeroy » Thu Oct 29, 2015 1:40 pm

You can connect a registered Base document to your spreadsheet and then create pivot tables in other spreadsheet documents.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27760
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Pivot table in BASE

Postby primuspaul » Thu Oct 29, 2015 3:29 pm

Yes, but I'd like to learn to do it in OO BASE.
OO 4.1.1 Windows 7 32bit
primuspaul
 
Posts: 6
Joined: Wed Oct 28, 2015 6:09 am

Re: Pivot table in BASE

Postby Villeroy » Thu Oct 29, 2015 4:02 pm

Connect a Base document to some database engine which is able to produce pivot tables.
Base is not a database. It is a tiny set of tools to work with databases in the context of this office suite.
Base has no extra features beyond the capabilities of the underlying database plus the capabilities of the office documents that make up forms and reports.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27760
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Pivot table in BASE

Postby Sliderule » Thu Oct 29, 2015 4:16 pm

primuspaul wrote:Yes, but I'd like to learn to do it in OO BASE.

In that case, compose your Query, using GROUP BY for the rows you want on the left, and an appropriate, CASE WHEN . . END for each column you want displayed.

NOTE: Since, you have not indicated the database back-end you are using, the above comments are in relation ( database pun intended :lol: ) with HSQL. If you are using a different database back-end, use the appropriate syntax for that database engine. :mrgreen:

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1207
Joined: Thu Nov 29, 2007 9:46 am

Re: Pivot table in BASE

Postby Villeroy » Thu Oct 29, 2015 5:02 pm

Indeed, the content of a pivot table is just the same as an aggregation query but with the horizontal layout with categories in column fields is something most databases do not support.

The content of a pivot table in Calc is like this pseudo-SQL:
SELECT <all row and column fields>, functions(<data fields>)
FROM <list range>
WHERE <filter conditions>
GROUP BY <all row and column fields>
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27760
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Pivot table in BASE

Postby primuspaul » Thu Oct 29, 2015 5:37 pm

So what is the advantage of BASE? Why not just keep data like this in excel, which has the tools built-in for a pivot table?
OO 4.1.1 Windows 7 32bit
primuspaul
 
Posts: 6
Joined: Wed Oct 28, 2015 6:09 am

Re: Pivot table in BASE

Postby Sliderule » Thu Oct 29, 2015 5:45 pm

primuspaul wrote:So what is the advantage of BASE? Why not just keep data like this in excel, which has the tools built-in for a pivot table?

This is a forum for OpenOffice / LibreOffice Office Suite, NOT Microsoft Office, NOR Excel spreadsheet . :crazy:

Therefore, I suggest you ask your question in a Microsoft Excel forum, since, you are asking about Excel. :knock:

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1207
Joined: Thu Nov 29, 2007 9:46 am

Re: Pivot table in BASE

Postby primuspaul » Thu Oct 29, 2015 5:53 pm

Excuse me, I meant Calc, which also has the feature I believe.
OO 4.1.1 Windows 7 32bit
primuspaul
 
Posts: 6
Joined: Wed Oct 28, 2015 6:09 am

Re: Pivot table in BASE

Postby Villeroy » Thu Oct 29, 2015 6:16 pm

google "database vs spreadsheet"
http://igetit.net/newsletters/Y03_04/ssvdb.aspx
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27760
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 3 guests