Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Report Builder - Conditional Expression Challenge

Posted on 2014-02-11
12
Medium Priority
?
885 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
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

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 2000 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

Independent Software Vendors: 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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

824 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