How to display filtered Pivot table data on separate sheets?

Discuss the spreadsheet application

How to display filtered Pivot table data on separate sheets?

Postby eeigor » Sat Apr 17, 2021 9:29 am

Actually the question is asked in the topic. An example of a solution in Excel is here.
Ubuntu 18.04 LTS • LibreOffice 7.1.3.2 Community
eeigor
 
Posts: 134
Joined: Sun Apr 12, 2020 10:56 pm

Re: How to display filtered Pivot table data on separate she

Postby Villeroy » Sat Apr 17, 2021 1:05 pm

A grouped Base report does something like that with a nicer layout.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 29715
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to display filtered Pivot table data on separate she

Postby Villeroy » Sat Apr 17, 2021 3:59 pm

Here we go again.
------------
Select the source range including the column labels, call Data>Define Range... and give a name to the new database range. Make sure that the "column labels" option is checked under "More Options". A database range is a different kind of named range.
------------
File>New>Database...
Connect to existing database
Type: Spreadheet
Specify the spreadsheet file
Save the database document.
The Base document is connected to the spreadsheet document. Nothing has been copied, imported, converted. Your data are stored in the spreadsheet, not in the database document.
In the tables section you should see the name of the database range and when you open it, it looks like a database table.
Close it again, Right-click the table icon and call "Report Wizard".
Start with a simple tabular report, grouped by whatever category you wanted to split into separate sheets.
In the last step of the wizard choose "Edit report". It is not yet finished.
Click the blue group box and choose "Force new page: Before Section" in the properties dialog (View>Properties...).
Save and close the report design.
Save the containing database.
-----------------------------
Open the report. It is like a read-only text document generated from the database data (coming from a spreadsheet in this particular case).
If you grouped by "Person Name" it looks like:

Charly Chaplin
< table data >
----------- page break ----------
Buster Keaton
< table data >
----------- page break ----------
Stan Laurel
< table data >
----------- page break ----------

If you are not satisfied by the layout, just let the wizard create another one. Manual report design is a little bit tricky.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 29715
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to display filtered Pivot table data on separate she

Postby eeigor » Sat Apr 17, 2021 8:13 pm

@Villeroy, thank you. It will take me a while to figure out this solution.
Ubuntu 18.04 LTS • LibreOffice 7.1.3.2 Community
eeigor
 
Posts: 134
Joined: Sun Apr 12, 2020 10:56 pm

Re: How to display filtered Pivot table data on separate she

Postby Villeroy » Sat Apr 17, 2021 8:24 pm

I overlooked an important thing: You want to split the pivot table. My solution splits the source data of the pivot table. If your data were stored in a database, you could create the report from an aggregation query (a pivot is the same as an aggregation query). Base can not do aggregation queries with pseudo-databases that are lilnked to sheets.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 29715
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to display filtered Pivot table data on separate she

Postby eeigor » Sat Apr 17, 2021 11:00 pm

Everything worked out. A rare task. Otherwise, it might be worth writing a macro. Moreover, in my particular case, the pages had to be saved in separate files and sent out.
By the way, I first met the Base. I once knew MS Access very well.
Last edited by eeigor on Sat Apr 17, 2021 11:22 pm, edited 1 time in total.
Ubuntu 18.04 LTS • LibreOffice 7.1.3.2 Community
eeigor
 
Posts: 134
Joined: Sun Apr 12, 2020 10:56 pm

Re: How to display filtered Pivot table data on separate she

Postby Villeroy » Sat Apr 17, 2021 11:20 pm

I have written such a macro for Base reports. It changes a criteria value, runs a report, saves the report as separate Writer document,, changes criteria value, runs report, ...
viewtopic.php?f=100&t=94514
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 29715
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to display filtered Pivot table data on separate she

Postby Villeroy » Sun Apr 18, 2021 10:30 pm

eeigor wrote:Everything worked out. A rare task. Otherwise, it might be worth writing a macro. Moreover, in my particular case, the pages had to be saved in separate files and sent out.

OK, this may become an interesting new feature. The attached extension installs a Basic library "PivotSplitter". Before calling the Main routine, click the grey pivot cell which shows the name of the field to be grouped. This can be a page field, a column field or a row field.
The macro detects the pivot around the active cell, detects the field specified by the active cell, creates a subdirectory "PivotExport", hides all field items, unhides them one by one and copies for each one the entire sheet to a new spreadsheet document and saves it to the subdirectory under the item's name. Finally, it shows a message about how many files have been stored to which subdirectory.
Configuration: If you set cDirName on top of the module to "", the macro creates a timestamped subdirectory for each run.

The macro works somehow, BUT
Unhandled problems:
0) Ugly Basic code with a dispatch macro as workaround because I don't understand interface XTransferable.
1) Invalid file name characters in field item names.
2) Does someone know how to not add a new file to the recent docs list?
3) There are "special pivot items" with empty names and in case of numeric groups there are items named like "<0" or ">100". The macro does not handle them in any special way.
4) If you do not see the column field in question but a column field named "Data" representing the data fields, drag "Data" to row field position and the name of your column field will appear.
Pivot macros are a can of worms.
Attachments
PivotSplitter.oxt
(4.36 KiB) Downloaded 29 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 29715
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 10 guests