How to Create Cascading Pick lists

Question

How to Create Cascading Pick lists

Vandy

SSCommitted

Points: 1827
More actions
August 21, 2006 at 6:10 am

#114090

Hi All
I am making one report by joining 3 tables. Date, Sales and Product Manager table
I want to show report in which first you can select product manager then major code related to product manager and then minor code for particular year.
So need 4 parameters. FOR YEAR 2006 SALES BY PRODUCT MANAGER
Product Manager
Major
Minor
JAN
FEB
MARCH
Alex
POB
GHJ
10000
7890
7653
John
HU9
JKL
8902
6789
7892
Ema
BN
7U
5678
8265
8902
THANKS

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Carolyn-358394 SSC Enthusiast Points: 179 More actions · Answer 1

Hi,

you'll need to set up a dataset in your report for each table where you get the values for Product Manager, Major Code and Minor Code from.

Then, when in Layout view, go to the Report menu and select Report Parameters. For each of your parameters, select the "From Query" option, then select the appropriate dataset, value field and label field.

It should then have cascading combo boxes for you when previewing the report.

Hope that helps, Carolyn

Neil Rankin SSC-Addicted Points: 452 More actions · Answer 2

Dataset for Major Codes assuming that each Manager has their own Codes

="Select MajorCode From MajorCodes where Manager='" & Parameters!ManagerParam.Value & "'"

Assumes that the Manager Parameter is called ManagerParam.

Is this what you are trying to do?

Vandy SSCommitted Points: 1827 More actions · Answer 3

Thanks a Lot Carolyn & Neil

I have made different dataset for all three parameters it's working fine but showing full list and i need same thing which Neil said.

i want that when you chose product Manager then my drop down list will show major groups only related with product manager. Same when you chose major code then drop down list shows only minor related to that major group. every product manager has four and five or some time more then that major group same with minor code.

I tried the code provided by Neil but have error . I am a new user so just want to double check i tried this on dataset of Majorcode.

PMitem table has ProductManager,Major,Minor,Item fields.

I made three Dataset, For Productmanager & Query for that is

Select ProductManger from PMitem

Same thing done in other two........

Please Correct me if i am wrong. So this all what i have done........

Thanks & regards

Mike Kendall SSC Eights! Points: 958 More actions · Answer 4

Vandy,

You need to add WHERE clauses to your 2nd and 3rd data sets. For example - if the parameter for the first pick is @ProductManager then the query for the second data set would have a WHERE ProductManager = @ProductManager.

Mike

Carolyn-358394 SSC Enthusiast Points: 179 More actions · Answer 5

in the query for the dataset, type something like:

SELECT * FROM PMItem

WHERE ProductManager = @ProductManager

Vandy SSCommitted Points: 1827 More actions · Answer 6

Thanks a lot

It's working .............

Kind Regards