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.
Thanks, this is really cool. I always used to do a substring on top of find string in such scenarios!
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?
Regards,
prabhakaran
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.
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.
regards,
Prabhakaran
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:
&objectsPromptAnswers=OBJECTGUID~OBJECTTYPE~OBJECTNAME
Filter is Object Type = 1. Use %20 to handle spaces for your OBJECTNAME.
Thanks again! this is really helpful
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.
I don’t think it is. This is something special for the element prompt I think.