Dynamic Pivot Table(s) issues

[maya1]

Hi,

Win10 LO 7.2.7.2 HSQL 2.61

From memory Access 2002 introduced some nice pivot tables and pivot charts.

You may have started off with one pivot listing sales by years e.g

1998 1999 2000 2001 etc

once you doubled clicked the columnheader of a year, the table expanded to show quarters,

once you double clicked a quarter heading it opened up the associated month, ….

Now HSQL does not support pivots but one can do crosstabs… I would like to simulate the above dynamic but have hit a couple of issues.

First I believe that in crosstabs pretty much everything needs to be hardcoded such as future years, not a problem per se however you would end up with lots of zeros in the future.

So the question is can a column in a tablecontrol be hidden if all rows are 0 ?

Secondly, no problem with year, quarters, months queries as these are all constant quantities, but how would I set up a crosstab for number of days of a specific months as they tend to vary 28,29,30,31

Thirdly is there a way to capture the column headers caption to a variable to pass on to a query to open the relevant pivot in either a form or a dialog.

At times I use a little + button as shown below. Sample db attached.

PivotFake.odb (26.5 KB)

Thanks for any suggestions

[Villeroy]

[Tutorial] Using registered datasources in Calc

[Ratslinger]

Hello,
Too many questions for a single post.

Column titles come from the control. If the column is selected here is the code to get the Label (majority of code generated my MRI):

Sub getTableColTitle
  Dim oDrawPage As Variant
  Dim oForms As Variant
  Dim oObj1 As Variant
  Dim oObj2 As Variant
  Dim oSelection As Variant
  Dim sLabel As String
  oDrawPage = ThisComponent.getDrawPage()
  oForms = oDrawPage.getForms()
  oObj1 = oForms.getByName("MainForm")
  oObj2 = oObj1.getByName("MainForm_Grid")
  oSelection = oObj2.getSelection()
  sLabel = oSelection.Label
   Print sLabel
  
End Sub

Example:

.
Also, there is a form event Fill parameters where you can insert code for the automated filling of parameter(s).

See → Fill out a SQL query parameter by running a Event Macro "Fill Parameters" on Subform - #3 by Ratslinger

and

Meta data about query parameters

[maya1]

Thanks for the code and links, much appreciated.

[maya1]

Thank you Villeroy, however Calc is not an option as problem with retaining formats on refresh.

[cpb]

could you be overcomplicating this?
it’s not a simple matter to move rows to columns no matter which database is used.
each year will need to be specifically referenced including future years for which data will not currently exist.

you may wish to change the sort order but here is a simple query which shows all required info.
I penned it using embedded HSQL whilst keeping in mind that you use HSQL 2.6.1, it may require direct mode.

select
	c."client", year(d."date") yr, sum(d."amount") total,
	sum(case when quarter(d."date") = 1 then d."amount" end) q1,
	sum(case when quarter(d."date") = 2 then d."amount" end) q2,
	sum(case when quarter(d."date") = 3 then d."amount" end) q3,
	sum(case when quarter(d."date") = 4 then d."amount" end) q4
from
	"tblDummy" d
join
	"tblClient" c on d."cid" = c."cid"
group by year(d."date"), c."client"
order by 2 desc, 1

here is the output:

[maya1]

Wow, thank you for that !

[Villeroy]

@maya1

Thank you Villeroy, however Calc is not an option as problem with retaining formats on refresh.

Calc has a lot more formatting capabilities than Base.

[maya1]

Hello,

macro works well ifi there is a selection, however if nhothing is selected it throws an error

How can I exit the sub gracefully on no selection?
You guys are great, almost there, all functionalty on one form with the exception of the days drill down which will be just a dialog listing existing days vertically.

[Ratslinger]

@maya1
Error handling → On Error GoTo ... Resume Statement
.
Example:

Sub getTableColTitle
  Dim oDrawPage As Variant
  Dim oForms As Variant
  Dim oObj1 As Variant
  Dim oObj2 As Variant
  Dim oSelection As Variant
  Dim sLabel As String
  oDrawPage = ThisComponent.getDrawPage()
  oForms = oDrawPage.getForms()
  oObj1 = oForms.getByName("MainForm")
  
  oObj2 = oObj1.getByName("MainForm_Grid")
On Error GoTo MyExit
  oSelection = oObj2.getSelection()
  sLabel = oSelection.Label
   Print sLabel
   Exit Sub
MyExit:
Print "Nothing Selected"
End Sub

Edit: Moved On Error position. Where you set can make a difference as to what is causing the error(s). Can have multiple in a routine.

[maya1]

Thanks , was trying if not, if empty…

[maya1]

Thanks to the generous help of this forums contributors I ve managed to reach my goal. For those interested in something similar the completed sample is attached. Of course it could be further enhanced by filtering rows and differing sort options.

PivotFake2.odb (27.4 KB)