Solved

Report Builder - Conditional Expression Challenge

Posted on 2014-02-11
12
825 Views
Last Modified: 2014-02-11
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
Comment
Question by:gberkeley
  • 8
  • 4
12 Comments
 
LVL 3

Expert Comment

by:oromm
Comment Utility
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
 

Author Comment

by:gberkeley
Comment Utility
OH SO CLOSE!!!!!

See screen shot - it's being 'literal'.
How do I get my date?
Updated-shot.jpg
0
 
LVL 3

Expert Comment

by:oromm
Comment Utility
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
 

Author Comment

by:gberkeley
Comment Utility
Now it's giving me blank cells instead of dates. Darn, you, Report Builder!!! Any thoughts?
0
 

Author Comment

by:gberkeley
Comment Utility
Dang, doesn't want parenthesis either.
0
 
LVL 3

Expert Comment

by:oromm
Comment Utility
Are you certain that the field name is "START_DATE", and that it does in fact have a value in the resultset?
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:gberkeley
Comment Utility
yes, perhaps I need table name of ACTION in there somewhere?
0
 

Author Comment

by:gberkeley
Comment Utility
Sorry - table is ACTIVITIES
SELECT
 ACTIVITIES.[ACTION]
  ,ACTIVITIES.START_DATE
  ,ACTIVITIES.END_DATE
  ,ACTIVITIES.CARE_PROGRAM_NAME
  ,ACTIVITIES.STATUS
FROM
  ACTIVITIES
0
 
LVL 3

Accepted Solution

by:
oromm earned 500 total points
Comment Utility
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
 

Author Comment

by:gberkeley
Comment Utility
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
 

Author Comment

by:gberkeley
Comment Utility
YES!!!! You have it - it's working.  I updated the second part to "" and that did the trick.
THANK YOU SO VERY MUCH!!!!!!
0
 

Author Closing Comment

by:gberkeley
Comment Utility
YES - I wish I could give you ANOTHER 500 points for responsiveness - thanks so very much!!!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

743 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

16 Experts available now in Live!

Get 1:1 Help Now