Understanding the Relationship
Posted by John Krische on April 16, 2010 at 7:58am
Hi Jan, et al...
First off, Jan, thanks much for you many contributions to Rev, including helping yours truly on a previous occasion with something totally different on the same project I've been struggling with for some time now. Apologies in advance for the length of this post.
I recently bought your QtmRep "basic" version and am just plain tearing my hair out trying to make it do some pretty basic reporting, or what seems to me to be basic anyway. In Filemaker I'd have it done in 1 minute or less. Not so much in Rev, but I've gotten used to that paradigm.
Anyway, I'm having trouble understanding the relationship between the fields in my query and what you call "group expressions". I've tried to follow your examples and every time I try, I get really bad results. I can't figure out what it is that I need to be putting into the "group expression" in order to make my grouping work. The docs just aren't clear on this.
Now, I admit my problem may be that I need to upgrade to QtmRep Pro, as I think my SQL queries are complex enough to warrant using what you call "data brokers" (which to me is something TOTALLY different in meaning). Unfortunately, I don't have the funding for that right now, so I've got to do my best with the basic version and then hope I get enough from selling my standalone to invest in the better version.
My report should be fairly simple. I have a table of log entries, each with their own timestamp and various fields about the event being logged. Events in the log happen many times per day. I wish to have a report that shows these events, grouped by date. It doesn't get much more basic than that in real-world reports, no?
So, what the heck do I need to put in the "group expression" to make this work? No matter what I try, I get the group header to show the date of the first group, then absolutely NOTHING in the detail line shows up, and that's it. I get 1 page with 1 date on it and nothing else. I've tested my queries independently and they work just fine; I know I've got plenty of data that should be showing up.
Please tell me you have a tutorial or sample that is this simple, I'd hate to take up much of your time for something so small. I've been looking at your "sample3.qrl" and the matching example in the example stack, to no avail. I think the problem there is that it relies on the "broker" model, which I can't use since I'm not on the Pro version.
Reply by John Krische on April 16, 2010 at 8:47am
Well, heck, nevermind...
Experimented my way into figuring it out, finally, after the umpteenth try. The problem was indeed my trying to follow the broker model, which I can't use since I'm not using QtmRpt Pro. After rearranging my query structure a bit, I at least have a working report that uses static WHERE clause requirements inside Rev's Query Builder. Now I just need to figure out how to make those WHERE requirements be dynamic and I'm all set, which is more a Rev issue than a QtmRept one.
(the whole rest of my app doesn't use Query builder, so I'm off to learn it now...)
Sorry to bother. Move along, nothing to see here...
Reply by Jan Schenkel on April 18, 2010 at 3:04pm
You don't need to use Rev's Database Query Builder to print database content - just use your existing database connection and run a query to fetch the records as a cursor (aka result set), then print the report using the qrtReports_PrintReportForCursor command.
qrtReports_PrintReportForCursor , ,
Here's a simple example, assuming that you're using a global gDbConnection to store the revdb connection id:
-- first run the query
local tSqlQuery, tDbCursor
put "SELECT * FROM Customer" into tSqlQuery
put revQueryDatabase(gDbConnection, tSqlQuery) into tDbCursor
-- then print the report
local tLayoutPath, tPreviewFlag
put "the_path_to_the_layout_file" into tLayoutPath
put true into tPreviewFlag
qrtReports_PrintReportForCursor tLayoutPath, tDbCursor, tPreviewFlag
-- finally cleanup the database cursor
You can make the query as simple or complex as you like, and pass parameters (see the excellent Rev user manual for information on how to use placeholders) - then hand off the database cursor to Quartam Reports to assemble the report.
Data groups are groups of detail records that have something in common: all customers from a specific city, all invoices for the same customer,... You specify the common trait in the form of a revTalk expression. One of the nicer features of Quartam Reports is that it allows you to write this revTalk expressions using the name of the query result columns as if it were a variable.
So if there is a column 'cust_Country' in your Customer table, the revTalk expression for your data group could simply be 'cust_Country' - and then you can print a group header for that country, as well as a footer with some aggregate data when the current detail record's cust_Country column is different from the previous detail record's cust_Country column.
Important here is to make sure they also come from the database pre-sorted in the right sequence - so add an ORDER BY clause to your SQL query to that effect.
Data groups are very powerful constructs, but they make take a bit getting used to.
Of course, if you have additional questions, do not hesitate to post them here.
Quartam Reports & PDF Library for Revolution
Reply by John Krische on April 19, 2010 at 11:03pm
Yeah, stumbled into making this work, too, so now I have several different working reports using this model.
Unfortunately, I've never gotten rev's placeholders to work as advertised. I always end up with a :1 or whatever instead of the substituted value, but that's OK, I don't much need it for what I'm doing.
Don't get me wrong, I'm thoroughly familiar with data groups on reports. Been doing grouped reports in FMP for a LONG time now (been using FMP since v2.1!), and similar things in PHP apps I build. The whole problem was that broker model I was trying to follow. I've now got, for example, several reports working where eventlogs of various types have reports where the events are grouped by event date, based on this simpler model using my existing gConID exactly as you've described.
Now that I'm getting used to its quirks, I'm loving this product. Many thanks!
Reply by Jan Schenkel on April 21, 2010 at 9:10pm
Sorry if I sounded like I was teaching a small child about the intricacies of a belt buckle
I've had quite a few support questions about what exactly groups are, hence the exhaustive reply.
Glad to hear you're moving forward and enjoying the features - that's why I built it in the first place!
Quartam Reports & PDF Library for Revolution
Whether you're using the Standard or Professional Edition of Quartam Reports, this is the place to ask general questions about using this industry-level reporting tool for LiveCode.
1 post • Page 1 of 1