How to create a Reporting Dashboard with Aging Buckets

[Lynn_Slaga]

I am working on creating a Reporting Dashboard based on a custom SQL for items in an active unity workflow queue.  One of the fields I return in my grid rows is the transdate of when the document entered the queue.  I am being asked to build a dashboard item that would show aging based on transdate ( less than 30 day aging bucket, 60 day aging bucket, older than 60 day aging bucket).  Has anyone done something similar?

[Bill_Schoby · Accepted solution]

You can use a CASE statement combined with a DATEDIFF function to determine age and put into a "bucket".  Here is an example where I'm using the revision date of WorkView objects.  I've made the important part bold.  You will need to replace "revisiondate' with the date field you want to use .  I selected revisiondate and ", datediff(day, revisiondate, getdate()) as Age" so you could see the result and how it fits into the values used in the CASE statement.

select top 100

revisiondate

, datediff(day, revisiondate, getdate()) as Age

, case

            when datediff(day, revisiondate, getdate()) > 90 then '90+ days'

            when datediff(day, revisiondate, getdate()) > 60 then '60 to 90 days'

            when datediff(day, revisiondate, getdate()) > 30 then '30 to 60 days'

            when datediff(day, revisiondate, getdate()) <= 30 then '30 or less days'

end as TimeBucket

from hsi.rmobjectinstance1018

order by revisiondate desc

You could also do an aggregation like this:

select
case
     when datediff(day, revisiondate, getdate()) > 90 then '90+ days'
     when datediff(day, revisiondate, getdate()) > 60 then '60 to 90 days'
     when datediff(day, revisiondate, getdate()) > 30 then '30 to 60 days'
     when datediff(day, revisiondate, getdate()) <= 30 then '30 or less days'
end as TimeBucket
, count(1) as ItemCount

from hsi.rmobjectinstance1018
group by

case
     when datediff(day, revisiondate, getdate()) > 90 then '90+ days'
     when datediff(day, revisiondate, getdate()) > 60 then '60 to 90 days'
     when datediff(day, revisiondate, getdate()) > 30 then '30 to 60 days'
     when datediff(day, revisiondate, getdate()) <= 30 then '30 or less days'
end

[Larissa_Armand]

Alternatively, if you didn't want to include the case statement in the SQL for whatever reason, you can add a calculated field to the dashboard that uses the same logic and date calculations.

[Lynn_Slaga]

Thanks Bill!