• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 892
  • Last Modified:

Report Builder - Conditional Expression Challenge

So - I am working on a Report Builder 3.0 report and have run into a snag trying to get a conditional expression on one of my report columns.   Essentially, I want the Start Date Column's row value to populate with the Start Date ONLY IF the Action Field meets a certain criteria, else, leave the Start Date Blank.  I'm not a SQL jock and have tried all the options in the help files for RB 3.0. Need some expertise. The error I keep getting is this:

The Value expression for the textrun ‘START_DATE.Paragraphs[0].TextRuns[0]’ contains an error: [BC30451] Name 'CCSP' is not declared.

The expression itself is written as:
=IIf(Fields!ACTION.Value = CCSP Referral, "Fields!START_DATE.Value"," ")
I'm not married to this expression; I just haven't mastered anything more complex.

I'm attaching a screen shot in hopes that will help to visualize what I want to do.  Assistance as always most appreciated!
Expression-Challenge.jpg
0
gberkeley
Asked:
gberkeley
  • 8
  • 4
1 Solution
 
orommCommented:
Have you tried with just this syntax, using double quotes around the comparison string rather than SQL string syntax?

=IIf(Fields!ACTION.Value = "CCSP Referral", "Fields!START_DATE.Value"," ")
0
 
gberkeleyAuthor Commented:
OH SO CLOSE!!!!!

See screen shot - it's being 'literal'.
How do I get my date?
Updated-shot.jpg
0
 
orommCommented:
Remove the double quotes from around the Fields!START_DATE.Value.  My bad for not seeing that initially.
=IIf(Fields!ACTION.Value = "CCSP Referral", Fields!START_DATE.Value," ")
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
gberkeleyAuthor Commented:
Now it's giving me blank cells instead of dates. Darn, you, Report Builder!!! Any thoughts?
0
 
gberkeleyAuthor Commented:
Dang, doesn't want parenthesis either.
0
 
orommCommented:
Are you certain that the field name is "START_DATE", and that it does in fact have a value in the resultset?
0
 
gberkeleyAuthor Commented:
yes, perhaps I need table name of ACTION in there somewhere?
0
 
gberkeleyAuthor Commented:
Sorry - table is ACTIVITIES
SELECT
 ACTIVITIES.[ACTION]
  ,ACTIVITIES.START_DATE
  ,ACTIVITIES.END_DATE
  ,ACTIVITIES.CARE_PROGRAM_NAME
  ,ACTIVITIES.STATUS
FROM
  ACTIVITIES
0
 
orommCommented:
At a loss now...
If your expression is simply
=Fields!START_DATE.Value
presumably the date shows on every row, correct?  
Not sure if it would make a difference to make the last parameter of your IIf to be a blank string ("" rather than " ").
0
 
gberkeleyAuthor Commented:
Sorry - that's my confusion. What I'm aiming for is this:

Check the ACTION field. If value of ACTION field = N'CCSP', then populate Start Date with the  Activities.start_date value, else, leave Start Date cell  blank.
0
 
gberkeleyAuthor Commented:
YES!!!! You have it - it's working.  I updated the second part to "" and that did the trick.
THANK YOU SO VERY MUCH!!!!!!
0
 
gberkeleyAuthor Commented:
YES - I wish I could give you ANOTHER 500 points for responsiveness - thanks so very much!!!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 8
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now