# three table report

**URL:** <https://forum.servoy.com/t/three-table-report/356>\
**Category:** Classic Servoy\
**Created:** [September 24, 2003, 6:17pm UTC](https://forum.servoy.com/t/three-table-report/356 "2003-09-24T18:17:54Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![david](https://avatars.discourse-cdn.com/v4/letter/d/aeb1de/32.png) [@david](https://forum.servoy.com/u/david)\
**Post date:** [September 24, 2003, 6:17pm UTC](https://forum.servoy.com/t/three-table-report/356/1 "2003-09-24T18:17:54Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![maarten](https://avatars.discourse-cdn.com/v4/letter/m/ecccb3/32.png) [@maarten](https://forum.servoy.com/u/maarten)\
**Post date:** [September 25, 2003, 9:39pm UTC](https://forum.servoy.com/t/three-table-report/356/2 "2003-09-25T21:39:25Z")

</div>

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…)

---

<div class="post-metadata">

**Author:** ![david](https://avatars.discourse-cdn.com/v4/letter/d/aeb1de/32.png) [@david](https://forum.servoy.com/u/david)\
**Post date:** [October 1, 2003, 5:14pm UTC](https://forum.servoy.com/t/three-table-report/356/3 "2003-10-01T17:14:06Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![maarten](https://avatars.discourse-cdn.com/v4/letter/m/ecccb3/32.png) [@maarten](https://forum.servoy.com/u/maarten)\
**Post date:** [October 1, 2003, 6:48pm UTC](https://forum.servoy.com/t/three-table-report/356/4 "2003-10-01T18:48:15Z")

</div>

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:]( "Laughing")

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 + “”

---

<div class="post-metadata">

**Author:** ![maarten](https://avatars.discourse-cdn.com/v4/letter/m/ecccb3/32.png) [@maarten](https://forum.servoy.com/u/maarten)\
**Post date:** [October 1, 2003, 10:50pm UTC](https://forum.servoy.com/t/three-table-report/356/5 "2003-10-01T22:50:02Z")

</div>

> 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  
}