(UTC)
  Lantica User Forum › Lantica Discussions › Lantica Discussion › Reports Across Databases
(Moderator: Forum Admin)
‹ Previous Topic | Next Topic ›
Page Index Toggle Pages: 1 Send Topic Send Topic Print Print
Hot Topic (More than 10 Replies) Reports Across Databases (Read 4365 times)
MP
Full Member
***
Offline



Posts: 104
Joined: Sep 3rd, 2007
Reports Across Databases
Oct 24th, 2007 at 4:59am
Print Post Print Post  
My Sesame application is structured using entity/relationship design practices.  Each database typically represents a single entity.  Foreign key relationships have been built programatically.  Without debating the reasons for doing this (which would lead to a long discussion about how to ram the round Sesame flat-file peg into the RDBMS square hole), I'd like to know if there is any way to use the Reports functionality to collect data from multiple databases.  I understand that if there isn't, that I can write all my reports from scratch using PrintString, but I'd rather not be burdened with that.

Here's an example.  Suppose you have three databases, Classrooms, Classes, and Teachers.  A Teacher teaches multiple Classes, and a Classroom has multiple Classes.  To relate all of these, you setup the Classes database to have foreign keys to the Classrooms and Teachers databases.  Given this scenario, is it possible to use the Reports feature in Sesame to print a Class schedule that includes both Teacher and Classroom information?
  
Back to top
 
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Reports Across Databases
Reply #1 - Oct 24th, 2007 at 11:46am
Print Post Print Post  
1. Sesame is not flat file. We don't have JOIN. We do have relationships. 

2. Yes, you can do this with X-Commands.

If you want more information, more specifics about the format you want to produce would be helpful. If your question is purely a theoretical exercise using the classic Teachers/Classes/Classrooms problem let me know and I'll just pick an output format.
  
- Hammer
The plural of anecdote is not data.
Back to top
WWW WWW  
IP Logged
 
MP
Full Member
***
Offline



Posts: 104
Joined: Sep 3rd, 2007
Re: Reports Across Databases
Reply #2 - Oct 24th, 2007 at 12:23pm
Print Post Print Post  
Hammer wrote on Oct 24th, 2007 at 11:46am:
1. Sesame is not flat file. We don't have JOIN. We do have relationships.

I'd call it a flat-file/RDBMS hybrid then, one which behaves far more like a flat-file system than a RDBMS system.

Hammer wrote on Oct 24th, 2007 at 11:46am:
2. Yes, you can do this with X-Commands.

What I don't follow is how to implement the X-Commands efficiently.  If I understand how reports work, there's only a Global Code section which runs once at the start of the report.  Then, as each row is generated, you could call the necessary X-Command in the On Print event.  My normal instinct would be to create one XResultSet per row and then share that data across all cells in the row.  But all I see is the ability for each cell to do its own lookup.

Quote:
If you want more information, more specifics about the format you want to produce would be helpful. If your question is purely a theoretical exercise using the classic Teachers/Classes/Classrooms problem let me know and I'll just pick an output format.

To take my theoretical example, let's say I want to print the following columns:

Class Name (from Classes)
Class Description (from Classes)
Teacher First Name (from Teachers)
Teacher Last Name (from Teachers)
Classroom Number (from Classrooms)
Classroom Floor (from Classrooms)
Classroom Seats (from Classrooms)

As the report is running through the set of Classes to print, does it have to do five separate lookups per row or can you get it do to only two?
  
Back to top
 
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Reports Across Databases
Reply #3 - Oct 24th, 2007 at 1:10pm
Print Post Print Post  
MP wrote on Oct 24th, 2007 at 12:23pm:
As the report is running through the set of Classes to print, does it have to do five separate lookups per row or can you get it do to only two?


You can just do two. One for Teachers. One for Classrooms. It would look something like this (might need syntax check):

ClassID :: On Print
Code
Select All
#include "sbasic_include.sbas"

