Is there a way to return data from a stored procedure in BusinessObjects XI R2

Posted on 2011-04-20
Last Modified: 2013-11-15
I have an existing stored procedure that was a part of a Crystal Report.  I would like to move this to a WebI report, which does not appear to support stored procedures out of the box.  I am using an Oracle database, which seems to have some limitations when it comes to views, namely, that you cannot perform DML operations from select statements.  That all means that, 1) in order to create my WebI report, I have to have a view (or table) to select from, and 2) I cannot use data manipulation operations to do this, because using a view would require those to be executed in a select statement.

My question is, what are my options?  It seems that, with the complexity of a data-warehouse, it's would be difficult to access and manipulate all data in cursors and views.  Is there something I am missing, and WebI can execute stored procedures?

Thanks a lot for the advice, I'm new to BusinessObjects, and could very well be missing something very obvious.

Question by:zmouser
    LVL 15

    Accepted Solution

    The best approach would be transform the procedure in a function that returns a table.
    Then, you can create a view in the database that will call the function, pretty much like this:

    create or replace view V1_Function as select * from table(function_name());

    Then, the view can be mapped on BO Universe.

    Let me look on some documentation regarding the function returning a table.
    LVL 15

    Expert Comment

    by:Walter Ritzel
    Here is the link:

    I have tested the examples, they work perfectly.
    LVL 26

    Assisted Solution

    by:Kurt Reinhardt
    In general, the answer to your question is no - you cannot explicitly use a Stored Procedure as a data source for WebI.  WebI reports use Universes. That being said, wpcortex provided some great technical information.  You'll need to take the procedure and manipulate it in such a way that you can reference it's output from within your Universe.

    LVL 100

    Expert Comment

    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now