July 2, 2012 at 3:59 am
Hi All
I'm getting a very odd problem with a parameter on a report and just want to know if anyone else has experienced it and, if so, were they able to resolve it.
I've got a parm (I'll refer to it as ParmA from now on) that get's it's available values from a dataset which is based on a stored procedure. I've then got some parms which get their avaiable values from datasets based on queries. Finally I've got some parms which don't have available values specified. ParmA works fine as long as it isn't preceded by a parm which doesn't have available values. But as soon as a parm with no available values is put before ParmA, ParmA gets greyed out and is unselectable. It appears as if it's a cascading parameter and has a dependeny on a previous parm, even though it actually doesn't.
After a bit more investigation I believe this only happens if the stored procedure recieves a Built-In Field as a parameter.
I apreciate the blurb above is probably hard to follow so here are some steps to replicate:-
1. Create a stored procedure containing any arbitrary select. Make it receive e.g. UserID as a parameter. It doesn't actually need to use the parameter.
2. Create a bank report
3. Create a dataset based on the stored procedure. On the dataset's parameters tab add a parameter called @user-id and set it's value to the UserID built in field.
4. Create a report parameter which selects it's available values from the dataset created in step 3. Call this parm pUserID
5. Create a dataset based on an arbitrary query
6. Create a report parameter which selects it's available values from the dataset created in step 5. Call this parm pQuery
7. Create a parameter with no specified available values. Call this parm pFreeText.
8. Run the report. Note that pUserID is enabled and you can select a value in it.
9. Switch back to the designer.
10. Move pUserID down the list once so it appear after pQuery and before pFreeText.
11. Run the report. Note that pUserID is still available.
12. Return to the designer and move pUserID down one more time so it's now at the end of the list under pQuery and pFreeText.
13. Run the report again. pUserID is now greyed out and cannot be selected. There's no obvious reason why. You can shuffle the parms into any order, pUserID will always be greyed out if it appears after pFreeText. Whether it appears before or after pQuery doesn't seem to have any effect.
14. Return to the designer and open the properties of the dataset created in step 3. Go to the parameters tab and ammend the@UserID to recieve a simple value, e.g. "Test".
Run the report again and pUserID is now enabled regardless of where it appears in the parameter ordering.
Can anyone explain what's happening here? It looks like a bug in SSRS to me but when I googled for it I couldn't see anyone else experiencing it (although it's pretty tricky to express this problem in a google search so it's entirely possible I've just missed it).
July 3, 2012 at 7:24 pm
The ordering of parameters in the Report Data window in Visual Studio is also the cascade order when you've set up dependencies, but you seem to be well aware of that.
Just to make sure I've followed you here, you've created a dependency between the @user-id and @pUserID. @user-id is driving a stored proc created in step 1 - it *requires* @user-id, even though it "doesn't actually have to use the parameter". That stored proc then provides values to @pUserID. It doesn't really matter if it's a stored proc or an embedded query, they both just create a dataset with parameters.
Assuming @pQuery has no default, it is intially blank but already has a drop down list of available values populated by the independent query. The report renderer will proceed with that step as long as it's not waiting on anything prior to that.
So the order initally is this :
@user-id (defaulted to User!UserID)
@pUserID (Depends on @user-id)
@pQuery (Independent)
@pFreeText (Free text entry)
You then swap to this order which still works (@pQuery does not block @pUserID because the available values for @pQuery step is free to proceed):
@pQuery
@pUserID
@pFreeText
You then swap to this order which now has @pUserID greyed out (@pFreeText is blocking @pUserID because @pFreeText is not free to proceed until it receives user input).
@pQuery
@pFreeText
@pUserID (not greyed out if hardcoded with a value)
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.
I'm wondering why this is a problem because you've already stumbled on the fix, swap the order of the parameters.
Not to mention that a free text parameter is a really bad idea, don't give the users an opportunity to type in wrong values when you could easily provide them a list or better yet default values thereby reducing the number of choices they have to make. Reports with too many choices are unwieldy and mostly get used for a narrow set of choices anyway.
As a side note, in step 4, you are creating a report parameter which selects its available values from... Wouldn't you rather have it choose its default value from the dataset? As you know, UserID is a global that holds the login name of the currently logged on windows user. I use this all the time to go fetch the employee's ID number from HR/payroll systems which is then useful for providing customized reports per employee.
July 4, 2012 at 2:23 am
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.
July 4, 2012 at 6:01 pm
Hi
If the main concern is lining up the parameters, I've seen somebody deal with this by having a fake parameter and then making the default value "DO NOT USE"
It kinda looks a bit funky but satiates the OCD.
Another couple of options to think about are making a parm Internal or Hidden, that will remove it from the dependency chain.
Allow NULL is the other option that might help you, if you don't have that ticked then yes your parm must have a value entered.
Which version of SSRS and BIDS are you using? I have 2005/2008/2008R2 servers but I use the BIDS from the 2008R2 disk to deploy to all of those.
July 5, 2012 at 2:12 am
I'm using 2008 R2 and using Visual Studio 2008 as in IDE.
One thing that occurs to me, is it possible to upload rdl's as attachments? I don't see it in the allowed extension types. I can't send out the actual report (politically sensitive and all that) but I could trivially knock up a dummy example that exhibits the problem.
On the allowing nulls etc. I've set them up apropriately for the parms involved. The dropdowns all allow nulls and the freetexts all allow blank.
The fake parms a last ditch option which I might consider but it's awfully fudgy.
July 5, 2012 at 2:39 am
Right, Here you go. Here's a dummy report that exhibits the problem. (Why didn't I just do this in the first place:doze:)
I've had to change the file extension from rdl to txt to get it to upload but it's just xml under the lid so that shouldn't make any difference. Just change it back to rdl after you download it.
P1 refers to a sproc called DecsTestProc. The code I used for that is here:-
CREATE PROCEDURE [DecsTestProc]
@VarIN as varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT CompanyNumber, CompanyDescription
From Companies
END
The select there probably won't work against your own DB but any select that returns two columns called CompanyNumber and CompanyDescription should do the trick. CompanyNumber should be an int and CompanyDescription should be a VarChar(50) - although I don't think the datatypes will actually make a difference, you'll get the problem with other types.
Equally the query used by P2 can be any query that returns CompanyNumber and CompanyDescription.
Try setting it up in an ide and then shuffling the parameters around and you should see what I mean. As long as P1 appears after P3 it gets disabled, but there's no obvious reason why.
July 5, 2012 at 6:25 pm
Ah good I was going to suggest attaching it as a text file. Just have to make sure not to break any rules or protocols on the forum.
I've just used this as a query in the sproc for dsP1 and the query in dsP2
Select Null CompanyNumber, '' CompanyDescription
union all
SELECT 1 CompanyNumber, 'comp1' CompanyDescription
union all
SELECT 2 CompanyNumber, 'comp2' CompanyDescription
union all
SELECT 3 CompanyNumber, 'comp3' CompanyDescription
union all
SELECT 4 CompanyNumber, 'comp4' CompanyDescription
As long as P1 appears after P3 it gets disabled, but there's no obvious reason why.
You're right. It's wacky land.
What I can see without shuffling anything is that yes, P1 is initially greyed out. If I type something random into p3 nothing happens, until I press enter. Then P1 is available. That still doesn't explain why the stored proc doesn't immediately supply the available values to P1 as a drop down box. Weirdly if the order is P3, P2, P1 then it doesn't happen.
Clearly there's a difference between the stored proc and the direct query. There's some interaction between the freetext parm and the stored proc parm that is not happening with the freetext parm and the direct query parm.
BUT
I have another trick for you. Add a default value for p3. Delete the (null) that it wants to put in there. Effectively you are setting the default for P3 to be an empty string. This then tricks the report into thinking it has a value and can proceed with running the stored proc and populating the available values for the next parm, P1.
To the end user, however, it looks like the parameter is waiting for entry. The only caveat here is to make sure that all the parms don't have defaults otherwise the report will go ahead and run itself, unless that's what you want. I often do want that. I try and make reports that run without any parameter entry by using current time period defaults, and using the UserID to look up the users Office location and showing them a report relevant to them. The users can then change params to look at historical (hysterical) data if they choose but 90% of the time they want to look at what's current.
July 6, 2012 at 2:13 am
Effectively you are setting the default for P3 to be an empty string.
Now that's some good lateral thinking and will probably resolve my issue 😀
the report will go ahead and run itself
Gah, I hate that behaviour. If it was a property I could turn on and off it'd be a wonderful feature but without me being able to control it it's just a pain. On the whole BIDS is a very good package, particularly given that it's free, but there are one or two design decisions which seem... odd.
Oddly enough I've just experienced a very similar problem on another report. It had a bunch of parameters and I made one of them obligatory (ie no nulls). All the subsequent parms that were based on queries immediately became disabled and would only enable when the first parm was selected and I noticed the parms were refreshing at that point even though their query didn't contain a dependancy. I've got a working theory now. I think sometimes the dependancy chain just get's too complex for SSRS to keep track of and, at that point, it defaults to a state of "better safe than sorry". It disables everything it's not sure about and offers them up to the user one at a time. Of course, I'll never be able to prove that but it'll do for me.
Thanks again for your help on this.
September 18, 2013 at 11:39 am
Not sure it helps, but I had some reports that seemed to randomly disable parameters with no logical pattern. Turns out that there was a setting in the Advanced Parameter options that had been changed from "Automatically determine when to refresh" to "Always refresh". The parameters with the default "Automatically determine when to refresh" were always enabled. The few that had been switched to "Always refresh" were disabled when viewing from my application. This was a different behavior than when I Previewed in BIDS. Anyway, just changing that back to "Automatically determine when to refresh" resolved the issue.
Hope this helps.
September 19, 2013 at 9:21 am
I've actually moved on from the contract where I developed that report so it's too late for me to fix now. It wasn't a show stopper so the customer just decided to live with it in the end.
I think I know the setting you're referring to though. I can't remember if I tried it out for this but it does sound like a likely fix. I'll stow that one in the memory banks because I'm sure I'll face this issue again sooner or later.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply