Compare dates & Using Indicators

Question

Compare dates & Using Indicators

davdam8

Hall of Fame

Points: 3620
More actions
October 1, 2012 at 4:09 am

#268330

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

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

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

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

davdam8 Hall of Fame Points: 3620 More actions · Answer 2

Thanks Genius! Works Fine.

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

DayDifference>=15 and <30.

Thanks

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

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.

davdam8 Hall of Fame Points: 3620 More actions · Answer 4

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!

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

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

davdam8 Hall of Fame Points: 3620 More actions · Answer 6

OK. Gonna try this one.

Goes directly into the indicator expression?

Thanks

Best regards

Ant-Green SSC Guru Points: 113507 More actions · Answer 7

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

davdam8 Hall of Fame Points: 3620 More actions · Answer 8

davdam8

Hall of Fame

Points: 3620

October 2, 2012 at 8:42 am

#1544417

How's that?

Ant-Green SSC Guru Points: 113507 More actions · Answer 9

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.

davdam8 Hall of Fame Points: 3620 More actions · Answer 10

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?

Ant-Green SSC Guru Points: 113507 More actions · Answer 11

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

davdam8 Hall of Fame Points: 3620 More actions · Answer 12

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?

Ant-Green SSC Guru Points: 113507 More actions · Answer 13

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

davdam8 Hall of Fame Points: 3620 More actions · Answer 14

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!