Newbie: Having trouble with mandatory/optional prompts/filters (SOLVED)

[hanfrie]

Hi all,
As a newbie in Cognos reporting I'm having trouble finding a solution for the following:
I need to make a report that starts with a prompt page containing 2 mandatory prompts (PERIOD, MAINCUSTOMER) and an optional value prompt for an additional multi-selection of SUB_CUSTOMERS, in which the first prompt is PERIOD, which value is used as a filter for the second value prompt MAINCUSTOMER (shown as a radio button list), to display only those maincustomers that were active in the selected period, and followed by a third prompt, which needs to be optional, were the first two prompts are mandatory, in which additional SUBCUSTOMERS can be selected, and which also were active in the selected period.
What I managed so far is a prompt page, containing three value prompts, the second and third having a Cascade Source equal to the parameter value of the first prompt. The first and second prompt are Required, the third not. No issues so far.
Now lets look at the querie for the report itself. This needs to have these filters:
- period = PERIOD
- customer = MAINCUSTOMER
  or
  customer in SUBCUSTOMERS

and this is were it goes wrong. The usage definition of the filters override the Required setting of the prompt itself on the prompt page. Check. So the period filter is required, as should be the second prompt, MAINCUSTOMER, but it needs to be joined with the optionally selected SUBCUSTOMERS. When I define this filter as Required, the third optional prompt then also becomes mandatory, which I obviously don't want. When I define the filter not to be Required, and you do not select a value in the third prompt, it disregards the filter completely, thus also not filtering on MAINCUSTOMER.

What do i need to adjust here? Is there anyone out there that can point me to the right direction?

[MFGF]

Hi all,
As a newbie in Cognos reporting I'm having trouble finding a solution for the following:
I need to make a report that starts with a prompt page containing 2 mandatory prompts (PERIOD, MAINCUSTOMER) and an optional value prompt for an additional multi-selection of SUB_CUSTOMERS, in which the first prompt is PERIOD, which value is used as a filter for the second value prompt MAINCUSTOMER (shown as a radio button list), to display only those maincustomers that were active in the selected period, and followed by a third prompt, which needs to be optional, were the first two prompts are mandatory, in which additional SUBCUSTOMERS can be selected, and which also were active in the selected period.
What I managed so far is a prompt page, containing three value prompts, the second and third having a Cascade Source equal to the parameter value of the first prompt. The first and second prompt are Required, the third not. No issues so far.
Now lets look at the querie for the report itself. This needs to have these filters:
- period = PERIOD
- customer = MAINCUSTOMER
  or
  customer in SUBCUSTOMERS

and this is were it goes wrong. The usage definition of the filters override the Required setting of the prompt itself on the prompt page. Check. So the period filter is required, as should be the second prompt, MAINCUSTOMER, but it needs to be joined with the optionally selected SUBCUSTOMERS. When I define this filter as Required, the third optional prompt then also becomes mandatory, which I obviously don't want. When I define the filter not to be Required, and you do not select a value in the third prompt, it disregards the filter completely, thus also not filtering on MAINCUSTOMER.

What do i need to adjust here? Is there anyone out there that can point me to the right direction?

Hi,

I think your real issue is that there is no distinction between main customers and sub customers in terms of the data items used. They are both represented in the customer item (or seem to be from what you describe here). Is that correct?

I think the best bet is to merge the prompts for main customer and sub customer into a single value prompt which allows multiple selections and which is defined as mandatory. In that way, the user must select at least one customer but could optionally select more. Would this work?

Cheers!

MF.

[hanfrie]

Hello MF,

Thank you for your response. I see now that my initial drawing of the problem has some blank spots. Sorry for the needed guessing here.
Yes, you are correct in the assumption that there is no distinction between main customers and sub customers in terms of the data items used. The reason for using two prompts is that I need to select other data for both types of customer, and only the user can tell who's the main customer. I have one data query which needs a filter containing only the main customer, and several data queries that use the possible combination of all selected customers.
I find it not desirable to be forced to make the first mandatory prompt where the user is to select the main and the extra subcustomers (or just the main) and then to be forced to answer a second mandatory prompt in which it selects the main customer again as an indication that this one is the main (or just the other way around for that matter), even when the user initially selected just one customer, and by doing so telling that it is the main customer. That's stupid, and there must be a way to avoid this I'm sure, but don't have the knowledge (yet) of how to accomplish that.

