Example 5: Using subreports at multiple levels
This example multi-level report explores 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"
}
}
This lists all Activities regardless of whether they are linked with an ActivityPerformedByPerformer.
...
This modification shows how you can add constraints that 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
