change report row limit

Hi Usman,

Thanks for the question on the row limit issue you are having. Can i just confirm the following, The version of Yellowfin / Build you are using which you can get from the URL:8080/info.jsp

You have mentioned you have adjusted the DB source and View settings but can I confirm this is where it was done.

Data Source

View

Regards,

Paul

Hi Paul

Please find the information below

Version

Connection Setting

View

Report

As you can see that when the report is actually executed with filters, the SQL that runs behind the scene is trimming the internal queries to 10,000 records. Not sure how to change this.

Hi Usman,

Can I confirm that you are using Microsoft SQL Server as the DB? There is a bug already logged for a similar issue with Microsoft SQL Server so I would like ton confirm this first and replicate it using SQL Server.

Thanks,

Paul

Hi Usman,

I have simulated this with YF 7.4 and MS SQL 2016 as follows.

STEP 1

SQL Data Source - Rows set to 500

SQL View - Rows set to 500

Report Sets it to 500 on initial

SQL Query

—————

SELECT

T0.C0,

T0.C1

FROM (

SELECT DISTINCT TOP 500

"AccessGroupMembers"."InternalReferenceId" AS C0,

SUM("AccessGroupMembers"."AccessGroupId") AS C1

FROM "dbo"."AccessGroupMembers"

GROUP BY

"AccessGroupMembers"."InternalReferenceId"

) T0

LEFT OUTER JOIN (

SELECT DISTINCT TOP 500

SUM("AccessGroupMembers"."AccessGroupId") AS C0

FROM "dbo"."AccessGroupMembers"

) T1

ON T0.C1 = T1.C0

STEP 2

I then modified at different steps the Source to unlimited and then the View to unlimited max rows and checked the SQL report but both the same.

Result as follows

—————

SELECT

T0.C0,

T0.C1

FROM (

SELECT DISTINCT TOP 500

"AccessGroupMembers"."InternalReferenceId" AS C0,

SUM("AccessGroupMembers"."AccessGroupId") AS C1

FROM "dbo"."AccessGroupMembers"

GROUP BY

"AccessGroupMembers"."InternalReferenceId"

) T0

LEFT OUTER JOIN (

SELECT DISTINCT TOP 500

SUM("AccessGroupMembers"."AccessGroupId") AS C0

FROM "dbo"."AccessGroupMembers"

) T1

ON T0.C1 = T1.C0

So the TOP 500 has remained the same which is expected in this case. The bug mentioned before could be causing issues, but I cannot replicate it here. If I remove the limit directly from the report it works as expected.

Step 3

If I remove the number of rows from the report, I get the following SQL query.

——

SELECT

T0.C0,

T0.C1

FROM (

SELECT DISTINCT

"AccessGroupMembers"."InternalReferenceId" AS C0,

SUM("AccessGroupMembers"."AccessGroupId") AS C1

FROM "dbo"."AccessGroupMembers"

GROUP BY

"AccessGroupMembers"."InternalReferenceId"

) T0

LEFT OUTER JOIN (

SELECT DISTINCT

SUM("AccessGroupMembers"."AccessGroupId") AS C0

FROM "dbo"."AccessGroupMembers"

) T1

ON T0.C1 = T1.C0

So the limit has now been removed from the main SQL and sub query, but as mentioned I had to edit the report to achieve this, which should work. If not then it still may be related to the bug.

I hope this clarifies the output a little more.

Hi Paul

Yes I am using SQL Server and have the same scenario as you explained above.

I have removed limit from the data source and view but can't find the option to do the same for report. I have checked the options on "Data", "Charts" and "Design" screens but cannot find the row limit option. Can you help me finding this option?

Hi Usman,

Please see below for the limit. It should just be setting / changing the Active Row limit when in the report. MS SQL server as mentioned adds a sub-query row limit, but changing this will update the limit in the query. Please try changing this and let me know how it goes.

Thanks Paul, that did the trick.

For now, I was able to fix the results using this limit. A permanent fix would off course be when this limit applies to overall results as opposed to sub-queries.

Feel free to close this ticket.

Hi Usman,

That is great to hear. If there is anything else, please let me know or just re-open this ticket.

Cheers,

Paul