three table report

Classic Servoy
1

I want a report that shows for each project, all the child tasks – and for each task, all the child steps. Setting up the report in the step table and using two sub-summary parts (one for parent task and one for grandparent project) only returns projects and tasks that have steps. I need all projects and tasks whether there is a step associated with them or not.

  1. Does this kind of report need to done from the top down using portals or tab panels (which is better?) and using the stretch and shrink properties?

I tried doing this report with an SQL query:

SELECT id_project, project.name, task.name, step.name
FROM project, task, idea
WHERE project.id_project = task.key_project AND task.id_task = step.key_task
ORDER BY project.id_project, task.importance_order, step.importance_order

  1. This also returns only rows that have a step. Is there a different way to construct this query? I am not a query guru…

  2. A side note: I take it that Servoy does not support embedded SELECT statements? I tried to write the above statement as an embedded INNER JOIN and I couldn’t get it to work.

2

If you want a non editable report I’d use nested queries combined with HTML

example:

var HTML = “

” // create a variable that stores all data as you go along your loops

loop1 select PROJECTID, projectName from projects
{
_____HTML = HTML + “

”
___loop2 select TASKID, taskName from tasks where project_id = PROJECTID
_____{
__________HTML = HTML + “”
__________loop3 select stepName from steps where task_id = TASKID
__________{
_______________HTML = HTML + “”
__________}
_____}
}
HTML = HTML + “
”+projectName+“
”+taskName+“
”+stepName+“
”

controller.elements.myLabel.text = HTML
//place html in a label

advantages:

  • fast
  • flexible (you can build the wildest reports)
  • reports can be shown anywhere you want (placed in a label).
    -reuse this code per project record
    (select PROJECTID, projectName from projects where projectId = currentProjectId
  • throw in variables (as valuelists/checkboxes etc..) to let users change the queries
    (select PROJECTID, projectName from projects where projectId = currentProjectId and status =
  • html works fast, building fancy report layouts
    (no tedious positioning of columns, backgrounds etc…)
3

Finally sat down with my office door locked today and figured this out. Very very cool stuff.

Question: when a dataset is returned, is there a way to refer to a column by name? Or is number the only way at the moment?

Posting a jpeg of what I came up with, any code suggestions?

Thanks for the cool idea. It opens up a whole new world…

  • david
4

Looking good!

Basically the sky is the limit when using SQL combined with HTML.
And there are tons and tons of examples/tutorials/books on the internet and probably in the closest bookstore in your area.
One of Servoy’s advantages using industry standards. :lol:

Maybe this kind of scripting might look a bit scary at first sight,
but it’s not. In fact it’s fairly simple, compact and flexible.
(using subsummaries, you need to create forms, relations, create sorts etc.., and even then, a lot of reporting is just NOT possible with subsummaries)

small note:
HTML += “” is shorthand for
HTML = HTML + “”

5

Question: when a dataset is returned, is there a way to refer to a column by name? Or is number the only way at the moment?

you can also do this:

var maxReturedRows = 3;
var query = ‘select company_name, company_type from companies’;
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturedRows);

for(var i = 1 ; i<=dataset.getMaxRowIndex() ; i++)
{
dataset.rowIndex = i // go to next row
var name = dataset.company_name
var type = dataset.company_type
}