I’ve been developing a visual query builder / data drilling app for a couple of weeks and am looking for feedback on the desirability for such a tool. I had several goals when starting this but don’t want to get too far without soliciting feedback and ideas.
Initial Goals:
-
Provide a reasonable level of functionality in a visual query builder. I don’t want to account for massively complex sql because anyone writing such queries doesn’t need this.
-
Provide a drill down hierarchy where the user can build upon prior queries to get to the details.
-
The ability to provide non sql users with a saved master query ( a file ) which they can drop on a reporter applet which will then get connection credentials and run the output portion of the queries.
Here is a movie showing the early stages: VQB
The movie is a bit long as it attempts to show a typical workflow.
Thank you
Very nice! I always loved the quey builder in MS Access and this getting very close.
It looks very nice and, above all, intuitive to use, too. The only think I am a little dubious
about is the query result shown on a separated popup window. Have’t you tried to put the result window inside a tab panel among the bottom part of the main window and, if the case, run just the drill down result on a popup window ?
@Antonio Simone The result window is currently the only output. It is definitely a bit clunky but does allow me to test. The problem with embedding is the drill down levels are unlimited at each level (within practical limits) My plan moving forward is to have a tree hierarchy on the left of a single results window or a tab of the existing window which will allow for drilling through the report structure. It might make sense to allow any drill result to open hierarchically or as a new window. This would allow comparing results side by side.
@Alain Bailleul I’ve never used the MS Access one. I don’t mind typing sql but I do think better visually.
Thanks for responding.
Tree hierarchy would be very nice, useful and faster way to track the path where the user started from. Yes, visually is better, indeed; as for my experience, the large part of people I know who do not feel comfortable with Sql coding, create queries visually and otherwise they would not have the query done. And in this case the drill down capability would be a great added value.
@Kem Tekinay Thank you for your input 
[quote=93877:@Kem Tekinay]Fabulous.
- I don’t mind the second window for results, but perhaps put the query, either as SQL or plain, at the top so I can remember what I’m looking at? Even a pop-up when I point at something on that window would be good.[/quote]
There will definitely be some indication of what the heck you are looking at.
You can go back to any result window and drill into as many lines as you like. The downside now as you indicated is with so many windows you need to know what you are looking at.
The initial query is intended to be the seed. For instance you would put date ranges or whatever there and then carry them forward into your drilled queries.
Agreed. Important for comparative analysis. You can have as many base query windows as needed as well.
Easy to implement. Right now the queries are saved as JSON. The sql is a single node.
You can view and edit the SQL manually. Right now it will not update the visual query portion. I plan on allowing the use of the sql either way.
Eventually I hope to. This is early on and I’m constantly refactoring.
I cobbled together a drill down canvas. I haven’t attached it to the query builder project but am using the same dataset as a proof of concept. All data is lazy loaded so the sub column headings don’t align. It could take a long time to run and load all the possible query combinations so, for now, I’m going to leave it as is.
Short Video
[quote=94144:@Peter Fargo]I cobbled together a drill down canvas. I haven’t attached it to the query builder project but am using the same dataset as a proof of concept. All data is lazy loaded so the sub column headings don’t align. It could take a long time to run and load all the possible query combinations so, for now, I’m going to leave it as is.
Short Video[/quote]
That’s cool, and lazy loading is definitely the way to go.
Have you thought about a more Finder Column view format? Select a row to see the sub-query result in a right hand column? Select multiple rows to get a stacked column view on the right (ripe for printing if nicely formatted)?
Just something that popped into my head as a nice way to see details of a row without blowing out the height of the original result set with each selection.
@Ian Jones - Good point. The Chinook dataset is pretty small but I was noticing the negative effect of the starting queries running off the page. I’ll see what I can come up with. Thank you.
Nice.
Not sure if this is at all useful to you but dpriver makes an awesome Query cleaner. You might be able to leverage it for the SQL output.
http://www.dpriver.com/pp/sqlformat.htm
@Eduardo Gutierrez de Oliveira Thanks, I’ll check it out.
I’ll be looking for a few testers soon to see if this app is useful to others. I have a preliminary user manual here. I recommend downloading the pdf as it does have a table of contents which makes it easier to navigate.
I think I’ve got this far enough along to have some people beat it up and find the glaring issues. It’s hard to break out of your usage patterns so I’d like to get some new hands and eyes on it. If you are interested PM me.
Moved this to AddOns since it’s not part of Xojo.
@Peter Fargo : Any news or updates?
Someone ask me for a tool like this.
[quote=168164:@Bernardo Monsalve]@Peter Fargo : Any news or updates?
Someone ask me for a tool like this.[/quote]
There is a visual query builder in the free Valentina Studio, but most features you’ll want related to it are in Valentina Studio Pro.
Thanks @Lynn Fredricks I test with new version of valentina studio, is amazing. I’m talking with @Peter Fargo too. thanks.