var vRSHandle as Int
var vClassrooms as String
var vTemp1 as String
var vTemp2 as String
var vTemp3 as String
var vLoop as Int
var vCount as Int

	// Populate Teachers List
	vTemp1 = ""
	vTemp2 = ""
	vRSHandle = @XResultSetSearch(@FN, "Teachers", SEARCH_MODE_AND, SEARCH_SYNTAX_QA, "!ClassID=" + @Str(ClassID))
	if(vRSHandle > -1)
	{
		vCount = @XResultSetTotal(vRSHandle)
		For vLoop = 1 To vCount
			XResultSetCurrentPosition(vRSHandle, vLoop)
			vTemp1 = vTemp1 + @XResultSetValue(vRSHandle, "FirstName")
			If (vLoop < vCount) vTemp1 = vTemp1 + @Newline()
			vTemp2 = vTemp2 + @XResultSetValue(vRSHandle, "LastName")
			If (vLoop < vCount) vTemp2 = vTemp2 + @Newline()
		Next
		XResultSetClose(vRSHandle)
	}
	TeacherFirst = vTemp1
	TeacherLast = vTemp2

	// Populate Classrooms List
	vTemp1 = ""
	vTemp2 = ""
	vTemp3 = ""
	vRSHandle = @XResultSetSearch(@FN, "Classrooms", SEARCH_MODE_AND, SEARCH_SYNTAX_QA, "!ClassID=" + @Str(ClassID))
	if(vRSHandle > -1)
	{
		vCount = @XResultSetTotal(vRSHandle)
		For vLoop = 1 To vCount
			XResultSetCurrentPosition(vRSHandle, vLoop)
			vTemp1 = vTemp1 + @XResultSetValue(vRSHandle, "ClassroomNumber")
			If (vLoop < vCount) vTemp1 = vTemp1 + @Newline()
			vTemp2 = vTemp2 + @XResultSetValue(vRSHandle, "Floor")
			If (vLoop < vCount) vTemp2 = vTemp2 + @Newline()
			vTemp3 = vTemp3 + @XResultSetValue(vRSHandle, "Seats")
			If (vLoop < vCount) vTemp3 = vTemp3 + @Newline()

		Next
		XResultSetClose(vRSHandle)
	}
	ClassroomNumber = vTemp1
	Floor = vTemp2
	Seats = vTemp3


Obviously, there are other choices for exactly how to skin this cat, but this should give you the basic idea.
  
- Hammer
The plural of anecdote is not data.
Back to top
WWW WWW  
IP Logged
 
MP
Full Member
***
Offline



Posts: 104
Joined: Sep 3rd, 2007
Re: Reports Across Databases
Reply #4 - Nov 21st, 2007 at 9:54pm
Print Post Print Post  
What about sorting?  Is there a way to sort on these derived columns?
  
Back to top
 
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Reports Across Databases
Reply #5 - Nov 21st, 2007 at 10:47pm
Print Post Print Post  
MP wrote on Nov 21st, 2007 at 9:54pm:
What about sorting?  Is there a way to sort on these derived columns?


You can sort within each ClassId by using XResultSetSort before entering your iteration loop.
  
- Hammer
The plural of anecdote is not data.
Back to top
WWW WWW  
IP Logged
 
MP
Full Member
***
Offline



Posts: 104
Joined: Sep 3rd, 2007
Re: Reports Across Databases
Reply #6 - Nov 21st, 2007 at 10:54pm
Print Post Print Post  
Hammer wrote on Nov 21st, 2007 at 10:47pm:
You can sort within each ClassId by using XResultSetSort before entering your iteration loop.

Yeah, but can I sort the entire report based on the derived column (or in the example, sort the list of all Classes based on the value in the TeacherLast column)?
  
Back to top
 
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Reports Across Databases
Reply #7 - Nov 21st, 2007 at 11:07pm
Print Post Print Post  
MP wrote on Nov 21st, 2007 at 10:54pm:
Hammer wrote on Nov 21st, 2007 at 10:47pm:
You can sort within each ClassId by using XResultSetSort before entering your iteration loop.

Yeah, but can I sort the entire report based on the derived column (or in the example, sort the list of all Classes based on the value in the TeacherLast column)?


Since the value in the derived columns is actually an assembly of the values in several teacher records, this isn't making sense to me. I would think a report sorted by Teacher would be a different report based on the Teachers records as opposed to the Classes records.

Again, we are not designed for super-normalization. We are designed for people who would really prefer to live their entire lives without ever finding out the difference between third and fourth form normal, or that there even are such concepts.  The solutions to requirements are usually going to be found farther away from your base data structures than you are accustomed to with big engines like Oracle.
  
- Hammer
The plural of anecdote is not data.
Back to top
WWW WWW  
IP Logged
 
MP
Full Member
***
Offline



Posts: 104
Joined: Sep 3rd, 2007
Re: Reports Across Databases
Reply #8 - Nov 22nd, 2007 at 12:19am
Print Post Print Post  
Hammer wrote on Nov 21st, 2007 at 11:07pm:
I would think a report sorted by Teacher would be a different report based on the Teachers records as opposed to the Classes records.

Yes, that's certainly an appropriate approach given your architecture.  I probably should have asked the question about derived columns on a different thread, since the topic is only partially related to reports using relationally linked data.  However, the question still stands.  Let's say, for example, that I only store Quantity and Price in a Line Items database.  In my report for Line Items, I want to sort by Total (i.e. Quantity * Price).  It's easy enough to create the derived column, but I'd like to know if I can sort on it without having to implement the obvious (and messy) workaround (i.e. maintaining the derived data in the database).
  
