Solved

ssrs  Fields cannot be used in report parameter expressions

Posted on 2014-10-15
8
3,700 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
 

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now