Custom group in prompt?

[thart21]

Hi,

Is there a way to do custom groupings in a list box prompt or drop down in Cognos 10 Report Studio? I have multiple customer #'s that I want to show as one selection for the user.

Customer #123
Customer #456
Customer #789

Show in list box as Customer 'A'

Customer #abc
Customer #def
Customer #ghi

Show in list box as Customer 'B'

Currently all are listed separately in my multi-select list box but want to make it easier for the user and eliminate their having to select 5-7 customers at a time.

Thank you!

Toni

[charon]

hey thart,

create custom groups through a set.
You need a dim or drm data source though..in case your package is not relational, go right click on the rows (customers), click
EDIT SET, then CREATE CUTSTOM GROUP.
fill your custom group...and confirm.
as result, you have a custom group/ set which is a data set as well. base your prompt on this data item, and voila. should work i think.
the other option would be to create a sql statement like Case when customer 123 then....and so on..

hope this helped, have a nice weekend
greetz :P

[MMcBride]

Another option would be go to your prompt Query create a new Data Item with a case or If then statement to segment these customers and use that to prompt with.

[thart21]

Thank you both so much for the ideas, I will check them out, exciting stuff!!  Enjoy the weekend!

[thart21]

I went with the CASE statement and it is working, thanks. Now I need to get it to just show once instance of the Customer group.

In my list box it is showing it 1 time for each Customer

A
A
B
A
B
B

Looking for somewhere to place Select Distinct. In my prompt, I am using 'Sold To Nbr' as my Use Value and 'CustGroup' as my Display Value.

Thanks!

Toni

[Lynn]

If your prompt query has the auto aggregation property set to Yes, then it should give you a distinct list. If you have it set to auto group and summarize already, then I suspect the problem is that your "use" value is the individual customer number and your "display" value is the custom group you created. If you specify the detail customer number as the value to use then it will have to show every one because it would need to know which customer number to use.

You could change your case to show A (123), A (456), etc and then sort by that column and allow multi-select.

Another option is to set your custom group as both use and display values, then alter your query to filter using the group logic. This may not perform terribly well depending on your data volumes.

It is first thing on a Monday morning for me, so take all the above with appropriate caution  :)