Hi Daviscollective and thanks for a useful reply
Just to respond to a couple of your suggestions up front:-
I'm wondering why this is a problem because you've already stumbled on the fix, swap the order of the parameters
The problem with that is that all the other parameters are naturally "paired". Ie a To and From Date, To and From Location, To and From Customer ID etc. The only one that isn't part of a pair is the the one created in step 4 (pUserID). I'd like it to appear last because SSRS renders it's parameters two abreast so by having pUserID last I allow all the other parameters to naturally line up in pairs.
Not to mention that a free text parameter is a really bad idea,
Normally I'd agree with you 100% on this but it is a requirement on this report. Some of the ranges they can enter are heeuge and, if expressed as dropdowns, would be completely unusable. The users on this will typically be taking the values they want to enter off paper work etc so will have the apropriate values in front of them and therefore textboxes work better. On a side rant: why oh why oh why don't microsoft implement proper predictive selections in the dropdowns where it matches against the entirety of what the users typed instead of just the last keystroke. If the dropdowns worked that way they'd be useful no matter how big the content.:rolleyes:
Anyway, now on to the meat of the issue:-
you've created a dependency between the @user-id and @pUserID
Yes although I think my naming of that parm in this example was clumsy and has caused some confusion. I called it pUserID not because it's returning a userID but to indicate that it's a parm that was based on a userID (or any other built in field for that matter). The steps I gave were meant to be generic (and if followed exactly will demonstrate the problem) so don't describe my real report which would have been way to complicated to describe here. In my real report that parameter actually returns the company numbers a user is allowed to access. The reason I said to make the sproc receive user ID as a parm is because the problem occurs only when the sproc recieves a parm fed by a built in field.
I'll try and describe it a bit more simply (but it's difficult because it's such a convoluted set of circumstances that create the issue so bear with me). The issue occurs if:-
You have a parameter (call it P1) based on a dataset
and
That dataset is built from a sproc which recieves a parameter fed from a built in field
and
You have at least one other parameter (call it P2) based on a dataset
and
That dataset is built from a query
and
You have at least one other parameter (call it P3) which isn't based on a dataset but instead allows free text
and
P1 appears after the P3 in the ordering
If all of those statement are true then P1 will be greyed out even though it doesn't actually have a true dependency on P3 (although your answer implies it does, I'll come to that in a bit)
If any of those isn't true then you won't see the problem. So, for example, if you have all freetext parameters then P1 isn't greyed out, even if it's after P3. Or if P1 isn't fed from a built in field then it's not greyed out.
But if the exact configuration I've described here is implemented then P3 gets greyed out even though all the information needed to poulate it is available to the engine up front. Worse, even if the user fills in every other parm, P3 never seems to get released.
I hope that describes the problem a bit more clearly but it's such a specific set of circumstances that I'm afraid I'm really struggling to keep it simple.
The @pFreeText parameter is the issue here. The report rendering cannot proceed beyond this step because it requires user input so when it comes before parameters that already have available or default values, it greys them out and forces you to enter them in order. The free text parameter is a report dependency even though it is not explicitly connected to other parameters or datasets.
This is interesting and I'm trying to think through the implications. Just to be clear pFreeText isn't used in any of the other parameters and can even be left blank when running the report (which basically equates to "ignore this selection" in the report logic). I don't understand why that would cause a subsequent parm which doesn't depend on pFreeText to be greyed out. And how would it work if the user chose to leave it blank but needed to complete the subsequent parameter? Also why do I then not get the problem if all the parms (other than P1) are free text? I'm sure there's something I haven't understod here so could you expand on this a bit because it feels like my answers under there somewhere.
Thanks again for your help so far but I'm not quite there yet so I'd really apreciate your further thoughts.