HELP! BO XI R4: Using a Stored Procedure as a data source

Posted on 2012-09-04
Last Modified: 2012-09-25
Hi folks,

I have a need to use a prompted stored procedure as the source for a Webi Report.

MS SQL Server 2008

Method 1
I have tried using the Custom SQL feature in the rich client, but for some reason, whenever I attempt to add in the prompts, it replaces the prompts in the custom SQL with hard-coded default values.  Custom sql used:

FROM ads.dbo.partner
   partner.PK_id  =  @prompt(Partner ID Prompt)
   partner.advertiserType  =  @prompt(Advertiser Type Prompt)

EXEC dbo.test_sp_call
 @prompt(Partner ID Prompt)
,@prompt(Advertiser Type Prompt)

Open in new window

I've even created the "Force SQLExecution=Always" parameters in the SBO files to no avail

Method 2
I have also tried creating a stored procedure based unv.  It's a simple universe with 1 stored procedure in it.  I've exported the unv to the repo.  When creating the report in the rich client, the prompts appear ok, but I get the following error:

Database error: [Microsoft SQL Server Native Client 10.0] : No value given for one or more required parameters.. (IES 10901)

Has anyone had any experience/luck implementing this.  Any help/insights with either of these methods would be greatly appreciated.  

Thanks and Regards,
Question by:faumel
    LVL 100

    Expert Comment

    Create prompts in the universe and use them in the report.

    Did you create a universe for your database?


    Author Comment

    Thanks for the quick response!

    I believe I've tried that in method 1.  I've also tried this with prompts created at the webi level.  Just to be clear, when you say "Create prompts in the universe", I'm assuming you mean creating parameters in the "Parameters and Lists of values" section of the Business Layer.  Is this correct?  If so, then please see below.  If not, can you please explain what I am missing?

    I've created universes trying both methods:

    Method 1: Uses a standard universe (via information design tool) based on table imports, and I've created prompts at the universe level.  (If you look at the code snippet, the prompts I am using are from the universe)

    Method 2: Uses a stored procedure universe (via universe design tool) with a single stored procedure in it.

    Thanks again!
    LVL 100

    Expert Comment

    Why do you feel the need to use manually created SQL?


    Author Comment

    Based on the project timeline and the requirements, utilizing a preexisting stored procedure was the best path.  There are other applications that also use the stored procedure, and in order to reduce the number of maintenance points, the decision was made to use the stored procedure, rather than recreate the extensive business logic that the SP contains.

    Any thoughts on how I can do this?
    LVL 100

    Expert Comment

    no.  I have never tried using manual SQL.  I think you are running into a limitation of it.


    Accepted Solution

    I figured it out on my own:

    Method 1 (Using custom SQL) was the wrong way to go.  I was on the proper path with Method 2 (Using a stored procedure universe).

    To solve the issue I was having with this error:  

    Database error: [Microsoft SQL Server Native Client 10.0] : No value given for one or more required parameters.. (IES 10901)

    I changed the connection from an OLE DB connection to ODBC.  BO only supports parameterized stored procedures using an ODBC connection.

    This created another complication.  Since I was running the server on a 64 bit system, it was necessary to create both a 32bit and 64 bit version of the ODBC connection.  This was accomplished by creating the same system DSN (With the same name) using each of these tools:

    32 Bit:

    64 Bit:

    The 32bit DSN is used by the Rich client, the 64bit DSN is used by the openDocument API.

    This solved the problem I was having with method 2, and I was able to continue with prompted stored procedures from there.

    FYI: There are 2 ways to create the prompts for stored procedures using the universe designer.
    1. Use the "Next execution" drop down in the Stored procedure editor.  Select, "Prompt me for a value" (The default is, "Use this value").  You will then be able to type in a prompt message, and even select a universe object to use as an LOV.

    2. Use the @prompt syntax in the "Value" field of the Stored procedure editor:
    @Prompt( '<message>', '<type>', LOV, Mono|Multi, Free|Contsrained|Primary_key, Persistent|Not_persistent, {<default_values>})

    Open in new window

    message: The text of the prompt or prompt label
    type: A (Alphanumeric), D (Date), N (Numeric)
    LOV: List of Values ('<class_name>\<object_name>')
    Mono|Multi: Allow 1 value or multiple
    Free|Contsrained|Primary_key: Allow the user to enter a value (Free), Limit the user to the values in an LOV (Contrained), Use index awareness (Primary_key)
    Persistent|Not_persistent: Save the last value selected in the prompt
    default_values: 1 or more default values (Depends on Mono|Multi).  This field must be encapsulated by "{}"
    When using this approach, with non-alphanumeric prompts (Dates/numbers)  you may have to make the inputs for the stored procedure all varchar and perform a conversion at the stored procedure level to the correct data type.  The reason for this, is when changing the "Value" field, the universe designer checks the datatype before allowing you to save.

    Additional Notes on prompting:
    PROBLEM: Optional prompts are not supported on stored procedure universes
    SOLUTION: Optional prompts are not supported, but defaults are!  In order to accomplish this, you must create the prompt using approach (2) in the FYI section above.  Although it is possible to make a prompt optional using the "optional" parameter (Not described above), it is not supported in Webi when attempting to run/refresh the query.  The solution is to create a default value for the prompt.  Then make sure that the default value is accounted for in the stored procedure.  So instead of having a prompt that is optional, you have a prompt with a default value of "Use default values" (Or something along that line).  In the stored procedure you create an if/else statement to check the value of the parameter for that default value and perform the appropriate steps.

    PROBLEM: Prompts are sorted alphabetically, not by creation order
    SOLUTION: When creating the prompts, Add a number in front of the prompt label.  So instead of:
    Enter Start Date:
    Enter End Date:
     (The order of these would end up reversed when refreshing the document)
    You would use:
    1. Enter Start Date:
    2. Enter End Date:

    Author Closing Comment

    Through a painful bout of trial and error, I was able to answer my questions far better than any of the "experts" who responded.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
    How to increase the row limit in Jasper Server.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    733 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

    21 Experts available now in Live!

    Get 1:1 Help Now