Passing Element Prompt Answers to Database Passthrough Functions

A common request is for the ability to use an element prompt to choose a month or day, and then based on that selection, choose a range such as a rolling months or rolling days.  The problem is that if you build out this prompt, ApplyComparison(“#0 in (select month from tblmonth where month >= #1”, Month@ID, ?Month), this would result in the SQL:  “a11.Month in (select month from tblmonth where month >= a11.Month in(6)” which is not valid SQL.  Since an element prompt generally appears in the Where clause, the engine resolves them all using an in operator instead of =.  Fortunately, MicroStrategy 9.0 introduced a new feature to support this type of scenario

The trick is that instead of using the macro #1, which traditionally will pass the field at the end of the function, we use the new macro #E1 to tell MicroStrategy that we’re passing an element prompt answer.  The engine will now adjust the query so that instead of generating a11.Month in(6), it will simply pass 6.

For more information, you can check out this Tech Note on the Knowledge Base.

You may also like...

8 Responses

  1. Rohit says:

    Thanks, this is really cool. I always used to do a substring on top of find string in such scenarios!

  2. prabhakaran says:

    Hi Bryan
    what i found out with this approach is ,when no elements is selected complete passthrough functions is ignored in the where clause of the SQL

    For example:when year prompt is not answered, i may wish to set Year=2012

    how to tackle this scenario? any workaround available?


    • Bryan says:

      Right, if there is no answer, then it’s just like not answering an optional prompt: it gets ignored. If you want to have a default, I’d suggest setting a default option on the Prompt itself. If that value isn’t overwritten with the elements passed in the URL, then it’ll use that 2012 default. I don’t think you could simulate that via the URL, because you’re limited in what you can really do there.

    • prabhakaran says:

      Thank you Bryan for your time.
      Here is my case ,In a report i am having date prompt and also static filter which will resolve to yesterday’s date(currentdate-1).

      If i am answering date prompt,i need data for that date otherwise static filter should be applied(currentdate-1).here i cannot set any default in date prompt because current date keeps on changing daily.

      please advice.


    • Bryan says:

      I usually use filters for that, and then make an Object Prompt for my Date Selection. The user can then easily choose from options like “Yesterday” or “Last Week”. One of the options I include is “Custom Date Range” which then has value prompts. You could set it up like that. To answer an Object Prompt, the syntax is:
      Filter is Object Type = 1. Use %20 to handle spaces for your OBJECTNAME.

    • prabhakaran says:

      Thanks again! this is really helpful

  3. Andrea says:

    Hi Bryan, do you know if is possible do the same thing with the Object Prompt instead the Element Prompt?
    I want to pass the name of the object selected (in the obj-prompt-screen) in a SQL.
    Thanks in advance.

Leave a Reply

Your email address will not be published. Required fields are marked *