[Solved] Pivot Table

[dreamquartz]

Hi All,

Have been fighting to set up a Pivot Table for a while, but with little luck.

I have a Query, leading to City and the Postal Codes in that City.
Need to determine the how many PC's there are in a City, how many are actually entered, and what the percentage if of the PC in relation to the City.

Attached I added a simple Calc Sheet to provide an example.

Hope someone can give some insight.

Dream

[jrkrideau]

How? I was just going to say I didn't think a pivot table was flexible enough.

[dreamquartz]

How? I was just going to say I didn't think a pivot table was flexible enough.

Please see the moved question to the Base Forum for follow up.
Because it is moved, it has been marked as [SOLVED] here.

Dream

[Villeroy]

There seems to be a bug. I append a calculated field =ISBLANK($C2) to the source table and add it to the pivot table as a column field. It shows the count of FALSE values only (not blank). It never shows any results for the TRUE category (blank). Same with a calculated text field =IF(ISBLANK(C2);"Yes";"No").
Filed issue: https://bz.apache.org/ooo/show_bug.cgi?id=126718

[dreamquartz]

There seems to be a bug. I append a calculated field =ISBLANK($C2) to the source table and add it to the pivot table as a column field. It shows the count of FALSE values only (not blank). It never shows any results for the TRUE category (blank). Same with a calculated text field =IF(ISBLANK(C2);"Yes";"No").
Filed issue: https://bz.apache.org/ooo/show_bug.cgi?id=126718

I closed this under Calc and moved it to the Base forum.
I am looking for a solution, where NOT having to copy the result set of the Base Query into Calc, and then use the Pivot Table, but simply use the Pivot Table with the Query as a source. Have no problem tweaking any Query .

Dream.

[jrkrideau]

Ah yes, BASE sounds like not a bad idea. Otherwise I suspect it is fairly easy in Python or [url=http://www.-project.org/]R[/url] but it did not look like an obvious Pivot Table problem

[Villeroy]

Congratulations for your decision to work with Base. Nevertheless, pivot tables can serve as an excellent reporting tool for database records as well.

My filed issue about pivot tables is invalid. I had chosen the wrong data field.
For the records I'll attach a solution with a pivot that comes quite close to the wanted result.

Another tip: With MRI as a "helper GUI" it is possible to rename existing pivot field labels. Navigate to the right DataPilotTable on the right sheet, then choose the right field from the collection of DataFields, ColumnFields or RowFields, switch from Get mode to Set mode and call method setName(). This seems to not actually rename the field (getName() returns the same as before) but the label changes anyway.