Base Re: [libreoffice-users] Calc and datapilot

________________________________
From: Simon Cropper <scropper@botanicusaustralia.com.au>
To: users@libreoffice.org
Sent: Wed, 27 April, 2011 11:00:22
Subject: Re: [libreoffice-users] Calc and datapilot

On 27/04/11 18:18, Cor Nouws wrote:
> Hi,
>
> JZ1982 wrote (23-04-11 23:43)
> > First of all I'm new to LibreOffice and i'm kind of a numbskull if it
> > comes
> > to the more advanced options within this software.
> >
> > What I'm trying to achieve is the following, I've got data consisting of
> > "code" "date" "text" "value" which I keep journalizing. I want to
> > manipulate
> > this data in a different sheet in a manner that I want the whole rows
> > parsed
> > to the different sheet depending on which "code" and "date" I'm
> > looking for.
> >
> > See the following picture:
> >
> > [IMG]http://i54.tinypic.com/20ubpdt.jpg[/IMG]
> >
> > I've been toying aroudn with DataPilot now for quite some time and I can
> > sort nicely on "code" but I fail to parse entire rows like I want to.
>
> Getting the result as on the right side of your picture, can easily be
> done with the default filter.
> Data > Filter > Default
> Then filter on code 402 and copy/paste the result to a different place,
> when needed.
>
> Hmm, but is it this what you want to achieve?
>
>
> Kind regards,
> Cor

Hi,

I don't think you can use Calc in the way you invision.

Some options exist...

1. Create a macro that sorts the list. This is not dynamic.
2. Create a duplicate of the sheet containing the core data. Use VLOOKUP to 
dynamically duplicate the sheet. Then create a filter on each duplicate sheet. 
Each duplicate sheet is filtered based on a particular 'volgnummer'. Usually I 
put this value at the top of the file as a header. This works really well with 
small to medium datsets but can be slow with larger datasets.
3. For larger datasets consider storing in a database then use Base to import 
just the 'code' lines you want to each sheet. This is a bit more complicated and 
less portable, depending on the database used.

Cheers Simon


Hi,
I recommend using Base for this.  You are really trying to use Calc as a 
database; square-peg, round-hole.  Proper database programs pull off this type 
of report really easily and it's even difficult to stop it producing things like 
this.  With Calc (or any other spreadsheet program) you will get into all sorts 
complicated expressions that may or may not be compatible with other programs.  
Take the easier route and use Base even if the table is quite small at this 
stage.

Base seems to prefer to have external tables to work from and can read the Calc 
spreadsheet as a table so all the programs will be happy to work with what you 
have already.  Access (the MS database program) can also use external tables but 
Base is likely to be better.  


Sadly we don't have many database experts in LibreOffice at the moment (unless 
thousands joined while in was on the boat) but the OpenOffice forums seem happy 
to deal with any questions.  Just say Base 3.3.2 or whatever rather than 
creating an issue about whether it is OOo or LO.  The version numbers give a 
good clue to the right people without their bosses being any the wiser.

Good luck and regards from
Tom :)

-- 
Unsubscribe instructions: E-mail to users+help@libreoffice.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/www/users/
All messages sent to this list will be publicly archived and cannot be deleted