SYMPTOM
The number of rows returned on a Strategy report is lesser than the number of rows seen when running the report SQL directly against the Microsoft SQL Server database. Even when running the SQL from Strategy DB query tool, the expected number of rows are returned.
The following factors are verified to be not applicable to this scenario:
This issue is seen for some reports where subqueries are used in the report SQL. Also, this issue is not seen when running the report through a 2-tier connection.
CAUSE
This issue can be seen when the results of a subquery used in the report SQL exceed the governing limit for final result rows under Project configuration > Governing rules > Final Result Rows.
To impose this governing limit, we set the 'SQL_MAX_ROWS' parameter value to the value of the governing limit, as a Statement option when running the report SQL. This ODBC parameter translates to the 'SET ROWCOUNT' parameter on the SQL Server database. When the results of a subquery exceed this limit, the SQL Server database truncates the subquery results as per the governing limit, thus resulting in a fewer rows.
ACTION
Increase the governing limit on the project configuration editor so that the governing limit takes the results returned for a subquery into account as well.
The Strategy internal reference number for this issue is 978988.