So, the query I spoke of earlier, has this filter:
DATA.CUSTOMER = ?MAINCUSTOMER? OR DATA.CUSTOMER IN ?SUBCUSTOMERS?

When I set this filter as optional, and this optional third prompt is not answered (so no extra subcustomers are selected) this filter will not be in effect, and thus no filtering on MAINCUSTOMER will take place. It will do just fine as long as something is selected in the third prompt.

I hope I clarified the situation by this.

[BigChris]

I'm not sure if this will work in the way that you want, but couldn't you just alter your prompt to be:

DATA.CUSTOMER in ?CustomerList?

You can then have your second prompt for ?MainCustomer?

When the user runs the report he puts all the customers he's interested in into ?CustomerList? and identifies which one is the main customer in ?MainCustomer?. If they're only running it for the main customer then those two will be the same. ?CustomerList? should presumably always contain ?MainCustomer?...and you might need to check for that. Both prompts will be mandatory, and effectively independent of each other.

[Lynn]

You could add a static choice to the sub customer prompt control indicating no selection and set that as the default. This leaves the prompt control required, but because a default is indicated the user doesn't need to do anything if there isn't any choice to be made.

Then construct your filter to take both possible scenarios into account.

Code Select Expand


(
  DATA.CUSTOMER = ?MAINCUSTOMER?
  AND
  ?SUBCUSTOMERS? contains ('None Selected')
)
OR
(
  DATA.CUSTOMER = ?MAINCUSTOMER?
  AND
  ?SUBCUSTOMERS? not contains ('None Selected')
  AND DATA.CUSTOMER IN ?SUBCUSTOMERS?
)


[BigChris]

I hesitate to question your logic Lynn, but does that second clause hold water?

(
  DATA.CUSTOMER = ?MAINCUSTOMER?
  AND
  ?SUBCUSTOMERS? contains ('None Selected')
)
OR
(
  DATA.CUSTOMER = ?MAINCUSTOMER?
  AND
  ?SUBCUSTOMERS? not contains ('None Selected')
  AND DATA.CUSTOMER IN ?SUBCUSTOMERS?
)

shouldn't it be
...
OR
(
(DATA.CUSTOMER = ?MAINCUSTOMER? or DATA.CUSTOMER in ?SUBCUSTOMERS?)
AND ?SUBCUSTOMERS? not contains ('None Selected')
)

I'm could very easily be wrong there...please let me know if I've got that round my neck

[Lynn]

 :-[

Good catch Chris!! I am quite sure you are correct. Not sure if the approach is going to meet the OP's requirement, but thought I'd throw it out there in case.

[hanfrie]

I think Lynn has won the price here (with some final help from BigChris). I've no experience in putting a static default choice into a prompt, but if it works in the way you described it, I think it should work. It looks solid to me at least. At the moment I'm at home, but will try this tomorrow right away. I'll let you know the result.

[edit]
I couldn't wait till tomorrow and tried this solution and yes it works. My thanks to Lynn and BigChris. I'll be able to sleep now.

[edit]
After some testing I found that the supplied query had another issue: the use of "CONTAINS" automatically implies a SINGLE selection, and I need a MULTI selection, so I changed it in the following;

(
  DATA.CUSTOMER = ?MAINCUSTOMER?
  AND
  ('None Selected') IN ?SUBCUSTOMERS?
)
OR
(
  (DATA.CUSTOMER = ?MAINCUSTOMER?
  OR DATA.CUSTOMER IN ?SUBCUSTOMERS?)
  AND
  NOT (''None Selected'') IN ?SUBCUSTOMERS?
)