Solved

Report Builder - Conditional Expression Challenge

Posted on 2014-02-11
12
853 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

734 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