Filter Using Prompts With Substitued Values List

[cslangston]

Greetings,

Is there a way in Framework Manager (v10.2.1) connected to a Teredata warehouse to have a prompt display a list of "named" values, that when selected, gets substituted to the actual DB value to be used for filtering?

Here's the scenario:

* A table with a column (size_ind) with a value of either 'Y' or 'N'
* A query subject that needs to filter based on that column. There are three options:
  1) 'Y' only records
  2) 'N' only records
  3) All records

The size_ind essentially indicates if a value in that row is large (N) or small (Y).

Rather than the user being prompted to enter a 'Y' or 'N' value, it's more desirable to filter the data based on a prompt to select LARGE only, SMALL only or BOTH.

So the user will be prompted (mandatory) to choose LARGE (substituted as 'N') or SMALL (substituted as 'Y'). Choosing both will select all records.

I've read a lot of information about prompts but none, so far, that describe this scenario. I've experimented with parameter maps (setting key LARGE to value N and key SMALL to value Y) but not being an experienced FM developer, I haven't figured out how to get it to work.

I've also tried many variations of the prompt and promptmany macros but the main issue there is that I haven't been able to get it to display a list of values to choose from - much less a "named" (LARGE and SMALL in this case) that could be translated to the actual DB column values (Y and N in this case).

Any idea, thoughts, suggestions, or reference material that can help me get this to work will be immensely appreciated?

[Lynn]

Have you looked into the display and use item properties and the filter item reference property?

In a typical star schema scenario you don't want to render prompt choices from your fact table since the query would need to get a distinct list of choices across all your fact rows to present what the user can select.

Instead you'd have a dimension/look up table containing these choices. There are a series of properties related to prompt control under "Prompt Info".

The concept of "both" as an explicit choice in a prompt is something I'd suggest you avoid. If a user doesn't make a selection they will get everything. This is an easy thing to address in training.

http://pic.dhe.ibm.com/infocenter/cbi/v10r1m1/index.jsp?topic=%2Fcom.ibm.swg.ba.cognos.ug_fm.10.1.1.doc%2Fc_display_item_show.html

http://pic.dhe.ibm.com/infocenter/cbi/v10r1m1/index.jsp?topic=%2Fcom.ibm.swg.ba.cognos.ug_fm.10.1.1.doc%2Fc_alternative_query_item_filters.html

[cslangston]

Lynn,

I agree; it's not a good idea to get a distinct list of choices from the fact rows but wasn't sure how to create a logical look-up table. I don't have the option of having one created in the database so that's why I was experimenting with parameter mapping. Another idea I found was to create a new namespace and a query calculation and use that. Seems promising but haven't figured out how to make it work.

I've looked into the "Prompt Info" properties but they're specific to query items and I'm looking to place this in on the query subject. I should have mentioned that this is to be on a prompt page. One quirk I've encountered with FM is that on a query item, when I specify "Select Value" - expecting a drop-down list - it still comes up with an "Edit List" prompt.

As for the LARGE, SMALL and BOTH options; the analyst most often will want only the LARGE records but in some cases needs to see only the SMALL or BOTH and therefore should have the option of choosing only SMALL or BOTH. I agree that BOTH shouldn't be a choice and will be accomplished by selecting both LARGE and SMALL records.

I'm reviewing the links you provided and hopefully they'll provide a clue on how to make this work.