ssrs Fields cannot be used in report parameter expressions

SSRS using a data set to set the default value of a parameter.

A value expression used for the report parameter “P_PO” refers to a field.  Fields cannot be used in report parameter expressions

I have three data sets that are used for a report selection parameters.
         1.      Project
         2.      PO
         3.      LineItem

In the query for the list of values for the PO the project parameter is used. In the query for the list of values for the LineItem both the project and PO parameter values are used.

I want to use the first value returned for the dataset as the default value for the parameter selection. I have tried using the first() operator but I get an error  

A value expression used for the report parameter “P_PO” refers to a field.  Fields cannot be used in report parameter expressions.

Is there a work around for this problem of using a dynamic default value from a dataset. I understand that "poorly written" SSRS can not figure out the order to execute the SQL

SSRS paramter screen shots
Version
fpkeeganAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

prequel_serverCommented:
how about making another dataset for default where it's Select TOP 1...
then you use Get values from dataset?
0
fpkeeganAuthor Commented:
The new data set will us the same  parameters as input. Same result.  Test do not guess !
0
ValentinoVBI ConsultantCommented:
prequel_server's statement actually makes sense, tested or not.  The input parameters are not what you're having an issue with, the First() expression for your default is your problem.  So if you create a dataset identical to the one that contains the D_VALUE field from your First() expression but with a "TOP 1" added to the query, you can use this dataset for the default selection of your parameter.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

fpkeeganAuthor Commented:
Create a new dataset that returns only one row with one value.
(oracle database)

oracle single value
Specify the default is to come from a query dataset.

default parameter
0
fpkeeganAuthor Commented:
need to provided a more detailed answer, tested, with screen shots.
0
prequel_serverCommented:
thanks ValentinoV.

@ geegan:

"need to provided a more detailed answer, tested, with screen shots."

umm no we don't.  I don't think you understand how EE works. We don't have to actually solve your problem for you by providing step by step instructions/screenshots.  You don't learn that way.

1. you describe problem
2. experts give possible solutions/resources to help you solve it.
3. you test it in your own environment with your own situation and let us know if it worked or if you got stuck somewhere
4. go to 2.

"Test do not guess !"

-we guess and you test.
0
ValentinoVBI ConsultantCommented:
@prequel: I partially agree with you on the step by step instructions comment, I say partially because some askers do actually need them to get things to work.  In this particular case I don't think it was needed.

But I don't agree with "we guess and you test".  First, guessing at a solution is not a good idea, other experts don't appreciate that kind of behavior.  But in your case I'm sure you don't really "guess", as far as I've seen your comments they make sense and are based on experience.  And that's a good thing, don't misunderstand me here :)

Now as for testing: in my case I do try to test everything, if possible.  It takes more time but that way we avoid posting silly errors such as typos which may not be noticed by the asker, sometimes because they don't gave enough base knowledge to understand what they're actually doing. Those are the cases that need detailed instructions. :)

Having said that: have a nice weekend all!

VV
0
prequel_serverCommented:
haha thanks VV. ya I agree with you but asker needs to be a little more polite when requesting more info. Just didn't like how he made that statement as if we're working for him. Then he posted the answer with screenshots answering his own question telling us how to answer it so I kinda of let him have it.

anyway thanks for defending me and I'm honored to get advice from an Ace like yourself.

have a good weekend
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.