[SOLVED] LazReport And a sub report (Part 2)

[madref]

Thanks to a few people i managed to get my master-detail report sorted out in this thread.

But now i want to add the final part to this report.
A club also has trainers, clubmanagers and so on.
These are selected in this query:

Code: Pascal  [Select][+][-]

1.   cSQL := 'SELECT * FROM qry_Seizoen_LedenPerTeam_Overige ' +
2.           'WHERE (Adm_Sei_ID = ' + IntToStr(CB_Seizoen.KeyValue) + ') ' +
3.           'ORDER BY Achternaam, Voornaam;';
4.   TQ_Rapport2.Active := False;
5.   TQ_Rapport2.DataBase := DB_Info.Connect_DB;
6.   TQ_Rapport2.SQL.Text := cSQL;
7.   TQ_Rapport2.Active := True;

Query qry_Seizoen_LedenPerTeam_Overige is build like this.

Code: Pascal  [Select][+][-]

1.     // qry_Seizoen_LedenPerTeam_Overige
2.     cSQL := '';
3.     cSQL := 'SELECT '+
4.               'Adm_Sei_ID, Lid_ID, Achternaam, Tussen, Voornaam, CS, SC, TR, LA, WS, BL, ' +
5.               'Lid_Adres, Lid_Postcode, Lid_Woonplaats, Lid_Telefoon, Lid_Mobiel, ' +
6.               '(CASE WHEN [CS]="X" THEN 1 ELSE 0 END + CASE WHEN [BL]="X" THEN 1 ELSE 0 END + ' +
7.               'CASE WHEN [SC]="X" THEN 1 ELSE 0 END + CASE WHEN [TR]="X" THEN 1 ELSE 0 END + ' +
8.               'CASE WHEN [LA]="X" THEN 1 ELSE 0 END + CASE WHEN [WS]="X" THEN 1 ELSE 0 END) AS ' +
9.               'Aantal_Funkties ' +
10.             'FROM ' +
11.               'tbl_Administratie LEFT JOIN qry_Seizoen_Overzicht_Leden_Uitgebreid ON ' +
12.               '(tbl_Administratie.Adm_Lid_ID=qry_Seizoen_Overzicht_Leden_Uitgebreid.Lid_ID) ' +
13.               'AND (tbl_Administratie.Adm_Sei_ID=qry_Seizoen_Overzicht_Leden_Uitgebreid.Sei_ID) ' +
14.             'GROUP BY ' +
15.               'Adm_Sei_ID, Lid_ID, Achternaam, Tussen, Voornaam ' +
16.             'HAVING Aantal_Funkties > 0 ' +
17.             'ORDER BY Achternaam, Voornaam;';
18.     Connect_DB.ExecuteDirect('' +
19.             'CREATE VIEW IF NOT EXISTS qry_Seizoen_LedenPerTeam_Overige AS ' + cSQL);
20.     Trans_DB.Commit;

I tried a new master-detail and even a sub-detail but that did not work.
How can i get this final part in my report?

[madref]

Bump up.


Any1?

[ttomas]

@madref Your question don't have enough information for answer.
You have one query and talk about master/detail/subdetail.
Your query is not prepared to be detail or subdetail. You must have param with the same name of field of your master.
Please explain what you want to do with report.
In your previous post you mention Other members of team. Do this trainers, clubmanagers and so on=Other members or this is something new?
You must first realize how your data is connected.
Master/Detail/Subdetail works perfectly well in lazreport.

[madref]

In access terms it would be called a sub-report (as in a report in a report).

A club has members. These members are categorized in teams and other people.
Each team has players, at least one coach, a team manager, a physiotherapist and other people related to that team (That was my first problem and was solved in that other thread).

But a club has also members that do the organization of that club. Like a president, a vice-president, a referee coordinator, referees, a technical commission and other related persons to that club.

They all have no connections to any teams. But these are the members that 'stay-over' for that season.

The season is the connection they all have in common.

I hope that this gives you more info in my problem and we can solve it together.

[ttomas]

In access terms it would be called a sub-report (as in a report in a report).

You have subreport component also in lazreport

But a club has also members that do the organization of that club. Like a president, a vice-president, a referee coordinator, referees, a technical commission and other related persons to that club.

They all have no connections to any teams. But these are the members that 'stay-over' for that season.

This is contradiction
club has also members vs no connections to any teams

Please explain what you want to print

[madref]

in that previous thread i also posted a screenshot from windows.
The top part of this attachment shows the report as in that thread.
The lower part is the part i want to recreate.

See attachment

[ttomas]

I already send you modification and report file. Do you test it?
Senioren 1990 is your Team name (Master query-MasterData)
Then you have 21 Players Detail1 - DetailData1 band.
Overige section is yours Other members of the team. This is also Detail of the same master QTeam. This is your Detail2 DetailData2 band. This Query must have Team_ID=:Team_ID in where section of query and set DataSource property to QTeam datasource. You must write query to return this 13 people (Other members, trainer, etc) for Team Senioren 1990 (where Team_ID=:Team_ID ...).
Do you know how to write all querys?
Do you know how to set master/detail relation in datasets?
Do you get correct data in 3 additional grids that i add in form?
You don't answer my question. Do you want to print all teams in league or just one?

[madref]

I already send you modification and report file. Do you test it?

Yes i did

...
Overige section is yours Other members of the team.

And there's the thinking mistake.
I think you know how a sports club is organized so there are ALWAYS people who DON'T belong to a team but they run the club.
I have organized them by season (or Adm_Sei_ID)

Do you know how to write all querys? -=> YES
Do you know how to set master/detail relation in datasets? -=> NOW I DO
Do you get correct data in 3 additional grids that i add in form? -=> YES
You don't answer my question. Do you want to print all teams in league or just one? -=> There is no league only a club with teams. and i only want to show the teams that have members. Which i managed.

[madref]

I solved it with a sub report.

[ttomas]

OK now you explain how is your data organized.
In that case you have Master (Team)/Detail(Players) report + Master subreport.
TQ_Rapport2 is not detail of QTeam, DataSource is empty.
You can use subreport.
Place DetailFooter band after DetailData1 band.
Place subreport component on DetailFooter band.
Designer will open new page SubReport1, in this Page place MasterHeader for Other members (Overige) and MasterData connected to TQ_Rapport2 with data.
In report I attach  I connected subreport to TQ_Rapport2 with Rapport_DBDS2 TfrDBDataSet. I can't test your query view is missing in my database, but you will have idea how subreport works.

[ttomas]

You solved when I typed previous post. Some brain waves travel through galaxy   

[madref]

You solved when I typed previous post. Some brain waves travel through galaxy   

Yes i certainly had a good brain wave because thats what you get if you work on a neurosurgery department