Example 5: Using subreports at multiple levels
Here is a multi-level report exploring Activity-ActivityPerformedByPerformer-Performer:
REPORT "Multi-level, no subreports - Activity-APBP-Performer"
ID 60474
{
SETTING { DECIMALSEPARATOR "." }
SETTING { LISTSEPARATOR "," }
SETTING { MEASUREMENT METRIC }
SETTING { PAGESIZE -1", 0.00 }
SETTING { HEADER 1 "t" }
SETTING { REPORTFORMAT 20 }
SETTING { EXTRACTFILE "test" }
SETTING { STYLESHEETFILE "Reports\StyleSht\default.xsl" }
TABULAR 1
{
SELECT "Name" LEGEND "Activity", "Type"
WHERE Class = Definition
WHERE "Type Number" = 1326
ORDERBY "Name"
JOIN
WHERE REFERENCEDBY = "Activity"
JOIN
SELECT "Name" LEGEND "APBP"
WHERE Class = Definition
WHERE "Type Number" = 1380
ORDERBY "Name"
JOIN
WHERE REFERENCES = "Performer"
JOIN
SELECT "Name" LEGEND "Performer", "Type"
WHERE Class = Definition
WHERE "Type Number" = 1367
ORDERBY "Name"
}
}
Using subreports this is, minimally:
REPORT "main: Multi-level, 1-level each: Activity Report"
{
FONT "Font4" { NAME "Courier" HEIGHT 12 }
SETTING { DECIMALSEPARATOR "." }
SETTING { LISTSEPARATOR "," }
SETTING { MEASUREMENT ENGLISH }
SETTING { PAGESIZE -1", 0.00 }
SETTING { HEADER 1 "OV-03 Need Line" }
TABULAR 1
{
SELECT "Name" LEGEND "Activity"
WHERE Class = Definition
WHERE "Type Number" = 1326 REM "Activity"
ORDERBY "Name"
SUBREPORT "Subreport: Activity-APBP Report" { }
}
}
REPORT "Subreport: Activity-APBP Report"
{
FONT "Font4" { NAME "Courier" HEIGHT 12 }
SETTING { DECIMALSEPARATOR "." }
SETTING { LISTSEPARATOR "," }
SETTING { MEASUREMENT ENGLISH }
SETTING { PAGESIZE -1", 0.00 }
SETTING { HEADER 1 "OV-03 Need Line" }
TABULAR 1
{
ORDERBY "Name"
JOIN
WHERE REFERENCEDBY = "Activity"
JOIN
SELECT "Name" LEGEND "APBP"
WHERE Class = Definition
WHERE "Type Number" = 1380 REM "APBP"
ORDERBY "Name"
SUBREPORT "Subreport: APBP-Perf Report" { }
}
}
REPORT "Subreport: APBP-Perf Report"
{
FONT "Font4" { NAME "Courier" HEIGHT 12 }
SETTING { DECIMALSEPARATOR "." }
SETTING { LISTSEPARATOR "," }
SETTING { MEASUREMENT ENGLISH }
SETTING { PAGESIZE -1", 0.00 }
SETTING { HEADER 1 "OV-03 Need Line" }
TABULAR 1
{
ORDERBY "Name"
WHERE Class = Definition
WHERE "Type Number" = 1380 REM "APBP"
JOIN
WHERE REFERENCES = "Performer"
JOIN
SELECT "Name" LEGEND "Performer"
ORDERBY "Name"
}
}
Note This lists all Activities regardless of whether they are linked with an ActivityPerformedByPerformer.
…
This modification shows how you can add constraints that will affect the results. It is rather wasted in this case, but it shows what happens:
REPORT "main: Multi-level, 1-level each: Activity Report - constrained"
{
FONT "Font4" { NAME "Courier" HEIGHT 12 }
SETTING { DECIMALSEPARATOR "." }
SETTING { LISTSEPARATOR "," }
SETTING { MEASUREMENT ENGLISH }
SETTING { PAGESIZE -1", 0.00 }
SETTING { HEADER 1 "OV-03 Need Line" }
TABULAR 1
{
SELECT "Name" LEGEND "Activity"
WHERE Class = Definition
WHERE "Type Number" = 1326 REM "Activity"
ORDERBY "Name"
SUBREPORT "Subreport: Activity-APBP Report" { }
JOIN
WHERE REFERENCEDBY = "Activity"
JOIN
WHERE Class = Definition
WHERE "Type Number" = 1380
JOIN
WHERE REFERENCES = "Performer"
JOIN
WHERE Class = Definition
WHERE "Type Number" = 1367
}
}
This shows what happens if selections are made from the main report at the lower levels:
REPORT "main: Multi-level, 1-level each: Activity Report - constrained with selections"
{
FONT "Font4" { NAME "Courier" HEIGHT 12 }
SETTING { DECIMALSEPARATOR "." }
SETTING { LISTSEPARATOR "," }
SETTING { MEASUREMENT ENGLISH }
SETTING { PAGESIZE -1", 0.00 }
SETTING { HEADER 1 "OV-03 Need Line" }
TABULAR 1
{
SELECT "Name" LEGEND "Activity"
WHERE Class = Definition
WHERE "Type Number" = 1326 REM "Activity"
ORDERBY "Name"
SUBREPORT "Subreport: Activity-APBP Report" { }
JOIN
WHERE REFERENCEDBY = "Activity"
JOIN
SELECT "Name" LEGEND "APBP"
WHERE Class = Definition
WHERE "Type Number" = 1380
ORDERBY "Name"
JOIN
WHERE REFERENCES = "Performer"
JOIN
SELECT "Name" LEGEND "Performer", "Type"
WHERE Class = Definition
WHERE "Type Number" = 1367
ORDERBY "Name"
}
}
The APBP, Performer and Type columns are sourced from the levels of the main report.
The APBP and Performer columns following those are sourced from the subreports:
Related topics
Parent topic
