change report row limit

Usman Jamil shared this question 1 week ago
Answered

Hi

I previously set a 10,000 record limit on the data source and build a few reports on it. I later realized that this is being applied on sub-queries instead of overall result, so i removed limit from the data source. There is no limit on the view either.

The report still shows 10,000 limit applied to sub-queries. I can't find an option to change that. I want to avoid recreating the views and report as I'll have to do it for quite a few reports built on this view.

Comments (8)

photo
1

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

6d58fbe54e2ddbc9d0899f0f992d97f3

View

4df4e6e1493d867808fb92db90bd7f3f

Regards,

Paul

photo
1

Hi Paul

Please find the information below


Version

/aqPAAAAAElFTkSuQmCC


Connection Setting

/EjT60w6bwHwAAAAASUVORK5CYII=


View

/weqscHTBYveZQAAAABJRU5ErkJggg==


Report

/CFk3Ol4OIQuyyUhIASEgBAQAvuNwP8FmR5qeHMhSOYAAAAASUVORK5CYII=


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.

photo
1

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

photo
1

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.

photo
1

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?

photo
1

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.

e45b28bf24f6109c668185adce0248e5

photo
1

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.

photo
1

Hi Usman,

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

Cheers,

Paul