SSRS expression to fill back ground color based on perticuler date

Question

I have this query which will return a date from SQL Server

SELECT TOP 1 
    CASE
       WHEN (SELECT TOP 1 CONVERT(DATE, [Duedate]) AS [Duedate] 
             FROM [dbo].[Table1] 
             WHERE CONVERT(DATE, [Duedate]) = CONVERT(DATE, GETDATE())) = CONVERT(DATE, GETDATE()) 
          THEN CONVERT(DATE, GETDATE())
          ELSE (SELECT TOP 1 CONVERT(DATE, [Duedate])
                FROM [dbo].[Table1] 
                WHERE CONVERT(DATE, [Duedate]) > CONVERT(DATE, GETDATE())
                ORDER BY CONVERT(DATE, [Duedate]) ASC)
    END AS [ReturnRequiredDate]
FROM
    [dbo].[Table1]

However, when I'm trying to add this to my dataset in a SSRS report, it's returning Date and Time format. How to resolve this?

Secondly, I would like to fill background color of table columns/rows based on this returned date (i.e. previousdays & including returned date).

That is, based on case,if the date returning 16th of any month then the filled color should be, starting from 16th to previous days.

Also, lets say, if the date returned is the 27th of any month then color should be filled starting from 27th back to previous records (previous date records) How can I do this using SSRS expression?

Using query, out of all the dates, I'm able to return all dates previous to [ReturnRequiredDate]. Is there any ways to fill color to those returned dates only?In the same SSRS table (where all my records are there). I don't want a separate table in the SSRS for color fill.

Hope I have explained the rule clearly, if not please let me know to modify the question. Thanks.

Could you add sample data returned from your query, It would be helpful to provide solution. — AnkUser, May 17 '19 at 09:46

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

First question

What is the issue with the date being returned as datetime? If you need to do calculcations against it, 2019-05-17 is functionally equivalent to 2019-05-17 00:00:00.0000000. If you need to display it as just a date, use formatting in your report, either in the textbox properties or in your expression, for example: =format(Fields!ReturnRequiredDate.Value,"yyyy-MM-dd")

Second question

Again you just need to apply a data driven expression to your textbox background colour property containing the data that checks if the value is less than the parameter date:

=iif(Fields!YourDate.Value <= first(Fields!ReturnRequiredDate.Value,"YourDataset"),"Red","Black")

SSRS expression to fill back ground color based on perticuler date

1 Answers1

First question

Second question