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

Posted on 2012-09-04
Medium Priority
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
  • 4
  • 3
LVL 101

Expert Comment

ID: 38366121
Create prompts in the universe and use them in the report.

Did you create a universe for your database?


Author Comment

ID: 38366183
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 101

Expert Comment

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 38369318
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 101

Expert Comment

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


Accepted Solution

faumel earned 0 total points
ID: 38419194
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

ID: 38431916
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Loops Section Overview
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

809 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