Compare dates & Using Indicators

  • Hi geniuses!

    I need to compare dates (proj.last.publication (field) and current date) of various projects and depending of the result, expose an indicator that informs if the project is updated or not.

    4 Traffic Lights Indicator

    Green: if proj.last.publication < 2 weeks (15 days) of current date;

    Yellow: if proj.last.publication >= 2 weeks (15 days) and < 1 month of current date;

    Red: if proj.last.saved > 1 month;

    white: if proj.stage = to 'autho.waiting' or 'approved' or 'planned' or 'waitingforapproval'

    Thanks in advance.

    Best Regards

  • Looks like you need DATEDIFF functions, one tip do this in the SQL data set not the tablix and then base your expression on that columns

    Something like below then based your expression for the indicators off the value in the DayDifference column

    SELECT

    Col1,

    Col2,

    DATEDIFF(DAY, proj.last.publication, GETDATE()) AS DayDifference,

    Col3,

    Col4

    FROM

    dbo.Table1

    If you want to do it in the Tablix as an expression, the same applies but you want to use Today() instead of GETDATE()

  • Thanks Genius! Works Fine.

    Just a question. In the expression, how do I write for the yellow indicator?

    DayDifference>=15 and <30.

    Thanks

  • Wrap it in a case statement and if the value is between 15 and 30 then flag it as a value to mark which colour indicator to use.

  • Thanks Anthony!

    I've never used a CASE statement in SSRS before, I'm using instead an IIF statement.

    Anyway can you write me the CASE statement for this particular example?

    Thanks man, you've helped me alot so far!

    Thanks

    Regards!

  • SELECT

    Col1,

    Col2,

    CASE DATEDIFF(DAY, proj.last.publication, GETDATE()) WHEN < 15 THEN 'Green' WHEN BETWEEN 15 AND 29 THEN 'Yellow' WHEN >= 30 THEN 'Red' END AS ColourToUse

    Col3,

    Col4

    FROM

    dbo.Table1

  • OK. Gonna try this one.

    Goes directly into the indicator expression?

    Thanks

    Best regards

  • No thats in the dataset, then base your expression off the colourtouse column

  • How's that?

  • Hows what?

    The CASE statement works out which colour to assign to the value based on the difference in dates, then you need to write your expression as we have done before using SWITCH of IIF to say if Field!ColourToUse.Value = "Green", "Green" etc etc.

  • DONE. Thanks!

    In this same example I got another condition I have to apply, but the info required is in another Database.

    My example:

    If project = "planned" (from DB abc) switch indicator to white.

    Do you know a way to do this?

  • unless you have found a way to link datasets together or have used cross database joining in the data set no.

  • It might sound like a crazy ideia but here it goes:

    I could use a Lookup function to return the info I need into a column, and based on that info switch the indicator.

    Does this is doable?

  • I dont know, never used lookup in SSRS, try it out and see.

  • Lookup Function works fine.

    I mean it retrieves results from another DB, wich is what I want, not exactly the result I expect but it seems to work allright.

    This is basically how it works:

    Lookup(source_expression, destination_expression, result_expression, dataset)

    This link have helped me alot:

    http://prologika.com/CS/blogs/blog/archive/2009/08/12/reporting-services-lookup-functions.aspx

    Regards!

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply