Value prompt with partial data and multi-select

[ry1633]

Hi all,

I'm working with Value Prompts in Report Studio.   if I have a column called [PRODUCT] that has codes in it like "ASASD-1672 some info".   The column data could start with any letter from A-I, and it also includes codes that start with a "5".   To the user, in general the letter codes mean one thing to the firm, and the "5" codes mean something else,  but it's possible to have the combination of an E or a 5 code in one grouping as well.

Can I put these into a Value prompt (preferably a check box list or something similar) - and have the ability to click more than one in a Multi-select?

thanks for the help :)

[cvamarley]

sorry..its confusing..can you please explain this point 'but it's possible to have the combination of an E or a 5 code in one grouping as well.' so that i can try to help you out.

[ry1633]

Sure I'll try to do it and still be generic without giving away too much org-sensitive data.  I have a column of [product] that  has codes in it like "ASASD-1672 some info"  These codes could start with any letter from A-I, or start with numbers 1-9 like this "5xxxxxx - some other intof".   To the user, in general the letter codes mean one thing to the firm, and the "5" codes mean something else in terms of a possible grouping like this:

"Further Manufactured" Product Code begins with: A,B,C,D,E,F,G,H,I
"Diagnostic" Product Code begins with: E or 5

So can I do this in a value prompt or should just do a straight Value Prompt check box and provide a textbox of instructions?   Either way I'd need to also have the ability to multi-select values, and it'd be nice to have Shift-Click and Ctrl-Click functionality for ranges of values.

[bdbits]

Assuming this is relational... you'll need to create a calculated item in the query behind your prompts. This is then just standard prompting - dropdowns, radio buttons, trees, whatever. Then in your query, you can use these in detail filter expressions with the "starts with" operator.

But, this kind of inspection within a field indicates to me that you might want to look at whether these substring "codes" have value enough to be pulled out as separate attributes or even tables/dimensions. That is beyond a report issue of course, but something to consider and discuss with your modeller.

[ry1633]

I'll have to dig into that more - from the modelling end.

For the time being - how do I set a prompt to Multi-select and can I have the ability to Shift-Click or Ctrl-Click multiple entries in the prompt?

[bdbits]

Give this a shot.

   
• Make sure your filter is [package].[item] in ?pMyPrompt? and not [package].[item] = ?pMyPrompt?.
• If you want to let Cognos generate the prompt page, you are done. :D
• If you have a custom prompt page...
• Use a value prompt control for pMyPrompt.
• The control will have a property called "Multi-select"; set it to "Yes". Intuitive, huh?  :o
• Set the "Select UI" property to list box (I think that is the default) if you want shift-click and ctrl-click. Your users might like checkboxes, but then the checkbox replaces ctrl-click and you lose shift-click to select a range. Your call.

Run the report.

[ry1633]

bdbits, This mostly works just fine how I'd envisoned it.  But I think I found a potential problem.    What happens if I want to select two groups of ranges?    Like in my Product column, I have two groups Product IDs - IDs that start with 'E' or '5' that I might want to have the user query for both separately or as a group.

How can I set the prompt list box to behave like that?   Say I want to select two ranges at the same time, that are in the same column, but not adjacent to each other? -ry

[bdbits]

Entirely possible I am misunderstanding, but... you should be able to ctrl-click as many entries as you want from anywhere in the list. The shift-click thing to select a range is only going to work for one of the ranges; that's just how listboxes work (not just in Cognos either). But you could shift-click the first group, then ctrl-click each member in the second group. Not so great if you have a lot of members I know, but that is the only way that comes to mind.