[Solved] Standard Filter data executed with Form Control box

Discuss the spreadsheet application

[Solved] Standard Filter data executed with Form Control box

Postby Kalculon » Wed Jul 19, 2017 10:20 am

Hello,

I need some advice in a chart I'm designing... I'm including it as an attachment.

The spreadsheet only uses two tabs: The 'Data' tab is used for Sales/Taxes/Service Fee info and 'Chart' reflects the respective stacked data and sum for Sales.

What I've done is to use Forms Control Check Boxes to activate or deactivate my data columns and then have it reflected in my chart automatically; this part works fine.

The part I'm having a bear of a time trying to figure out is how to pass the selected Start and End dates back to the 'Data' tab's Standard Filter, so that only the desired time-frame data is reflected in the chart automatically or even semi-automatically by clicking the Update Graph Button.

I would of course like to have a logical catch filter on the End Date so that it can never be before the Start date as well but even if this can't be solved easily, having the chart update on-the-fly is my main goal at this point.

I'm at a loss how to approach this problem and any help would really be appreciated.

Rob
Attachments
Test Chart.ods
(20.21 KiB) Downloaded 52 times
OpenOffice 4.13, Windows 7 Professional
Kalculon
 
Posts: 4
Joined: Wed Jul 19, 2017 10:14 am

Re: Standard Filter data executed with Form Control box date

Postby Villeroy » Wed Jul 19, 2017 2:29 pm

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26635
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Standard Filter data executed with Form Control box date

Postby Kalculon » Tue Jul 25, 2017 12:27 pm

Thanks for your post Villeroy. This doesn't seem to work for me; I've enabled macros but there are no pull down items in order to select and filter. I will keep playing with it. There is no easy way to import my selected dates into the filter and then re-query? Was hoping there was a simple way of resolving this but it appears this is not the case.
OpenOffice 4.13, Windows 7 Professional
Kalculon
 
Posts: 4
Joined: Wed Jul 19, 2017 10:14 am

Re: Standard Filter data executed with Form Control box date

Postby Villeroy » Tue Jul 25, 2017 5:22 pm

This is the most easy and flexible way, particularly because it does not depend on any macros.
Did you register the database document? menu:Tools>Options>Base>Databases. Add the downloaded PowerFilter.odb. Then load my spreadsheet.

Store categorised data in a database.
Use a small input form to store filter criteria in the same database. The "Filter" table in my example db stores filter criteria.
Create a query to get row sets with calculated data based on the entered criteria (a query is a "formula" which returns a whole table rather than one value).
Database row sets are always normalised. To create a not normalised cross table with one column per category for your chart, you can use a pivot table.

####################### HERE WE GO AGAIN #######################
Open my PowerFIlter.odb you have registered to work with stand-alone documents. We are going to modify it a little by mere copy&paste.
Go to the "Tables" sections and call menu:Tools>SQL...
Paste the following SQL script and execute it. It expands the "Filter" table by 3 columns and one row.
Code: Select all   Expand viewCollapse view
ALTER TABLE "Filter" ADD COLUMN BOOL1 BOOLEAN;
ALTER TABLE "Filter" ADD COLUMN BOOL2 BOOLEAN;
ALTER TABLE "Filter" ADD COLUMN BOOL3 BOOLEAN;
INSERT INTO "Filter" VALUES(3, Null, Null, Null, Null,'Chart Filter', True,True,True);

After getting a success message, close that dialog and call menu:View>Refresh Tables which notifies Base that the structure of the database has been changed by a command line tool.
-------------------------------------------------------------------------------------------------------------
menu:Insert>Query(SQL View) and paste the following SQL:
Code: Select all   Expand viewCollapse view
SELECT YEAR( "Data"."D" ) ||'-'|| MONTH( "Data"."D" ) AS "Month",
  CASEWHEN( "Filter"."BOOL1", SUM( "Data"."V" ), NULL ) AS "Pre-Tax",
  SUM( "Data"."V" ) * CASEWHEN( "Filter"."BOOL2", - 0.30, NULL ) AS "Taxes",
  SUM( "Data"."V" ) * CASEWHEN( "Filter"."BOOL3", - 0.20, NULL ) AS "Service Fees"
FROM "Data", "Filter"
WHERE "Filter"."FID" = 3
  AND ( "Filter"."D1" <= "Data"."D" OR "Filter"."D1" IS NULL )
  AND ( "Filter"."D2" >= "Data"."D" OR "Filter"."D2" IS NULL )
  AND ( "Filter"."INT2" <= "Data"."CID" OR "Filter"."INT2" IS NULL )
GROUP BY YEAR( "Data"."D" ), MONTH( "Data"."D" )
ORDER BY YEAR( "Data"."D" ), MONTH( "Data"."D" )

Save the new query as qChartFilter
Save and close the database.
-------------------------------------------------------------------------------------------------------------
Open the here attached document PowerFilter2.ods and try the red spreadsheet tab "Topic_89590".
Like the other filter forms, this one involves a one-line macro to refresh the import range after you finished criteria input.
I did not add a chart yet. But if you do so, you will notice that the chart range expands and shrinks automatically with the import range after you selected more or less months to display. Together with expanding calculation formulas, this is another thing you don't have to bother when using databases.
Attachments
PowerFilter2.ods
Expanded frontend for the database in topic 88516
(39.04 KiB) Downloaded 50 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26635
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Standard Filter data executed with Form Control box date

Postby Villeroy » Tue Jul 25, 2017 8:55 pm

[deleted previous answer about hiding columns because you don't need to hide any columns. your check boxes do the right thing already]
I added a very old but versatile filter macro to hide rows by means of an "advanced filter" (filter by criteria in cells).
I simplified your formulas a little bit and replaced the useless date controls with validated cell input.
Nevertheless, I'd always do such things with a database because the storage is more consistent and you avoid many problems with growing data sets.
Attachments
Test Chart3.ods
(24.03 KiB) Downloaded 44 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26635
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Standard Filter data executed with Form Control box date

Postby Kalculon » Wed Jul 26, 2017 7:43 am

Thank you Villeroy. I'm currently recovering post-op and will review all that you've posted shortly; this last chart with the included macro seems to be exactly what I needed.
OpenOffice 4.13, Windows 7 Professional
Kalculon
 
Posts: 4
Joined: Wed Jul 19, 2017 10:14 am

Re: Standard Filter data executed with Form Control box date

Postby Villeroy » Wed Jul 26, 2017 10:32 am

I forgot another Basic snippet which writes date control values to spreadsheet cells: https://forum.openoffice.org/en/forum/v ... 21&t=18714
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26635
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Standard Filter data executed with Form Control box date

Postby Kalculon » Mon Aug 07, 2017 11:11 am

Villeroy,

Just wanted to thank you again. I'm going to try incorporating the macro and changes into my spreadsheet; you've helped a great deal! If there are any questions I'll post them as need be.

:D
OpenOffice 4.13, Windows 7 Professional
Kalculon
 
Posts: 4
Joined: Wed Jul 19, 2017 10:14 am


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 14 guests