I have a need to use a prompted stored procedure as the source for a Webi Report.
MS SQL Server 2008
BO XI R4
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:
SET NOCOUNT ON
partner.PK_id = @prompt(Partner ID Prompt)
partner.advertiserType = @prompt(Advertiser Type Prompt)
@prompt(Partner ID Prompt)
,@prompt(Advertiser Type Prompt)
I've even created the "Force SQLExecution=Always" parameters in the SBO files to no avail
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,