Back to top
 
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Reports Across Databases
Reply #9 - Nov 22nd, 2007 at 1:31am
Print Post Print Post  
You can't sort by a derived column in a report, although you can break on a derived column. Sorting happens on the server while the resolution of derived column values happens on the client. BTW, you may find that you do want to maintain some pieces of derived data in the database as they can be searched much more quickly and also used in sorts.

My instinct/training is also to not store calculated data, but with Sesame I find that the advantages to storing things like totals far outweigh the disadvantages.

[evil_scheme]I haven't tried it yet, but if you really have a need for it, I bet you can fake a query by making a dummy record definition (database) which you only use to populate using XResultSet, sort with XResultSetSort(), run reports with @XResultSetPrintReport(), and empty it out when you're done with it. Not sure what kind of performance you'd get with that. I'll have to play with it a bit... [/evil_scheme]
  
- Hammer
The plural of anecdote is not data.
Back to top
WWW WWW  
IP Logged
 
MP
Full Member
***
Offline



Posts: 104
Joined: Sep 3rd, 2007
Re: Reports Across Databases
Reply #10 - Nov 28th, 2007 at 1:40am
Print Post Print Post  
Hammer wrote on Oct 24th, 2007 at 1:10pm:
MP wrote on Oct 24th, 2007 at 12:23pm:
As the report is running through the set of Classes to print, does it have to do five separate lookups per row or can you get it do to only two?


You can just do two. One for Teachers. One for Classrooms. It would look something like this (might need syntax check):

This works, but inconsistently.  It appears to work based on the order of the columns in the report (a reasonable expectation), except this isn't always true.  For example, in one report I have a hidden element which holds my Foreign Key value.  It's the first field in the Group Body.  I perform my lookup, get the value, assign it to another value element that exists further to the right in the Group Body, and nothing happens.  The odd thing is that my lookup fetched five values, and the other four worked fine.  This isn't easy to recreate, as not every report that's formatted this way behaves in this fashion.  One time, a report worked perfectly fine.  Then I decided to re-arrange the columns, putting the FK column after the value columns, and the report broke (which, as stated before, is a reasonable expectation).  When I put the columns back to their original order, I could no longer get the assignment to work.

My workaround for the above was to create a static variable and assigned the lookup value to it.  Then in the On Print event of particular value element I'm having a problem with, I assign the static value to the element.  Note also that I use ThisElement, as I've also found that using the element name, even in the On Print event for that element, is also unreliable.
  
Back to top
 
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Reports Across Databases
Reply #11 - Nov 28th, 2007 at 2:27am
Print Post Print Post  
MP wrote on Nov 28th, 2007 at 1:40am:
My workaround for the above was to create a static variable and assigned the lookup value to it.  Then in the On Print event of particular value element I'm having a problem with, I assign the static value to the element.  Note also that I use ThisElement, as I've also found that using the element name, even in the On Print event for that element, is also unreliable.

If you have a report that is acting funny, can you send it into Support? If you've shaken out a bug, we'd like to fix it. If it's just an adjustment that needs made to your SBasic, they can probably tell you what to do and in what order.
  
- Hammer
The plural of anecdote is not data.
Back to top
WWW WWW  
IP Logged
 
MP
Full Member
***
Offline



Posts: 104
Joined: Sep 3rd, 2007
Re: Reports Across Databases
Reply #12 - Nov 28th, 2007 at 2:42am
Print Post Print Post  
Hammer wrote on Nov 28th, 2007 at 2:27am:
If you have a report that is acting funny, can you send it into Support? If you've shaken out a bug, we'd like to fix it. If it's just an adjustment that needs made to your SBasic, they can probably tell you what to do and in what order.

Sure.  How do I do this without sending my whole app (something I'm hesitant to do)?
  
Back to top
 
IP Logged
 
Ray the Reaper
Global Moderator
Members
Lantica Support
*****
Offline


The One & The Only

Posts: 2482
Joined: Aug 20th, 2003
Re: Reports Across Databases
Reply #13 - Nov 28th, 2007 at 6:32pm
Print Post Print Post  
Hello MP,

If you are in Version 2.0 you can export the report design out to XML and e-mail that to support@lantica.com. That should give us a good enough idea of what you are doing without needing the entire applicaiton.

-Ray
  
Raymond Yoxall Consulting
ray.yoxall@gmail.com
ryoxall@lantica.com
Sesame Applications, Design and Support
Back to top
WWW WWW  
IP Logged
 
Page Index Toggle Pages: 1
Send Topic Send Topic Print Print
‹ Previous Topic | Next Topic ›
 
  « Board Index ‹ Board Top  

Lantica User Forum » Powered by YaBB 2.6.11!
YaBB Forum Software © 2000-2024. All Rights Reserved.

Valid RSS Valid XHTML Valid CSS Powered by Perl Source Forge