Ssrs 2008 using nested iif expressions

SSAS and SSRS
1

In an ssrs 2008 report, there is the following existing iif expression:
=IIF(Fields!CorrespondenceLanguage.Value = "English", IIF(IsNothing(Fields!firstDay.Value), "Thursday, August 17, 2017", Fields!firstDay_WeekDayEnglish.Value + ", " + Fields!firstDay_MonthEnglish.Value + " " + Fields!firstDay_Day.Value + ", " + Fields!firstDay_Year.Value), "")

I want to add the following to precede the existing IsNothing logic:
IIF(INSTR("066", Fields!schoolType.Value) > 0 and IIF(IsNothing(Fields!firstDay.Value), "Wednesday, August 9, 2017",

I would like the IIf expression to look like the following:
=IIF(Fields!CorrespondenceLanguage.Value = "English",IIF(INSTR("066", Fields!schoolType.Value) > 0 and IIF(IsNothing(Fields!firstDay.Value), "Wednesday, August 9, 2017", IIF(IsNothing(Fields!firstDay.Value), "Thursday, August 17, 2017", Fields!firstDay_WeekDayEnglish.Value + ", " + Fields!firstDay_MonthEnglish.Value + " " + Fields!firstDay_Day.Value + ", " + Fields!firstDay_Year.Value), "")

This logic is basically not workiing. I basically want to check if the school number = 066 and (IsNothing(Fields!firstDay.Value), I want the date to be "Wednesday, August 9, 2017". For any other school when there is not a first day set, I want the default to be
Thursday, August 17, 2017.

Thus can you show me how to change the iif expression to accomplish my goal?

2

as you can see eventually stuffing expressions with a lot of data can become unmanageable, and can potentially cause strabismus. :older_man:
my recommendation would be why not do all this in the stored procedure that feeds this report using case statement

3

Ace!

4

The problem is this is for school letter that are being sent out in the fall. The report is used for schools in a large school district.
We are talking about creating a table to hold all the unique values. But for now, I have to get the report working. Thus can you tell me how to fix my problem?

5

I can't speak for others here, maybe they see it the same way that I do, if not they can speak up.

This sort of coding is very very fragile, and when my code gets anywhere near this point, having "evolved over time", I sit down and rewrite it to make it safe

All the IF this and ELSE that with hardwired bits & pieces are OK whilst they are few in number, and simple, but once they grow to a point of potential failure I replace them with a JOIN to a lookup table, or a set of structured rules. That takes time to do, is not cost-effective when its only a couple of simple items, but sure as eggs-are-eggs it is cost effective the moment the issue becomes complex.

Albeit that I don't understand this particular problem fully, mainly because I don't have any core-skills in SSRS, my gut feeling is that it needs sorting out with something much more robust than a hideously complex set of nested IIF statements. IIF Functions are a ghastly solution compared to something more structured. "More structured" brings with it better readability, much higher chance of spotting bugs, easier debugging, and so on. Going one step further and building Lookup Tables and/or Rules Tables means that the data stored in them is forced to be uniform (by column datatype, length restrictions, and quite possibly also by validation rules on any data added to the lookup tables) and is also a lot more robust than (IF MyColumn = 'XXX', 'YYY', 'ZZZ') where XXX, YYY and ZZZ are only as good as I typed them, hardwired, into my code.

At the very least these IIF statements could be recoded and encapsulated in a VIEW which would make it much easier to "test" that they were performing as-intended, and be reusable across any number of queries / reports - i.e. the logic would be centralised.

Using multiple CTEs or CROSS/OUTER APPLY statements would allow the code to be developed [in a VIEW] iteratively and, I fully expect, more reliably

6

What is the datasource for this SSRS report?

  1. Embedded TSQL
  2. stored procedure
  3. View?
7

embedded t-sql

8

What do you do if requirements change tomorrow? open SSRS and change and deploy?

9

You are correct. Change and deploy next year. The goal is to come up with a table eventually where these values do not need to be hard coded.

10

Modify the embedded SQL and use a CASE expression to generate the column. Much easier to manage and maintain than trying to embed IIF statements in an expression.

Better yet - convert that embedded SQL to a stored procedure and use a stored procedure to define the dataset. Using a stored procedure would allow you to modify the hard-coded dates without having to redeploy - and you could even dynamically create the dates based on when the procedure is executed - or you can derive the values based on other criteria.

1 Like