Dynamic Number of Rows

[Hatcher12]

Hello, I have a query that I need to pull some tabular data out of and export it with excel. I have been getting a 50,000 row error even with the query hint Maximum Rows Retrieved set to (ALL), 60,000, etc. I was wondering if there is a way for me to specify a range of rows to pull, and then run the same query with a new specified set of rows this time starting from row number X that I left off from previous run. I am ordering the query as well to avoid duplicates. Thank you!

*EDIT* This query is sql based and the FETCH ROWS function did not seem to work very well

[MFGF]

Hello, I have a query that I need to pull some tabular data out of and export it with excel. I have been getting a 50,000 row error even with the query hint Maximum Rows Retrieved set to (ALL), 60,000, etc. I was wondering if there is a way for me to specify a range of rows to pull, and then run the same query with a new specified set of rows this time starting from row number X that I left off from previous run. I am ordering the query as well to avoid duplicates. Thank you!

*EDIT* This query is sql based and the FETCH ROWS function did not seem to work very well

Hi,

Are you getting an issue with a row retrieval limit, or is it an issue with a row limit exporting to Excel? What is the exact error you see?

Cheers!

MF.

[Hatcher12]

Hi,

Are you getting an issue with a row retrieval limit, or is it an issue with a row limit exporting to Excel? What is the exact error you see?

Cheers!

MF.

Hey,

Server Error with starting details,

XQE-V5-0013 The number of rows retrieved exceeds the limit of '50000'. Please increase maxRowsRetrieved hint for the query 'BIWEEKLY'.
RSV-SRV-0042 Trace back:
RSReportService.cpp(786): XQEException: CCL_CAUGHT: RSReportService::processImpl()

It will run in html but upon running in excel that is what I recieve. Also I am using Excel 2019 I saw some articles about the old row limit on excel's side.

[MFGF]

Hey,

Server Error with starting details,

XQE-V5-0013 The number of rows retrieved exceeds the limit of '50000'. Please increase maxRowsRetrieved hint for the query 'BIWEEKLY'.
RSV-SRV-0042 Trace back:
RSReportService.cpp(786): XQEException: CCL_CAUGHT: RSReportService::processImpl()

It will run in html but upon running in excel that is what I recieve. Also I am using Excel 2019 I saw some articles about the old row limit on excel's side.

If you select the query you are using (in the Queries pane) and look in the Properties pane on the right, what is the "Maximum rows retrieved" property of the query set to be?

Also, as a test, do you get the same error if you run the report to PDF?

Cheers!

MF.

[Hatcher12]

If you select the query you are using (in the Queries pane) and look in the Properties pane on the right, what is the "Maximum rows retrieved" property of the query set to be?

Also, as a test, do you get the same error if you run the report to PDF?

Cheers!

MF.

Maximum rows retrieved is set to all, when I run it as a PDF it failed with the same error. I went down the rabbit hole once after reading a document online about changing rows retireved on the backend of cognos but I didn't feel comfortable changing any of that even though I think I had access to.

[MFGF]

Maximum rows retrieved is set to all, when I run it as a PDF it failed with the same error. I went down the rabbit hole once after reading a document online about changing rows retireved on the backend of cognos but I didn't feel comfortable changing any of that even though I think I had access to.

Hi,

So it's definitely an input limit on row retrieval rather than an output limit exporting to Excel. HTML works because it only retrieves one page at a time when running interactively, whereas the other rendering types produce the whole output in one go (so all the data is read at once).

You mentioned using a SQL query? Is there a reason you're hard-coding SQL and not just using a regular query containing data items? I'm wondering if this is imposing a limit?

Cheers!

MF.

[dougp]

Could this be related to a governor in the Framework Manager model?

[Hatcher12]

Hi,

So it's definitely an input limit on row retrieval rather than an output limit exporting to Excel. HTML works because it only retrieves one page at a time when running interactively, whereas the other rendering types produce the whole output in one go (so all the data is read at once).

You mentioned using a SQL query? Is there a reason you're hard-coding SQL and not just using a regular query containing data items? I'm wondering if this is imposing a limit?

Cheers!

MF.

Long story short there are some data elements that are not available on the data items container. I am importing records into the DEV environment which is something that most people in my position that work on this platform would not be doing. Generally, they would open a ticket with Infor and have them do the work. I like seeing the process, and since I have the knowledge it's quicker and less of a headache... Until I run into issues like this that the Infor employees would not run into since they probably make copies of the DB and can run their scripts in house. I have asked, but all they will allow me to do is use cognos analytics to run scripts against the DB.

EDIT: Same error when using data from the package, like the drag and drop style data elements