If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Number of retrieved rows exceeds

Started by halciber, 17 Aug 2018 03:05:55 PM

Previous topic - Next topic

halciber

Hello,

I've got a user who is reporting getting an error on 3 reports. The error is "The number of rows retrieved for the user....exceeds the limit of 5000." This confusing because she doesn't appear to have any account limits. A user from the same group has no problem running this report. He appears to have the same rights and privileges as she does. I've compared report settings and user rights, but I don't see anything that suggests that she should have any retrieval limits.

This is on Cognos 11. What can I look at that might help me solve this problem?

Thanks,
Mike Goldweber

MFGF

Quote from: halciber on 17 Aug 2018 03:05:55 PM
Hello,

I've got a user who is reporting getting an error on 3 reports. The error is "The number of rows retrieved for the user....exceeds the limit of 5000." This confusing because she doesn't appear to have any account limits. A user from the same group has no problem running this report. He appears to have the same rights and privileges as she does. I've compared report settings and user rights, but I don't see anything that suggests that she should have any retrieval limits.

This is on Cognos 11. What can I look at that might help me solve this problem?

Thanks,
Mike Goldweber

Hi,

If I had to guess, I'd wager this is an Active Report rather than a regular report? With Active Reports, the data is baked into the rendered report output MHT so it can be used offline. By default, all Active Reports have a defined maximum query row limit of 5000, to prevent an author from trying to render the world's supply of data into the output. You can find this if you edit the report, open the Properties pane, choose the Report object, and click on the ellipsis next to the "Active reports" property. You can also change it to a different value for the report if you wish.

If I had to guess further, I'd wager the report either has a prompt that filters the query as it generates the output, or else you are using security filters in your package. Either of these could explain why one user can generate the output and another cannot - if they are choosing different prompt values or their security filters return different numbers of rows.

Cheers!

MF.
Meep!

halciber

Hi MF,

Thank you very much for helping me with this. You are correct about the Active Report validation option being checked. You are also correct about a filter being used; but this filter looks like it is a data filter. That is, it is looking for specific values in the query. However, I don't see any specific security filter.

Would the security filter be set in the query/report, or would this be a group setting?

Thank you again for your help! I really appreciate it.

Best Regards,
Mike Goldweber

MFGF

Quote from: halciber on 20 Aug 2018 09:51:38 AM
Hi MF,

Thank you very much for helping me with this. You are correct about the Active Report validation option being checked. You are also correct about a filter being used; but this filter looks like it is a data filter. That is, it is looking for specific values in the query. However, I don't see any specific security filter.

Would the security filter be set in the query/report, or would this be a group setting?

Thank you again for your help! I really appreciate it.

Best Regards,
Mike Goldweber

Hi Mike,

Security filters are set up in Framework Manager and are baked into the package. They are usually based on groups from your security provider, eg anyone who belongs to group A has a security filter [Branch] = 'Northern Branch' and anyone who belongs to group B has a different filter [Branch] = 'Southern Branch'. In this case, two authors belonging to different groups and writing/running the same report from the same package would see different results. The whole idea of security filters is that they cannot be overridden within a report, ensuring you retain your analytics governance and prevent people from seeing data they shouldn't have access to. If you want to add/change/remove them, the only place to do this is in Framework Manager (then re-publish your package).

Coming back to your existing data filter in the report, is it hard-coded, or does it involve a prompt of some sort, allowing users to choose different values?

Cheers!

MF.
Meep!

halciber

Hi MF,

Thank you for this detailed explanation. It's a big help. This report does require the user to select a date when running it. So it isn't hard coded.

I spoke to the report's author, and he indicated that he did not use a security filter. Actually, he doesn't know anything about them. Leaving a real mystery.

Regards,
Mike

MFGF

Quote from: halciber on 20 Aug 2018 12:36:40 PM
Hi MF,

Thank you for this detailed explanation. It's a big help. This report does require the user to select a date when running it. So it isn't hard coded.

I spoke to the report's author, and he indicated that he did not use a security filter. Actually, he doesn't know anything about them. Leaving a real mystery.

Regards,
Mike

Hi,

An author wouldn't necessarily know about a security filter - you'd need to check with the person or team who created your reporting package.

I'm assuming if the user is selecting a date, the number or rows for that date exceeds 5000? You can easily change the limit for the report if necessary...

Cheers!

MF.
Meep!

halciber

Hi MF,

Thanks for the additional information. I'll look to see if the reports have additional limits put on them.

I really appreciate your help.

Sincerely,
Mike