Report based on query + sub-report (1:N relation)

[BayerMeister]

Hi. I'm a bit lost in passing parameters, setting WHERE filters etc, so my report does not give me the information I want.

I have 3 tables: A (with id, data1), B (with id, a_id (foreign key), data2), C (with id, b_id (f.k.), data3).



I have one query:

SELECT A.data1, B.data2, B.id, COUNT(C.id) AS count_c, SUM(C.data3) AS sum_c
FROM (A INNER JOIN B ON A.id=B.a_id) INNER JOIN C ON B.id=C.b_id
GROUP BY B.id, B.data2, A.data1;

I have a helper report (used as sub-report later), that is just like the table C.

Ontop of the query above there is the main report. It embeds the sub-report.

Here's how that should look like. The whole "Main Report" is populated from the query above (not directly from the tables A and B). The sub-report takes data from table C, as written above.



But it doesn't look like this yet.

So, what's ok and what's not:

• As soon as I set the sub-report's master and child link I always get a poll dialog asking me for the value of "B". What's B by the way? B.id? The query therefore contains the B.id to be linked with the C.b_id of the sub-report.
• If the above worked, the sub-report would be limited to records from C that linked to the same B record.
• The query is not yet limited to one B.id. I want to do that from outside. Say I call the report from a button-press and there I know I want only stuff related to B.id = 7. What means are used to pass this information?

Thanks for your interest, extra thanks for help!

[BayerMeister]

OK, I did this completely differently:

• There is no big reason to have more than one query/table here - I changed the query so that the result contains columns of all 3 tables (a join over all 3 tables, WHERE B.id is given)
• Therefore I didn't need a sub-report for a "sub-query", I used textboxes. In the body of the document components are duplicated for each record. Later I realized that placing a sub-report into the body makes even this component apper that many times as there are records to show. So the lesson: If you want to use a subquery, place it into the bottom of the page/report header.