SSRS Conditional value

Off Topic
1

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?

2

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.

3

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)

4

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.

image
image.png768Ă—224 26.2 KB

5

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

image
SQL:

image
image.png850Ă—116 11.2 KB

6

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

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

7

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

8

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

9

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?)

10

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:

image
image.png559Ă—630 37.3 KB

11

Try this instead:

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

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")
13

Is this not in a Tablix?

14

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

15

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.

1 Like