Hi All,
I have to define a mandatory prompt in Framework Manager within a query subject so that whenever a user pulls a column from that query subject in QS, he will be prompted for a value.
now the problem is the prompt type that cognos gives is an Edit Box prompt. Instead of this I need a Select Value prompt. I have tried by changing the Prompt Info property of the query item to Select Value, but still it is giving as an Edit box.
When the 'Usage' of the prompt is changed from 'Always' to 'Optional', Select Value prompt is given but it is not prmpting unless the run button is hit in QS.
Can anyone tell me is this the way Cognos works or i can achieve this somehow?
PS: I have tried both the syntax for the prompt
column in ?prompt?
column in #prompt()#
Appreciate your quick response.
in (#promptmany('Add one or more values!')#) ????
Was this the answer to your question? I'm looking for the exact same thing, but it doesn't seem to work as expected. :(
In order to force Cognos to generate a value list for the user include a source column in the prompt macro.
prompt macro parameters:
prompt/promptmany
(
Parameter Name,
Datatype,
Default Value,
Pretext,
Source,
PostText
)
example:
#promptmany('Year','integer','year(getdate())','','[Sales].[Time].[Year]')#
This looks like exactly what I'm looking for. Unfortunately it doesn't work on my current model.
Let me tell you the steps that I'm taking...perhaps you can see where I am doing something wrong...which is obviously the case :)
First I create a query item
In the Expression definition I type:#promptmany('UOM','','','','[Database view].[T_DIM_TIME_YEAR].[YEAR] ')#
If I then try to test the QI I get a prompt screen of some kind. If I double click on the values I can create a list of values. If I enter only '2009' I get a result of all '2009'.
That's ok (and some result) but I would like to populate that list with results that a user can choose from.
If I enter two values I get an error message. Mentioning there is an error before or near position 8 of "'2010';" in parsing text '2010';'2009'
What is your filter exactly? Make sure it's set up correctly for a set instead of a single value.
Euh...filter? ???
I would like the user to select a single value out of a list of values. That selected value is the value I want to use in another QI. In that other QI I calculate the value that should be displayed based upon the selection that the user has made when he was prompted to do so.
A promptmany macro will return the results separated by a semicolon. SQL knows how to deal with separated values like that in a filter. For example:
[Year] in (#promptmany('UOM','','','','[Database view].[T_DIM_TIME_YEAR].[YEAR]')#)
You wouldn't put '2002';'2003';'2004';'2005' into a query item, but that is what the macro returns. In order to convert it into a string, you can use another macro function SQ.
For example:
case when #sq(promptmany('UOM','','','','[Database view].[T_DIM_TIME_YEAR].[YEAR]'))# contains '2005' then 'YES' else 'NO' end
Ok...I feel I'm getting so close, that it would be a pitty to give up on this possibility.
I used the case statement to evaluate the chosen value and present a result.
CASE (#promptmany('UOM','','','','[Database view].[T_DIM_TIME_YEAR].[YEAR] ')#)
WHEN '2009' THEN '2009 is gekozen'
WHEN '2010' THEN '2010 is gekozen'
END
This works fine. If I type in 2009 it displays '2009 is gekozen' as result. If I type in 2010 it displays '2010 is gekozen' as result. This result (obviously in my actual package it will be a different value) can be used in another QI. Or my result in the QI can be more complex instead of this simple sentence. But this is basically what I'm looking for...
However there is still one thing missing: the user still needs to type the value himself. I was hoping that by referring to the '[Database view].[T_DIM_TIME_YEAR].[YEAR] ' a list with years would pop up. If there is another way (even hard coded) to provide the user a list of values to choose from I would really like to know it!
Thanks again!
Okay, the reason you're not seeing the list is because you're testing the query from within FM. When you publish the package, the list will be generated automatically.
The case statement is a bit problematic. Remember, when you select multiple values Cognos will return something like: '2009';'2010'. The case statement will fail for two reasons. First '2009';'2010' is not a string and cannot be parsed. Second, even if it was a string '2009;2010' that will never equal 2009 or 2010.
A better solution would be to put the prompt macro in the filter
[Database view].[T_DIM_TIME_YEAR].[YEAR] in (#promptmany('UOM','','','','[Database view].[T_DIM_TIME_YEAR].[YEAR] ')#) and change the case statement to
CASE [Database view].[T_DIM_TIME_YEAR].[YEAR]
WHEN '2009' THEN '2009 is gekozen'
WHEN '2010' THEN '2010 is gekozen'
END
I tried to publish the package, but still no list is being generated. And I'm sure that the year dimension contains data.
There is no need for the user to select multiple values. The user should be prompted with a list and it is required that he picks one value. With that value I can then create a case statement.
I think the solution that you are proposing does something like that. I'll give that a shot.
Now I'm using a table that is really part of the model and joined to the other tables (so the filter will also filter my facts), but in future I will have a separate filter to base my prompt and filter upon. Do you think that will work?
Unfortunately still no list to select from in Query Studio. However....the filtering works. And the case statement also returns the correct value based upon my "selection".
It's very strange that the filter is not generating a list. Check the properties of [Database view].[T_DIM_TIME_YEAR].[YEAR]. If the Prompt Type is set to Edit Box, try changing it to Server Determined or Select Value.
If the users are only prompting for one value, change the prompt macro to prompt() instead of promptmany()
Looks like I don't need to add the value. I only need to add the filter with the prompt in it.
So if I run it now....first I need to fill in (type) a value, then I get a drop down with only the value that I just typed.
I'm still a little bit at a loss... :(
Changed the promptmany() to prompt()
Removed the case statement (first it's useful to filter a complete dimension based upon a dropdown based upon a table)
Also changed the Prompt Type.
In all cases the result is as described above :(
Hello folks, i' m using the queryItem prperty of macros syntax to obtain a list of values to request for filtering but i ve noticec that cognos reorder the values in the list in alphabetical order even if i ordered the item in a descending order.
IT is possible to force ordering or prevent cognos from ordering the values in the macro?
You asked that question here as well
https://www.cognoise.com/index.php/topic,35113.0.html
https://www.cognoise.com/index.php/topic,24105.msg75293.html#msg75293
4. Double posting. Please do not post the same question in multiple threads. If you want a topic moved to a new thread, please just ask a moderator - we're happy to help. Ideally you will post your topic in a new thread anyway, and not need this service. Posting the same question in multiple places can be confusing for people looking for an answer later on, and can also mean that people spend time answering questions that have already been resolved elsewhere.