Passing values 'ALL' through Cascading prompts

[dm21]

Hi,

How can do the follwoing.
I have Region , City , Sales Team , Employee Cascading promtps.

Employee is filtered by Region , City and Sales Team.
What if I have multiple Sales Teams and I need to choose 'ALL' employees.

How can pass values of Sales team and employee to the report?

Thank you.

 

[griffindm]

dm21,

See thread401-624832 for a discussion of how to do this with Cascading prompts.

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

 

[dm21]

Hi Dave,

Thanks for the information. But this is really not solving my problem.

What I am trying to achive is, say if I have chosen two sales teams A and B and in my last prompt of employee I need to choose all the employees with value 'ALL'. With concatination Users will have to choose two values like 'A-ALL' AND 'B-ALL'

The other promblem I am facing is, I am working with Sybase database and sybase database does not allow UNION operator in a view. So I can not add 'ALL' record to my Employee query through view. Instead I am modifying SQL in impromptu and with union query. But some times those query generates impromptu error causing it to exit the application. As per cognos they say that they will stop supporting that feature in the future.

Thanks.

 

[griffindm]

dm21,

Drop the concatenation and make the filter in the last picklist report use an IN clause, as in
Sales_team IN (?picksalesteam?) ...
and you should get just those employees that are in either sales team.

As for the Sybase limitation on Union views, I find it hard to believe that they could leave such a basic ANSI SQL feature out. I'll check over on those two forums (ANSI and Sybase) to confirm.

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

 

[griffindm]

dm21,

Check out thread187-68159. It looks like a Union View is only supported in Sybase as of version 12.5.

If your version is before that, any chance you could add an "ALL" row directly to the tables?

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

 

[dm21]

Hi Dave,

Yes, unfortunately I am using Sybase 12.1 version.

Sales Team prompt is also a cascading prompt where it filter by region and city.

If I add Sales Team Prompt in my last report as filter as you mentioned then I will get duplicate prompts for Region , City and Sales Team which is also not good.

Regards.

 

[griffindm]

dm21,

Sorry, I had the order of the picklists wrong. I thought Sales Team was the last one before the selection of employees.

Dave G.


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

http://www.decisionsupportgroup.com

 

[dm21]

Dave,

It is the last one before the selection of employee

The flow is as below

To choose the right employee you would be prompted for
Region --> City --> Sales Team --> Employee

Where Sales Team is filtered by region and city.

 

[griffindm]

Clarification: Is the Sales Team value unique or does it cross multiple Regions and Cities?

DAve G.


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

http://www.decisionsupportgroup.com

 

[dm21]

It is unique to Regions and Cities

 

[griffindm]

dm21,

Based on your comment:
>If I add Sales Team Prompt in my last report as filter as
>you mentioned then I will get duplicate prompts for
>Region , City and Sales Team which is also not good.

and the information on uniqueness, I don't see how this is happening.

If you select 'New England' as the Region from the first prompt, you should then see only the Cities for that Region. Selecting 'Boston' to the City prompt should take you to a single prompt for Sales Teams, showing only those in that City. The IN clause in the employee filter of the final picklist report should allow you to select multiple Sales Teams and the offered list of employees should be those in either of the Sales Teams selected.

If the prompts are appearing more than once, I may have misunderstood what the prompt of the final employee picklist report is. Is the first picklist for Region a report or catalog picklist? I believe the cascading reports and filters should be:

Rpt Filter
---------- ---------
Region None
City Region = ?GetRegion?
SalesTeam City = ?GetCity?
Employee Salesteam IN (?GetSalesteam?)
Final Rpt Employee IN (?GetEmpl?)

Generally with picklist prompts, multiple occurances of prompts means that the cascading logic is getting mixed up.

Let me know what you think.

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

 

[dm21]

Dave ,

The logic of prompts you got is right.

But my promblem is for final reportm if I have to choose 'ALL', employees then how would I know which sales team they belong to.

For eg.

Region - Canada
City - Toronto, Vancouver
Sales Team - Toronto Team, Vancouver Team
Employee - 'ALL'

At this time how can I pass these two sales team to my final report. Because just passing 'ALL' to final report is not enough.

Thanks.

 

[griffindm]

dm21,

You are not passing "All" to the final report. You are passing a list of employee id's to the final report. In the case of the "All" selection, you should be passing the complete list of all employee id's for those assigned to the Toronto and Vancover sales teams.

Dave G.


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

http://www.decisionsupportgroup.com

 

[dm21]

Dave,

You mean to say I will have to ask users to select all the users from the Employee Prompt?

 

[griffindm]

dm21,

After following all the way through the logic of your report chain, I don't believe you can get the ALL option to behave the way you require. Adding the ALL row via a Union Join OR adding the row to the table OR offering another prompt in the final report to override the employee selection would in each case return ALL of the employees in the table, and not a list of just all of the employees in the sales teams previously selected.

Don't see a solution to this one.

Sorry,

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

 

[dm21]

Dave,

Thank you very much for your help. I really appreciate it. I am going to talk to user and see if they don't mind selecting all the Employees.

Regards.

 

[dm21]

Dave,

Just one drawback on the method we talked about. In the simple list report I would not get to see prompt variable to add those in my Report Title.

Because Impromptu does not recognize the prompt which are not in last window prompt.

 

[griffindm]

dm21,

You are correct. That is a limitation of any usage of cascading picklists. The final report can only show prompt variable data from the last picklist report, unless other data is concatenated to the values as is moves through the picklists reports. This is often not feasible.

Dave G.


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

http://www.decisionsupportgroup.com