SSRS Conditional value

Apologies in advance if this is a really simple question. I think my syntax is the problem, but alas.

I am wanting to have a field in a BAQ report that in one case needs to always display a specific value from the dataset. In the case where a condition is met, I want to join the values from 2 different fields and place them into the SSRS report field.

I want to always display the value of QuoteDtl_Character03 in all cases.
In the case where QuoteHed_ShortChar04 has a value, I want to put that value in the front of the value and then add on the QuoteDtl_Character03 value as well with a character return built in.

=IIF(LEN(First(Fields!QuoteHed_ShortChar04.Value, "BAQReportResult"))>0,"Pt. Defect Info: "& First(Fields!QuoteHed_ShortChar04.Value, "BAQReportResult") & vbcrlf & First(Fields!QuoteDtl_Character03.Value, "BAQReportResult"), First(Fields!QuoteDtl_Character03.Value, "BAQReportResult"))

From what I know about IIF statements, it’s this=IIF(something, doifTrue, doifFalse)

So far, with the first condition met, it’s only spitting out the QuoteDtl_Character03 value and not the other one. Any tips?

I prefer to create two Placeholders for this. First is you text “Pt. Defect Info: “ would be using the iif like you said. Then next to that in the textbox is the Char03 value.

Try this …

=IIF(First(Fields!QuoteHed_ShortChar04.Value, "BAQReportResult") Is Nothing,
    "Pt. Defect Info: " 
      & First(Fields!QuoteHed_ShortChar04.Value, "BAQReportResult") 
      & vbcrlf 
      & First(Fields!QuoteDtl_Character03.Value, "BAQReportResult"), 
    First(Fields!QuoteDtl_Character03.Value, "BAQReportResult"))

I think Is Nothing wil check for Nulls too

edited (originally had an extra parenthesis in there)

I think i understand…but I have very limited room to work with in this report, so I was trying to throw it into the existing field where QuoteDtl_Character03 is being placed.

So that code works, but it doesn’t display the Value of QuoteHed_ShortChar04, which is super odd because it definitely exists

image
SQL:

WHOOPS!!! That shouldn’t work at all.

I was testing for Is Nothing, the two conditions need to be reversed.

Why are you using the First() aggregator?

from that data set, I’d expect:

Pt. Defect Info: 20 x 20
Please review ...

for every record

Because I have no idea what I’m doing, clearly!! :wink:

So basically the query goes after Quote rec + single Quote Line. So there’s really no need for an aggregation

If you want that once per quote (like if one quote had multiple lines), change the scope from BAQReportResult to QuoteLine

(or should it be QuoteNum?)

So when I go to expression editor, it looks like this is how the values from the BAQReportResult dataset are available which is probably why they look like that:

Try this instead:

=IIF(Fields!QuoteHed_ShortChar04.Value Is Nothing,
    "Pt. Defect Info: " 
      & Fields!QuoteHed_ShortChar04.Value
      & vbcrlf , "") &
    Fields!QuoteDtl_Character03.Value

It won’t let me save it without referencing an aggregation (!) but modifying the expression still yields the same results functionally

=IIF(First(Fields!QuoteHed_ShortChar04.Value, "BAQReportResult") Is Nothing,
    "Pt. Defect Info: " 
      & First(Fields!QuoteHed_ShortChar04.Value,"BAQReportResult")
      & vbcrlf , "") &
    First(Fields!QuoteDtl_Character03.Value,"BAQReportResult")

Is this not in a Tablix?

Not sure what that is. It’s a BAQ report

If you use a Tablix (like a table), it will have the context of the BAQReportResults. Then, you won’t have to select the “First” record each time, but it will display every record based on grouping.
https://docs.microsoft.com/en-us/sql/reporting-services/report-design/tables-matrices-and-lists-report-builder-and-ssrs?view=sql-server-2014

1 Like