Cascading Prompts problem

[rcaesar]

Hi there,

I have researched into all the discussion on cascading prompts, and followed them. But I still have a problem.

Here is the scenario, suppose:


Prompt 1: Manager (I have a long list)
Values: Tom, Harry, Jane

Prompt 2: Area
Values: North, South, East, West

(A manager can have several areas)

When I select say Tom , the prompt cascades fine, and say I get North, and the resultant report is fine too (I used concatenation).

But, if I were to select all the vales in prompt 1, I get
in Prompt 2 multiple values, like North, North, North, South, South, South, etc.

I would like it to show only North, South, East and West (Since I selected all Managers), and not repeat values.

The second prompt uses the concatenation, but displays the Area.

The concationation I used is (Manager + Area), and my final report has

MgrArea in (?Manager + Area?)

Any suggestions as to how I would be able to accomplish what I want.

**Also is there a way to have the prompts in one page? I don't think its possible, but just to make sure.

Thanks as always, you guys always come through.

Rgds.

 

[griffindm]

There are at least two ways to do this. The first is a little involved, and uses the insertion of an 'ALL' option in your 1st cascading prompt report. Then filter the second report to give a concatenation returning a distinct value for the combination of the string 'ALL' and the regions. In this case the values returned will be:

ALL-South
All-North
All-East
All-South

Then use filter logic on the 'manager' value of 'ALL' and the four regions to give the correct result in the final report to return all regions for all managers.

The second approach works in similar fashion, but uses a second prompt in the first report to achieve the ALL option, rather than using SQL tricks to get the ALL into the manager picklist.

Hope this helps.

Dave Griffin




The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[rcaesar]

Thanks Dave,

From your response it looks like I won't be able to get exactly what I want.

For example, if I were to select only 2 managers, I would possibily get South, South, East, East in the second prompt.

I was hoping, if I could get distinct values: South, East in the second prompt listing.

I think your solution would give me All-South, All-East in the second prompt.

I have worked with powerprompt, using javascript a lot, and there I could use something like

Select distinct area where manager in somevar ....

(somevar holding previous selections for managers).

Unfortunately, in this particular report, I have contend with also limiting the prompts to managers having contracts >((today() + 90 days)) only. Which I am having a tough time in getting resolved in powerprompts

So, I was hoping I could thrash out a solution using impromptu prompts in this case.

In any case, thank you so much - you have always come thru.

Rgds

 

[griffindm]

I'm not sure where the problem happens in the second report. With the returned values of All-South and All-East, you have a distinct set of regions. The other required step is to use this data in the final report in the following way. (This assumes you parsed the value for manager from the concatenated string passed to the final report, called inmgr below, and region, called inregion).

[rest of filter] and region in (inregion) and 1 = If ('All'= inmgr) then (1) else if (manager in (inmgr)) then (1) else (0)...

Let know if this works for you.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[rcaesar]

Thanks Dave again,

I will try your suggestions and will let you know how it pans out.

Rgds