How to create a universe based on stored procedures with input parameters having list of values in a prompt

[Former Member]

Hi Experts,

I have created a universe which includes stored procedure with one input parameter called VersionId. This stored procedure fetches the data from multiple tables by filtering it with VersionId. The universe I have created got added successfully in Web Intelligence Report and while running the report it is giving me prompt to enter VersionId in the text box. Please refer the attached screen shot for the prompt window. My question is, "Can I give the drop down instead of text box as in the screen shot" i.e. when user refresh the data, he/she will able to select the VersionId from the drop down such as 68,69,70,...

JFYI:- I am keeping the VersionId in the SQL table.

Or else please let me know any other solution to achieve this task.

Immediate response will be HIGHLY APPRECIATED...

Regards

Nitin

[Former Member · Accepted solution]

Below is how you can Implement it.

1. You need a separate table or Derived table for you list of Values. Lets call this table LOV.

2. Create Class and objects from this table for which you need the LOV's and hide the class and objects since the main purpose of the objects is to be provide list of Values for your Prompt.

3. In your stored Procedure Query use the @Prompt function as below

the Object we created for using as LOV will be used in the circle area below.

Your End Result in the Prompt area will look as below

Hope this was helpful

www.megibi.com