Cascading parameter dropdowns

Question

Post reply

Cascading parameter dropdowns

sqltung

Hall of Fame

Points: 3962
More actions
December 2, 2008 at 9:04 am

#198424

For a recent reports project, I was asked to create a number of sales reports. Each report has about 8 parameters in dropdown lists (combo boxes). Previously, each dropdown was populated by a stored procedure which returned all possible values for the dropdown.
My manager says the salesmen don't like it when they get no results after selecting their parameters, so as a result, he wants me to have each dropdown be dependent upon the dropdowns before it, so that only the values that are in the data will be in the dropdown.
In the end, what I've got now is a very painfully slow set of dropdowns, particularly the last two, because I've got to run a fairly complicated stored procedure which involves dynamic SQL to get the dropdown data.
Is this a common way to do this?
I'm interested in any comments or suggestions.

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

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

cavo789 Mr or Mrs. 500 Points: 503 More actions · Answer 1

Image two parameters : Country and the City.

If the user select "Belgium" in the Country Dropdown, then the SQL for City should be

SELECT City FROM tblCities WHERE Country=@Country ORDER BY City

i.e. just use your parameter when defining your dataset.

Christophe

sqltung Hall of Fame Points: 3962 More actions · Answer 2

cavo789 (12/3/2008)
Image two parameters : Country and the City.
If the user select "Belgium" in the Country Dropdown, then the SQL for City should be
SELECT City FROM tblCities WHERE Country=@Country ORDER BY City
i.e. just use your parameter when defining your dataset.

Thanks, but I'm not having trouble with the SQL, just wondering how common this is.. and I'll give you more info:

A territory manager logs in and is given a dropdown with only his territories. The next dropdown is to select which date is used in the report: order date, invoice date, ship date, etc.

There is another dropdown for the customer address type: ship-to or bill-to.

Then there is a dropdown for year, one for starting month, and one for ending month (which depends on starting month).

There is also a dropdown for customer grouping type 1, and one for customer grouping type 2, and each is not related or dependent upon the other.

However, depending upon the selections made previous to the customer groupings, the customer groupings dropdowns should only show values which would be represented in the data, using the previously selected dropdowns. So basically I have to run a superset of the report results to get the last two dropdown values for the customer groupings.

The reports are done and are working fine, but of course, those dropdowns are slow slow slow!

My manager insists this is the common way to do this, and I disagree. I'm just looking for opinions..

laubenth Mr or Mrs. 500 Points: 588 More actions · Answer 3

Do you know which part is slow? Are the first 6 fast and just the last 2 are slow? How fast are the queries? Can you capture them in profiler? From my experience the query has been the slow part. Are there hundreds of items in the dropdowns?

I think stored procedures taking parameters for each dropdown is the way to go having the first drive the next and so on.

I am not sure how common your situation is but it makes sense for what he is asking for. Why give them the option to select something that we can figure out isn't going to return any results.

Why do you need to create dynamic SQL do you reference different tables depending on the parameters/selections?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Heh... why does everyone blame dynamic SQL... that's not what makes these drop downs painfully slow. Poor intial design, lack of proper indexes, and non-sargable SQL is what makes things slow.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqltung Hall of Fame Points: 3962 More actions · Answer 5

laubenth (12/6/2008)
Do you know which part is slow? Are the first 6 fast and just the last 2 are slow? How fast are the queries? Can you capture them in profiler? From my experience the query has been the slow part. Are there hundreds of items in the dropdowns?
I think stored procedures taking parameters for each dropdown is the way to go having the first drive the next and so on.
I am not sure how common your situation is but it makes sense for what he is asking for. Why give them the option to select something that we can figure out isn't going to return any results.
Why do you need to create dynamic SQL do you reference different tables depending on the parameters/selections?

Just the last 2 dropdowns are slow, because, while the complete set of them (maybe 12 options each) could be in the data, some will probably not be, and you really have to run the report without regard to the customer groupings to see which ones will be relevant.

I don't have the code in front of me today, but as far as dynamic sql is concerned, I don't use it unless it makes sense, and sometimes it really makes sense.

The reason the dropdowns are slow is I basically have to run a superset of the report to get each dropdown, and if you change any other dropdown values, the last 2 dropdowns must be re-run.

I will take a look at what kind of speed up I can get from adding an index or two where I can, but in the meantime, my manager has decided that the last 2 dropdowns should just show all 12 or so values and not to have them depend upon the previous dropdowns, so now it runs fast and smooth.