Solved

ssrs  Fields cannot be used in report parameter expressions

Posted on 2014-10-15
8
4,038 Views
Last Modified: 2014-10-17
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
0
Comment
Question by:fpkeegan
  • 3
  • 3
  • 2
8 Comments
 
LVL 3

Assisted Solution

by:prequel_server
prequel_server earned 250 total points
ID: 40383593
how about making another dataset for default where it's Select TOP 1...
then you use Get values from dataset?
0
 

Author Comment

by:fpkeegan
ID: 40384019
The new data set will us the same  parameters as input. Same result.  Test do not guess !
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 250 total points
ID: 40384088
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:fpkeegan
ID: 40385220
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
 

Author Closing Comment

by:fpkeegan
ID: 40385226
need to provided a more detailed answer, tested, with screen shots.
0
 
LVL 3

Expert Comment

by:prequel_server
ID: 40386884
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
 
LVL 37

Expert Comment

by:ValentinoV
ID: 40386993
@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
 
LVL 3

Expert Comment

by:prequel_server
ID: 40387078
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

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SSRS DateAdd function 4 70
SSRS Summary and detail Report folder structure. 1 74
how to calculate Quintiles in ssrs 3 45
Date Differences SSRS 3 66
Written by Valentino Vranken. Introduction: In a previous article (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Reporting-On-Data-From-Stored-Procedures-part-1.html) I announced that I would writ…
After much searching across the internet I have found that you could not set the name of the file you were attaching to dynamic report subscriptons in Microsoft Reporting Services. I did manage to find one article showing you how your could make a s…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question