Report Builder Conditional IF Function Problem

Hi everyone,

I have created a view from the exisiting table to use in the report builder model and in the table whenever there is a null(when users does not enter a date for this particular field in the application) for datetime field either of these 2 ("1/1/1753" and "12/31/9999") dates are stored . Since I am creating my views based on these tables these dates will be in my views as well. Checking for this at the application level and cleaning up is not a option for me at this time.

So what I am trying to do is to check for these values and replace it with Null or blank in the model designer expression property. So that when user creates a report using this field they will not see these sql standard dates.

I tried using conditional IF in Model designer .

EX:

IF(Next MCR Review Date = 1/1/1753,EMPTY,Next MCR Review Date)

IF(Next MCR Review Date = 1/1/1753,NULL,Next MCR Review Date)

Both of these gives me errors.

Can anyone tell me what I am doing wrong and also are there any other ways to get to what I want.

I have already spent a lot of time digging for documentation but no luck any help is appreciated.

Thanks a Lot

Ashwini

I struggled with this for a while, it's a good question... and the model builder stuff really is a PITA.

Here is what I suggest: include the appropriate information in your view as an extra field and use that field instead of your "real" date" in your model.

In the view you can use something like

Code Snippet

CASE WHEN [Next MCR Review Date] = '1/1/1753' THEN NULL

ELSE [Next MCR Review Date] END AS ModelReviewDate

.. you can leave the original value in as a separate column in the view for additional purposes, comparison, etc.

>L<

|||

Thank you so much Lisa this works like a charm. I had tried this before but silly me I had not put the quotes '1/1/1753' like this Instead I was just typing it this way

Code Snippet

[Next MCR Review Date] = CASE TME.next_assessment_dt
WHEN 1/1/1753 THEN ' '

WHEN 12/31/9999 THEN ' '

ELSE TME.next_assessment_dt
END

Your code Snippet rang the bell Thanks again so much!!! You have made my day.

Ashwini