Suggestions for QRP design?

[rogeryeoh]

System: CTD 4.2, RB 4.2, SQLBase 9.01

I have this requirement of printing company transaction statements which basically comprise a section on payments to be made and another one for deductions. The respective data for these 2 report sections are captured in 2 separate child tables, each with their parent table foreign key.

As far as I've seen, RB only has 1 detail section, so how should I best address this situation with 2 child table (details)? I've considered re-doing the database design such that there'd be only 1 child table, but this would be terrible design; like merging the PAYMENTS and the DEDUCTIONS tables into a single "MULTI-PURPOSE" table. This part isn't actually too bad, but the report layout would be odd, with an extra wide detail section with many fields that are mutually exclusive to each transaction type.

Am I missing something here, cos I would have thought reports containing different detail sections would be quite common. Or am I looking at this totally wrong?

Thanks

[Charlie]

Hello,

The trick we've used on a few reports:

• create a report variable (say "DetailSection") that you set to whatever values depending on the type of data record being fed to the report;
• setup the report template with multiple detail lines, each one (or each group of details) only prints depending on the value of "DetailSection"

We have one report that has over a dozen different groups of "sub-records" for each record, so we have a matching number of detail lines (or groups of lines) for each type of sub-record.

I've attached a small snipit of code. Hope it helps.

[Charlie]

A nice simple example: MultiDetailReport.zip (on Dave Rabelink's Team Developer Sample Vault)

[rogeryeoh]

Hi charlie,

Many thanks for your suggestion.

I did think of using your method .. like a parameter-dependent detail layout, but 2 questions arise:
1) How would you handle the different column headers for the 2 data types? Think I could use break group headers for this, using "DetailSection" as a control break value?
2) if say, I'm having 2 lines in the detail block with each line being mutually-exclusive to the other, during actual printing, wouldnt there always be an extra blank line being printed? Would this look like a double-spaced report?

I've also considered using one single unformatted detail line (with one full-width data field). The APP would extract, pre-sort and parse the relevent data rows and columns into an "output" array, from both child tables. The completed array is then looped through and passed to SAM_ReportFetchNext the normal way. I'd use the same array to contain the relevent column heading line such that column headings would be relevent to the next block of data. Does this make any sense at all?

[Charlie]

Hello,

1) you have to put the column headers in the detail block, showing them only for the first occurence of a group of records, so conditional display on the row
2) In a detail row, don't set conditional display functions for each field; set the conditional display on the row; if condition is true, then row and fields appear; if condition is false, then none of the fields NOR the row will appear; that way you don't get "blank" rows showing

I was going to suggest one single unformatted, tab-delimited, line, but the output can get ugly. For example: can't wrap the text in your "embedded" columns.

If you want to get out of a mess that involves multiple queries and bringing it all in together at the app level, consider UNION'ing all those queries together into one SQL statement; that should make your report app and template easier to work with. Makes it easier to debug your query(ies), and easier to debug the report app + template.

Sorry, busy and trying to keep it short yet still useful.

[rogeryeoh]

charlie,

I think I got what you mean finally, using conditional display on the rows. I never realised there was this option at all, my bad. Gonna give it a shot now.

Just to confirm my understanding .. I'll have 2 different column header rows dictated by the respective information types defined on 2 separate rows as fields. Then another 2 rows containing the corresponding fields to these headers, in total 4 detailed rows. Using your UNION suggestion, I'll pass in all the data into these fields such that 1 row would be filled, another would be empty, and let conditional display take care of the printing. Is this about right? Just need to figure out how to interrupt SAM_ReportFetchNext such that I can populate the column headers for the 2nd block of information.

Your help is much appreciated.

Thanks

Roger

[Charlie]

Hey, no bad at all. Glad you've discovered conditonal display ... it opens up a whole bunch of options.

I think you've got the whole design approach down, but I'm not quite sure why you need to interrupt SAM_ReportFetchNext for populating columns headers.

Setup your X rows of column headers for your X groups of data. Each row of column headers just have "Background Text" objects (i.e. not "Field" objects). This way, the report template knows what headings it should show and when (i.e. this row of column headings for such and such a group, and show the row only for the first record of a "sub-group" (or "sub-details"). This is a good case to let the report be in control, i.e. let it know about itself and what it should show when. The only thing the app does is feed raw data to the report for the report to process/format/etc.

There may be times when you want the data to BE headings because those heading can vary/change over time. For example:

• years as column headings: 2004, 2005, 2006, etc.
• fiscal years as column headings: 2004-2005, 2005-2006, etc.
• people names (say total sales amount per salesperson): Frank, Sandra, etc.

I don't think you need to get into feeding the report column header values whenever dealing with a new sub-details group. If you really must, then work with "Input Variables" and SalReportSetStringVar()'s during SAM_ReportFetchNext processing.

Cheers.

[rogeryeoh]

hi charlie,

Your description of the column headers in the detail section has sort of left me blinking. I think I should sit down and study RB's capabilities in detail. This could be because the documentation that came with the CD on RB is not quite as detailed as that for TD or SQLBase. Could you possibly point me in the direction towards a more complete (not just the starters guide) writeup on RB? I really hate to take up your time on what now appears to be something quite simple but has me totally stumped.

I'm a bit lost since you're asking me to use background text types for the column headers rather than field types. This is what I do in a normal report when hardcoding column headers as BG text is the regular thing to do. This is also what I would do in a control-break report in the Control headers. The incoming data rows would then cause RB to determine whether or not to do a control break and print the new headers.

However, I didn't know this could also work in the detail section. I always thought that if I used BG text, they would always be shown, so all my detail lines only had field-types in them while literal column headers would be in the header groups. OK then conditional display comes along and dictates whether or not to show that header detail line instead, which could handle the literal BG column headers. So I've only got to figure out the control variable that feeds the conditional display formula. I think I more or less know how to handle this within the APP without interrupting SAM_ReportFetchNext.

Thanks so much for everything.

[Charlie]

Hello Roger,

Sorry, out of the office for a bit.

As far as documentation goes, I've never used anymore than what comes with TD. I've been working with the TD suite since the mid-90's, so much of what I do now I've learned from using the product, exploring every little bit of it as I use. Sorry, no great references out there that I know of ... just personal experience.

I've attached a screenshot of one of our report templates. This one has multi-details, and has most all column header groups defined in a break group header. I hope that helps a little more, and doesn't confuse too much. Personally, although it would require a little more code in the conditional display functions, I think it would have been smarter to put the column header groups in the detail block, each column header group just above its respective data detail group ... makes it easier to align background text column headers and related data fields.

[rogeryeoh]

charlie,

man oh man oh man!! I didnt even realise you could use the IFF functions on the header variables. OK I'm gonna spend the next couple of weeks on RB to see what I can dig out of it.

Won't hold you up anymore, and all your help is very much appreciated.

Thank you so much

roger

[Charlie]

You're very welcome, Roger.

If ever you need a hand, put together a simple app with a table that feeds hard-coded data to a report template, and we can use that as a base for questions and answers.

Cheers.