Skip to main content

Notifications

Customer experience | Sales, Customer Insights,...
Answered

Create Dashboard or Search query for cases with no open tasks

(0) ShareShare
ReportReport
Posted on by 15

Hi, 

I want to be able to retrieve cases which are assigned to specific user and have no uncompleted or open tasks? 

Ideally this would be a dashboard but having tried there and the advanced find I think im more likely to have success on advanced find but every time i test a search it doesnt work. 

Appreciate any help you can provide. 

Thanks, 

Mortoncj

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Create Dashboard or Search query for cases with no open tasks

    Hi Partner,

    Please click Yes under "Did this answer your question?" to close this thread if the answer is helpful.

     pastedimage1628818036684v1.png

    Thanks. 

    Regards,

    Leah Ju

    Please mark as verified if the answer is helpful. Welcome to join hot discussions in Dynamics 365 Forums.

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Create Dashboard or Search query for cases with no open tasks

    Hi Mortoncj,

    Or you can download Sql 4ds tool of XrmToolBox to use sql to query it.

    SQL 4 CDS  Â· XrmToolBox

    pastedimage1628750714800v1.png

    SELECT   DISTINCT e.title,
                      e.owneridname
    FROM     incident AS e
    WHERE    e.ownerid = '73988725-b8a1-eb11-b1ac-002248045db0'
             AND (NOT EXISTS (SELECT *
                              FROM   incident
                                     INNER JOIN
                                     task AS aa
                                     ON incident.incidentid = aa.regardingobjectid
                              WHERE  aa.statecode = 0
                                     AND title = e.title))
    ORDER BY e.title ASC;
    

    Regards,

    Leah Ju

    Please mark as verified if the answer is helpful. Welcome to join hot discussions in Dynamics 365 Forums.

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Create Dashboard or Search query for cases with no open tasks

    Hi Mortoncj,

    I have used below screenshot for query case with not open activity, but it still bring up cases with tasks that open, and cases without any tasks can't be returned.

    pastedimage1628742518883v1.png

    For example, case-qw1 has two tasks, one is open, another is completed, it has been returned with above query.

    pastedimage1628742933735v2.png

    Maybe you can do two steps to achieve it indirectly.

    (1)Query all cases that are assigned to specific user.

    pastedimage1628743452641v4.png

    (2)Query all cases that are assigned to specific user and related task is open.

    pastedimage1628743333461v3.png

    So qw3 and qw4 are cases you want.

    Regards,

    Leah Ju

    Please mark as verified if the answer is helpful. Welcome to join hot discussions in Dynamics 365 Forums.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Congratulations to the January Top 10 leaders!

Check out the January community rock stars...

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,162 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,962 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans