Report with two different sections

[igalperry]

Hi Jan,

I am trying to design a report which has two separate sections with different columns and data representations. The report would include totals derived from both sections. How can I best accomplish this using Quartam. Note that I am getting the report data from MySQL if this has any bearing on the method of reporting. I have provided a simplified example below.

Thanks in advance.

Igal

Code: Select all

Section 1

Part ID    # Sold     Unit Prc     Total $
A          2          7.50         15.00
B          5          10.00        50.00
------------------------------------------
Total                              65.00

Section 2

Consultation      150.00
Labor             950.00
------------------------
Total            1100.00

Project Total    1165.00 (Total from section 1 and section 2)

[JanSchenkel]

Hi Igal,

This one is a bit of a challenge, but not impossible to accomplish.

• I would fetch all the data in a single query, using UNION to put together different SELECT results
• as part of each SELECT, I would add a 'ReportSection' column with a fixed value
• that way I could use the 'qrtReports_PrintReportForCursor' command
• on the layout side, I would first design the Detail band to only contain items for Section 1 lines
• once that's working correctly, I would extend the Detail band with the items for Section 2 lines, below the Section 1 items
• then I would add printing conditions to each item, based on the 'ReportSection' column that we added earlier
• once everything works as expected, apart from blank space, I'd take a backup copy first
• and then move the Section 2 items upwards so that they overlay the Section 1 items, finally resizing the Detail band

The tricky part will be to get the query right.

Code: Select all

SELECT ... UNION SELECT ...

queries are tricky in that they require the same number of columns which have to be in the same order and of the same data type. So you'd have to insert 2 empty columns (for # Sold and Unit Prc) into you second SELECT. I have no idea what your database structure is like, but the query would eventually look something like this:

Code: Select all

SELECT 1 AS ReportSection, PartId, NumberSold, UnitPrice, NumberSold * UnitPrice AS LineTotal FROM ...
UNION ALL
SELECT 2 AS ReportSection, WorkDescription, 0, 0, WorkTotal FROM ...

Of course, the above is just my initial thought - if you need more help, don't hesitate to post here!

Jan Schenkel.

[igalperry]

Hi Jan,

Many Thanks,
This sounds like it is going to work! I will let you know as soon as I put it to the test (will take a while as my database is much more complex, as you suspected).
Best,
Igal

[JanSchenkel]

One more suggestion - when you go down the 'SELECT UNION SELECT' route, you should use 'AS' frazes to give each column the same name across the different 'SELECT' statements.

Jan Schenkel.

[JanSchenkel]

Well, if you have two SELECT statements that you're going to combine using UNION, your life will be a lot easier if you give the columns a common name.

Code: Select all

SELECT 1 AS ReportSection, PartDescription AS Description, NumberSold, UnitPrice, NumberSold * UnitPrice AS LineTotal FROM ...
UNION ALL
SELECT 2 AS ReportSection, WorkDescription AS Description, 0 AS NumberSold, 0 AS UnitPrice, WorkTotal AS LineTotal FROM ...

Quartam Reports lets you use column names directly in your expression, as long as they are valid LiveCode variable names. And by using the same column name on both sides of the UNION, through the use of AS frazes, you make your life easier as well.

HTH,

Jan Schenkel.

[igalperry]

Jan,

Good news! Your suggestion seems to have worked. I am able to utilize printing conditions to display different report sections. I am not using a broker. Instead, I have utilized groups to show summaries for each of the report sections. Unfortunately, I have encountered an issue that I would like to bring to your attention as it may indicate an problem in Quartam. It appears that the group footer for a group displays the group value for the next group. That is:

If group 1 is "A", group 2 is "B", and group 3 is "C" then I see the following:

Group Header 1: "A"
Group Footer 1: "B" (Expected to see "A"!)

Group Header 2: "B"
Group Footer 2: "C" (Expected to see "B"!)

Group Header 3: "C"
Group Footer 3: "C"

Any insight you can provide is greatly appreciated.

Igal

[JanSchenkel]

Well, it's actually not that strange - the data context has already moved on to the next record. This is easier to get right when scripting your own custom data broker.
Admittedly, this is something that should be handled more elgantly, by pushing the context back before printing the group footers, then pushing it forward again before printing the group headers. Unfortunately, that won't work for all database cursors, as some are of the forward-only persuasion.
I'll see what I can do for the next functional release (1.2.0).

Thanks for the feedback!

Jan Schenkel.

[igalperry]

Jan,

To circumvent the issue, I have used a broker and within the broker I have kept the previous record in memory. When evaluating group statements, I refer to that last record for evaluating certain fields. It has worked out so far.

Thanks,

Igal