Solved

Report Builder - Conditional Expression Challenge

Posted on 2014-02-11
12
866 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
[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
  • 8
  • 4
12 Comments
 
LVL 3

Expert Comment

by:oromm
ID: 39851578
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
ID: 39851601
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
ID: 39851627
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

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

Author Comment

by:gberkeley
ID: 39851643
Dang, doesn't want parenthesis either.
0
 
LVL 3

Expert Comment

by:oromm
ID: 39851652
Are you certain that the field name is "START_DATE", and that it does in fact have a value in the resultset?
0
 

Author Comment

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

Author Comment

by:gberkeley
ID: 39851667
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
ID: 39851677
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
ID: 39851684
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
ID: 39851692
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
ID: 39851695
YES - I wish I could give you ANOTHER 500 points for responsiveness - thanks so very much!!!
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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

627 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