Solved

Quick Report "Export To Excel" fails

  • 24 September 2021
  • 1 reply
  • 28 views

Userlevel 5
Badge +9

Hello,

We have run into this issue in several scenarios: A Quick Report works fine, but if we try the “Export to Excel” option of running the report we get something like the following:

 

 

One scenario where this happens is when using “WITH” in the query. That can be overcome by wrapping the WITH in a SELECT.

Another scenario it happens with is when using parameters such as a date range, the kind that makes the calendar popup available.

But the most recent issue was with a a query using a pipelined result from a custom package method call, for example:

 

SELECT * FROM TABLE(BELL_CUSTOM_REPORT_API.GetCustomerInfo('B%'))

 

This is a custom API with a method called GetCustomerInfo() that uses PIPELINED data to return a “table-izable” result. It works fine in SQL Developer and when running the Quick Report directly. But if I use “Export to Excel” from the Quick Report screen I get the error listed in Excel that you see above. Putting “&AO” in front of the package name does not help.

Anyone know a way around this? Using nifty tricks in Quick Reports is not very helpful if the resulting queries are not usable for generating Excel output, as a lot of our business needs end with, “Then I would like the results in an Excel spreadsheet that gets emailed to me at a scheduled time...”

 

Thanks,

Joe Kaufman

 

icon

Best answer by sutekh137 24 September 2021, 18:21

View original

1 reply

Userlevel 5
Badge +9

Sorry, I was too quick to post before making sure I looked at all relevant questions. I found a thread about granting SELECT to a certain view to IFSSYS like so:

GRANT SELECT ON {fully-qualified View name} to IFSSYS;

So, I tried granting EXECUTE to my custom reporting package like this:

GRANT EXECUTE ON IFSAPP.Custom_Report_Package_API to IFSSYS;

and that worked! Didn’t need to refresh any cache or anything, it just worked the next time I tried “Export to Excel”.

Hope this helps anyone else getting errors -- it comes down to IFSSYS permissions, apparently.

 

Thanks,

Joe Kaufman

Reply