Microstrategy with Prompt and Stored Procedure

[itrave007]

Hi there,

I am trying to using a promot in Microstrategy based on calling a stored procedure in Oracle. How do I go about doing it. First calling the stored procedure or function an than the result of the call, to populate a prompt in which the user selects and than I run the report based on the selection.

 

[dmcmunn]

If you can use an Oracle 'function' instead of a stored procedure, you could setup a filter containing the function inside a MicroStrategy ApplyComparison(). Next, embed the filter inside a MicroStrategy filtered prompt.

Example:
To return only months in the most recent two specific fiscal years...

Create Filter: "Most Recent Two Fiscal Years Filter"
ApplyComparison( " #0 in (Select fiscal_year from udf_most_recent_two_fy) ", [month]@[fisc_yr] )

Create Prompt: "Valid Months Prompt"
Filter this prompt by "Most Recent Two Fiscal Years Filter"

This would present only the months for the most recent two fiscal years.

Best,
dmcmunn