ApplySimple() SQL Server UDF and Object Prompts ?

[dmcmunn]

Greetings from Business Requirements Hell...

Has anyone found a way to use a Microstrategy object prompt to supply a parameter to a SQL Server 2000 user-defined function as part of an ApplySimple() ? The KB indicates it has only been tried and supports the use of "value prompts". I confirmed this with MSI tech support the other day, but was wondering if any of you savvy folks out there have pulled this off ?

Problem:
User selects a fiscal year (FY2003), year (2003), year+quarter (20032) or year+month (200303) and I need to transform that twice (1) into BOTH the current and previous fiscal years associated with the time frame selected and (2) into a filtered result set of non-date-like identifiers from another lookup table that were active during either the current or previous fiscal years. I'm using the list of codes for the current/previous fiscal year's as a report filter embedded in a series of reports with this same requirement.

This long drawn out story is because the business needs to have all codes show up in the final report regardless of fact table activity in either the current or previous fiscal years. Report must show current/previous years for purposes of comparison. One year seems simple. But the date object prompt-based report filters for the other date-filtered metrics on the report always want to reduce the result set to the selected fiscal year only which leaves the report 1/2 as long as requirements say it needs to be.

I'm open to suggestions...

Best,
dm

 

[entaroadun]

Could you please give a specific example of a report requirement and the desired output?

 

[dmcmunn]

I was able to get this resolved after using a fiscal year value prompt to filter the date object prompts to only valid attribute elements for the fiscal year. So, in the words of the immortal Roseanne Roseannadanna, "Nevermind.