Reporting using relationships

[rochard]

I am designing a summary report based on three tables, tblMembers, tblReservation and tblReservationRoom. tblMembers is related to tblReservation by the field MemberID; tblReservation is related to tblReservationRoom by the field ReservationID. tblReservationRoom is not related to tblMembers.

Each members has one or more records in tblReservation and each one of those records has one or more records in tblReservationRoom; each of these records is for one hotel for a date period.

What I want to do is two fold.

Firstly I want to create a report that starts with the Member Name and groups all Reservations for that Member together and then underneath that shows all the room reservations. Secondly I want to create a report summarized by Member and then by the Hotel.

I seem to remember reading somewhere that all this is possible and I'm sure it is but cannot find anything in the documentation covering how to do it.

Any help gratefully received.

[maarten]

Here's a sample solution that should get you started.
note: for html design of reports it's easier to use a good html editor like
eg dreamweaver, and then copy paste the html code into Servoy.

Secondly I want to create a report summarized by Member and then by the Hotel.

wasn't sure what you ment here. hotelrooms per member?
Anyway I'm sure you can do what ever you wan't after studying
the sample solution.
For those of you who aren't very familiar with SQL;
Bob Cusick is building a great SQL generator
We'll do a post as soon as it's available.
Let me know if you need more explanation.

[rochard]

Maarten

Thanks for the sample solution which I am studying now and which demonstrates what I am trying to achieve.

What I was hoping to be able to do was design a non html report as a form with several subsummary parts and relate the room reservations to the members table even though there is field that links the two files directly.

It seems, somewhat, logical since value lists can display related values two relationships deep to be able to do something similar on a form layout using sub summary parts.

Is this possible or is the only way to do it as an html report? Thanks again.

[maarten]

not possible.
summary reports only go 1 relation deep.
(unless you start using calc fields like in Filem.)

I'll ask Jan Blok to comment wether multi level summaries
would be technically possible in future versions.

Note:
It's really worth while to try and get familiar with the SQL/HTML combination.
-much faster performance
-much more freedom
-very portable (place reports on any form you like as they are attached to a global or label/button)
-record related reporting(browse through records and see report change based on the selected record (eg. browse through member records and see a view of their reservations/rooms)

[rochard]

Thanks, Maarten. I'll do as you suggest although the html reports I've seen always look pretty ugly and I like reports to look really good.

[maarten]

Well, basically you can design anything with HTML.
Try using an HTML editor like Dreamweaver, where you can edit html like as if you 're working with a text editor.
(it wil handle all code in background for you.)
create cascading style sheets, with predefined fonts, borders etc.


NOTE:
We do experience problems with html borders.
Currently, the html renderer only supports 3D borders,
but this is going to be fixed in the near future.

[Harjo]

We do experience problems with html borders.
Currently, the html renderer only supports 3D borders,
but this is going to be fixed in the near future.

That would be really nice!

[pbakker]

In my experiences with HTML within Servoy, I noticed more HTML functions do not always work, for example, changing the background when hovering over a link.

Can we expect only a fix for the border issue, or and improved HTML renderer with more functions?

Paul

[rochard]

Here's a sample solution that should get you started.
note: for html design of reports it's easier to use a good html editor like
eg dreamweaver, and then copy paste the html code into Servoy.

Secondly I want to create a report summarized by Member and then by the Hotel.

wasn't sure what you ment here. hotelrooms per member?
Anyway I'm sure you can do what ever you wan't after studying
the sample solution.
For those of you who aren't very familiar with SQL;
Bob Cusick is building a great SQL generator
We'll do a post as soon as it's available.
Let me know if you need more explanation.

Thanks for the sample solution which makes sense, kind of. I've been trying for the last couple of days to modify the method to include addtional columns in the report. For example, in your method you have:

var roomQuery = "select hotel from rooms where reservation_id ="+reservDataset[1];
var roomDataset = databaseManager.getDataSetByQuery(controller.getServerName(), roomQuery, null, maxReturedRows);
for( var k = 1 ; k <= roomDataset.getMaxRowIndex() ; k++ )
{
roomDataset.rowIndex = k;
HTML += '<TR bgcolor="#ffff33"><TD></TD> <TD></TD> <TD align=right> '+roomDataset[1]+'</TD></TR>';
}
}
HTML += '</TABLE>';

globals.htmlReport = HTML;
forms.showReports.controller.show()

How do you include other columns in the html table? For example, let's say that we want to include a column called roomPrice from the rooms table. If you could show how this is done, then I'll be able, I hope, to figure out the rest. Many thanks.

[maarten]

1) extend your SQL query
2) extend your HTML code

var roomQuery = "select hotel, roomprice from rooms where reservation_id ="+reservDataset[1];
var roomDataset = databaseManager.getDataSetByQuery(controller.getServerName(), roomQuery, null, maxReturedRows);
for( var k = 1 ; k <= roomDataset.getMaxRowIndex() ; k++ )
{
roomDataset.rowIndex = k;
HTML += '<TR bgcolor="#ffff33">'+
'<TD></TD> '+ // blank
'<TD></TD>'+ //blank
'<TD align=right> '+roomDataset[1]+'</TD>'+ //refers to hotel (1st column in query)
'<TD align=right> '+roomDataset[2]+'</TD>'+//refers to roomprice (2nd column in query)
'</TR>'
}

[rochard]

Maarten, that's perfect. I was close but missing how to extend the dataset. Thank you a million times over.

1) extend your SQL query
2) extend your HTML code

var roomQuery = "select hotel, roomprice from rooms where reservation_id ="+reservDataset[1];
var roomDataset = databaseManager.getDataSetByQuery(controller.getServerName(), roomQuery, null, maxReturedRows);
for( var k = 1 ; k <= roomDataset.getMaxRowIndex() ; k++ )
{
roomDataset.rowIndex = k;
HTML += '<TR bgcolor="#ffff33">'+
'<TD></TD> '+ // blank
'<TD></TD>'+ //blank
'<TD align=right> '+roomDataset[1]+'</TD>'+ //refers to hotel (1st column in query)
'<TD align=right> '+roomDataset[2]+'</TD>'+//refers to roomprice (2nd column in query)
'</TR>'
}

[rochard]

var roomQuery = "select hotel, roomprice from rooms where reservation_id ="+reservDataset[1];

'<TD align=right> '+roomDataset[1]+'</TD>'+ //refers to hotel (1st column in query)
'<TD align=right> '+roomDataset[2]+'</TD>'+//refers to roomprice (2nd column in query)
'</TR>'
}

How and where would you format roomprice so that it displays as currency? TIA.

[maarten]

roomDataset[2].toFixed(2) //2 dec. fixed

notes:
-toFixed is a method that can be found in the Method editor (JSLib node>Math)
roomDataset[2] is a javascript object. You can attach all kinds of methods to an object in order to extract various kind of results. (date, string and math functions)
- if your working with large queries (lots of columns) you can make your code more readable by declaring variables first:
var room = roomDataset[1]
var price = roomDataset[2]
'<TD align=right> '+room+'</TD>
'<TD align=right> '+price.toFixed(2)+'</TD>
etc...