CA Service Management

 View Only
  • 1.  cabi report - tickets by group supervisors

    Posted Apr 12, 2016 12:00 PM

    Hi all,

     

    I have been trying to create a Web Intelligence report that produces all open tickets based on (groups') supervisor names.  The requirement is to return all open tickets in John Foo's and Joe Bar's groups.

     

    In the query editor in Web Intelligence, I selected 1) [Request Detail].[Request number], 2) [Request Detail].[Group name], and 3) [Contact Detail].[Supervisor combo name] with a appropriate filter, but the query seems to hang forever.  We have fewer than 50,000 tickets.  Can someone help, please?

     

    Vanessa



  • 2.  Re: cabi report - tickets by group supervisors

    Posted Apr 18, 2016 02:28 AM

    You're not selecting the right objects for the report and as a result you're creating a cartesian product. The supervisor field you're using doesn't have nothing to do with group managers, unless your setup uses this instead of the regular group manager flag in the group memberships table.

    I don't have a test setup with the OOB universe available so I'm going out on a limb here and attempt helping you without actually testing the results, but I think you need the fields [Request Detail].[Ref Num] and [Request Detail].[Group Name] as the result objects and then in the filter section you need to create a subquery where the first field you drag the [Request Detail].[Group Name] and in the second field you drag the [Contact Detail].[Contact Extended].[Contact Grpmem].[Group Name]. Then in the filter section of that subquery you add [Contact Detail].[Contact Extended].[Contact Grpmem].[Manager Flag] = 1 and [Contact Detail].[Contact Extended].[Contact Grpmem].[Member Combo Name] like you want it to be filtered.

    Be aware that a group can have more than one manager and a manager can manage more than one group, so you might get overlap with the results for different group managers.



  • 3.  Re: cabi report - tickets by group supervisors

    Posted Apr 18, 2016 06:25 AM

    Does this have to be a WEBI report? I'm almost lingering towards getting same result directly on the SDM gui using a Stored Query and Scoreboard node.

     

    See the following OOTB Stored Queries you may use.

     

    Stored Queries_2.gif



  • 4.  Re: cabi report - tickets by group supervisors

    Posted Apr 18, 2016 06:42 AM

    Due to the limits in the product you can't get this to work in a way that would make it sensible to use the scoreboard for it. You can get the tickets assigned to the logged in user,the ticket assigned to the logged in user's groups but not the tickets assigned to groups where the logged in user is the manager. Unless it's been fixed within the last two years. For the manager's groups' tickets you'd have to hard-code the manager and then it would be useless for scoreboard use.



  • 5.  Re: cabi report - tickets by group supervisors
    Best Answer

    Posted Apr 18, 2016 11:43 AM

    Marty helped me with this.  What I need to do is to hard code the inner join of cr and cnt with cr.gorup = cnt.id.  But I am sure your solution will come in handy one day.  Thank you for your help.