Solved

ssrs  Fields cannot be used in report parameter expressions

Posted on 2014-10-15
8
4,413 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

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…
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

732 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