Report row limit on sub-queries
The report row limit is interfering with the effective operation of report sub-queries.
Master query: Sales table to return say top 10 rows
Subquery: Returns the total sales per product and month to compare to the daily sales as proportion of monthly sales of each product and daily sale transaction.
Report limit being applied to the subquery and therefore fails to find all matches to the master query rows.
SELECT T0.C0, T0.C1, T0.C2, T0.C3, T1.C2 FROM ( SELECT DISTINCT TOP 10 "Test"."MonthDt" AS C0, "Test"."SaleDt" AS C1, "Test"."ProductCd" AS C2, SUM("Test"."Qty") AS C3 FROM "dbo"."Test" GROUP BY "Test"."SaleDt", "Test"."MonthDt", "Test"."ProductCd" ) T0 LEFT OUTER JOIN ( SELECT DISTINCT TOP 10 "Test"."ProductCd" AS C0, "Test"."MonthDt" AS C1, SUM("Test"."Qty") AS C2 FROM "dbo"."Test" GROUP BY "Test"."MonthDt", "Test"."ProductCd" ) T1 ON T0.C2 = T1.C0 AND T0.C0 = T1.C1
Options to consider
1) Apply report limit only to outer query after subquery matches. Basically using top 10 only in the final query which is the expected behaviour.
2) Allow the row-limit to be specified for subqueries (override) if the default behaviour is to remain for each sub-query. Most control for designers, also most appropriate for union type queries.
3) Define an outer and inner row limit at report level with inner row limit to apply to all sub-queries in the report. Less control but also works
The advice to abandon row limit completely in order to correct this operation as per
detracts from the main purpose of the row-limit at report level in the first place.
I understand that all queries will be affected by the data source row limit which is fine.