# Reporting using relationships **URL:** \ **Category:** Classic Servoy\ **Created:** [May 13, 2004, 6:10pm UTC](https://forum.servoy.com/t/reporting-using-relationships/1888 "2004-05-13T18:10:10Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![rochard](https://avatars.discourse-cdn.com/v4/letter/r/e9c0ed/32.png) [@rochard](https://forum.servoy.com/u/rochard)\ **Post date:** [May 13, 2004, 6:10pm UTC](https://forum.servoy.com/t/reporting-using-relationships/1888/1 "2004-05-13T18:10:10Z")
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. ---
**Author:** ![maarten](https://avatars.discourse-cdn.com/v4/letter/m/ecccb3/32.png) [@maarten](https://forum.servoy.com/u/maarten)\ **Post date:** [May 13, 2004, 8:06pm UTC](https://forum.servoy.com/t/reporting-using-relationships/1888/2 "2004-05-13T20:06:46Z")
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 ![:)]( "Smile") We’ll do a post as soon as it’s available. Let me know if you need more explanation. ---
**Author:** ![rochard](https://avatars.discourse-cdn.com/v4/letter/r/e9c0ed/32.png) [@rochard](https://forum.servoy.com/u/rochard)\ **Post date:** [May 13, 2004, 10:09pm UTC](https://forum.servoy.com/t/reporting-using-relationships/1888/3 "2004-05-13T22:09:28Z")
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. ---
**Author:** ![maarten](https://avatars.discourse-cdn.com/v4/letter/m/ecccb3/32.png) [@maarten](https://forum.servoy.com/u/maarten)\ **Post date:** [May 13, 2004, 11:15pm UTC](https://forum.servoy.com/t/reporting-using-relationships/1888/4 "2004-05-13T23:15:09Z")
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) ---
**Author:** ![rochard](https://avatars.discourse-cdn.com/v4/letter/r/e9c0ed/32.png) [@rochard](https://forum.servoy.com/u/rochard)\ **Post date:** [May 14, 2004, 3:44am UTC](https://forum.servoy.com/t/reporting-using-relationships/1888/5 "2004-05-14T03:44:50Z")
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. ---
**Author:** ![maarten](https://avatars.discourse-cdn.com/v4/letter/m/ecccb3/32.png) [@maarten](https://forum.servoy.com/u/maarten)\ **Post date:** [May 14, 2004, 8:31am UTC](https://forum.servoy.com/t/reporting-using-relationships/1888/6 "2004-05-14T08:31:15Z")
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. ---
**Author:** ![Harjo](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/harjo/32/4873_2.png) [@Harjo](https://forum.servoy.com/u/Harjo)\ **Post date:** [May 14, 2004, 9:28am UTC](https://forum.servoy.com/t/reporting-using-relationships/1888/7 "2004-05-14T09:28:27Z")
> 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! ![:)]( "Smile") ---
**Author:** ![pbakker](https://avatars.discourse-cdn.com/v4/letter/p/a4c791/32.png) [@pbakker](https://forum.servoy.com/u/pbakker)\ **Post date:** [May 15, 2004, 8:56am UTC](https://forum.servoy.com/t/reporting-using-relationships/1888/8 "2004-05-15T08:56:33Z")
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 ---
**Author:** ![rochard](https://avatars.discourse-cdn.com/v4/letter/r/e9c0ed/32.png) [@rochard](https://forum.servoy.com/u/rochard)\ **Post date:** [May 15, 2004, 8:06pm UTC](https://forum.servoy.com/t/reporting-using-relationships/1888/9 "2004-05-15T20:06:40Z")
> 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 ![:)]( "Smile") > 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 += ‘ ‘+roomDataset[1]+’’; > } > } > HTML += ‘’; > > 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. ---
**Author:** ![maarten](https://avatars.discourse-cdn.com/v4/letter/m/ecccb3/32.png) [@maarten](https://forum.servoy.com/u/maarten)\ **Post date:** [May 16, 2004, 3:22pm UTC](https://forum.servoy.com/t/reporting-using-relationships/1888/10 "2004-05-16T15:22:27Z")
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 += ‘’+ > ' '+ // blank > ‘’+ //blank > ‘ ‘+roomDataset[1]+’’+ //refers to hotel (1st column in query) > ‘ ‘+**roomDataset[2]**+’’+//refers to roomprice (2nd column in query) > ‘’ > } ---
**Author:** ![rochard](https://avatars.discourse-cdn.com/v4/letter/r/e9c0ed/32.png) [@rochard](https://forum.servoy.com/u/rochard)\ **Post date:** [May 16, 2004, 8:07pm UTC](https://forum.servoy.com/t/reporting-using-relationships/1888/11 "2004-05-16T20:07:09Z")
Maarten, that’s perfect. I was close but missing how to extend the dataset. Thank you a million times over. > 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 += ‘’+ > > ' '+ // blank > > ‘’+ //blank > > ‘ ‘+roomDataset[1]+’’+ //refers to hotel (1st column in query) > > ‘ ‘+**roomDataset[2]**+’’+//refers to roomprice (2nd column in query) > > ‘’ > > } ---
**Author:** ![rochard](https://avatars.discourse-cdn.com/v4/letter/r/e9c0ed/32.png) [@rochard](https://forum.servoy.com/u/rochard)\ **Post date:** [May 16, 2004, 11:27pm UTC](https://forum.servoy.com/t/reporting-using-relationships/1888/12 "2004-05-16T23:27:43Z")
> var roomQuery = “select hotel, **roomprice** from rooms where reservation\_id =”+reservDataset[1]; > > ‘ ‘+roomDataset[1]+’’+ //refers to hotel (1st column in query) > ‘ ‘+**roomDataset[2]**+’’+//refers to roomprice (2nd column in query) > ‘’ > } How and where would you format roomprice so that it displays as currency? TIA. ---
**Author:** ![maarten](https://avatars.discourse-cdn.com/v4/letter/m/ecccb3/32.png) [@maarten](https://forum.servoy.com/u/maarten)\ **Post date:** [May 17, 2004, 7:15am UTC](https://forum.servoy.com/t/reporting-using-relationships/1888/13 "2004-05-17T07:15:22Z")
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] ' ‘+room+’ ' ‘+price.toFixed(2)+’ etc…