Compare dates & Using Indicators

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services » Compare dates & Using Indicators

18 posts, Page 1 of 2

1 2 »»

Compare dates & Using Indicators

Rate Topic

Display Mode

Topic Options

Author

Message

davdam8

Posted Monday, October 01, 2012 4:09 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:26 AM
Points: 156, Visits: 567

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

Post #1366460

anthony.green

Posted Monday, October 01, 2012 7:59 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1366581

davdam8

Posted Monday, October 01, 2012 8:54 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:26 AM
Points: 156, Visits: 567

Thanks Genius! Works Fine.
Just a question. In the expression, how do I write for the yellow indicator?
DayDifference>=15 and <30.

Thanks

Post #1366606

anthony.green

Posted Tuesday, October 02, 2012 1:47 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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.

Post #1366886

davdam8

Posted Tuesday, October 02, 2012 6:59 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:26 AM
Points: 156, Visits: 567

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!

Post #1367003

anthony.green

Posted Tuesday, October 02, 2012 7:32 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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

Post #1367032

davdam8

Posted Tuesday, October 02, 2012 7:48 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:26 AM
Points: 156, Visits: 567

OK. Gonna try this one.
Goes directly into the indicator expression?
Thanks

Best regards

Post #1367041

anthony.green

Posted Tuesday, October 02, 2012 8:39 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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

Post #1367080

davdam8

Posted Tuesday, October 02, 2012 8:42 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:26 AM
Points: 156, Visits: 567

How's that?

Post #1367083

anthony.green

Posted Tuesday, October 02, 2012 8:47 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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.

Post #1367089

« Prev Topic | Next Topic »

18 posts, Page 1 of 2

1 2 »»

Permissions