We appreciate you taking the time to vote and add your suggestions to make our products awesome! Your request will be submitted to the community for review and inclusion into the backlog.

We recommend reviewing what is submitted before posting, in case your idea has already been submitted by another community member. If it has been submitted, vote for that existing feature request (by clicking the up arrow) to increase its opportunity of being added to Cireson solutions.

For more information around feature requests in the Cireson Community click here.

Dashboard - 'First of the Week' Relative Date Searches

The dashboards have been a great addition to the Cireson tool set. One issue that I keep running into is building a weekly report query without it being a continuous count. As it stands now there are three operators that allow first of the month,quarter & year but week is missing. Change to a "days ago" operator and now the results turn into a running number and really isn't very helpful to supervisors and managers who want to know running stats like created vs closed for the week in the Dashboard. Changing over to a sql query widget and functions like count don't work, unless I'm missing something.

What I think would be helpful is to expand those operators to include "First Day of the Week". I've built such a query already but really really hate the finagling of the query itself to enable grouping or not able to use the count function in a plain jane ServiceManagement Query via the widget. I just want a number.  


* that pictures much larger than I thought it was going to be. 

Example Query:

select count(WorkItemId) as 'ClosedCount'  
from WorkItem
where 
(TierId = '6CAB9C40-D82F-952D-1F90-5742ECBFE69A' --CTS IR's
or 
TierId = '1B55C7DA-2BCA-78B8-72BE-6741F9CDA875' --CTS SR's
or 
TierId = 'C956674B-B49A-B145-A7E3-783E41471E98') --CTS Ma's
and
(StatusId = '2B8830B6-59F0-F574-9C2A-F4B4682F1681' --IR Resolved
or
StatusId = 'B026FDFD-89BD-490B-E1FD-A599C78D440F') -- SR completed

and

Created >= DATEADD(ww, DATEDIFF(ww,-1,GETDATE()), -1) -- how I get the start of the week
2 votes

Submitted · Last Updated

Comments

  • Gerhard_GoossensGerhard_Goossens Customer Advanced IT Monkey ✭✭✭
    edited May 2018
    I did something like this...

    (Today)
    AND DATEDIFF(dd, ResolvedDate, GETDATE()) = 0

    (Yesterday)
    AND DATEDIFF(dd, ResolvedDate, GETDATE()) = 1

    (This Week)
    AND ResolvedDate >= DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)

    (Last Week)
    AND (ResolvedDate >= DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) - 1, 0) --first day of last week
    AND ResolvedDate < DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)) --first day of this week

    (This Month)
    AND DATEPART(yyyy, ResolvedDate) = @Year
    AND DATEPART(mm, ResolvedDate) = @Month
    AND DATEPART(dd, ResolvedDate) BETWEEN 1 AND @Day

    (Last Month)
    AND DATEDIFF(dd, ResolvedDate, GETDATE()) <= 30

    (This Year)
    AND DATEDIFF(yyyy, ResolvedDate, GETDATE()) = 0

    (Last Year)
    AND DATEDIFF(yyyy, ResolvedDate, GETDATE()) = 1
Sign In or Register to comment.