Cisco Agent Detail Report

[rbermel83]

When running the Cisoc Agent Detail Report I am seeing the following SQL commented out:

/*

            INSERT #selected_agents(agentID, profileid)

            SELECT DISTINCT r.resourceID, r.profileid

            FROM db_cra.dbo.ResourceGroup rg, db_cra.dbo.Resource r, #selected_names sn

            WHERE       rg.resourceGroupID = r.resourceGroupID AND

                  rg.resourceGroupName = sn.name and

                  rg.profileid = r.profileid and

                   (r.active = 1 or

                  r.dateinactive >= @starttime)

*/

which is making it difficult to report by Resource Group. My question is has anyone run into the root cause that this has been commented out? My concern is that it was commented out becuase it caused some undesirable outcome. Thank you in advance.

[keithgarris]

What version of Contact Center are you using? What stored procedure are you seeing that commented out in? We have UCCX 7 and the SP that calls the Agent Detail report doesn't have that commented out.

[danths]

sp_getListofAgents has the following code....per the code it ignores the resolurceList passed without joining the it against the resourcegroup table. Thi s is UCCX reporting 7.0(144) build.

IF (@resourceList <> 'NULL')
begin

  set @rgselected = 1 

  EXEC @returncode = db_cra.dbo.parse2 @resourceList, @opvalue = @op

/*
  INSERT #selected_agents(agentID, profileid)
  SELECT DISTINCT r.resourceID, r.profileid
  FROM db_cra.dbo.ResourceGroup rg, db_cra.dbo.Resource r, #selected_names sn
  WHERE  rg.resourceGroupID = r.resourceGroupID AND
       rg.resourceGroupName = sn.name and
   rg.profileid = r.profileid and
         (r.active = 1 or
   r.dateinactive >= @starttime)
*/

  insert #selected_agents(agentid, profileid)
  select distinct resourceid, profileid
  from db_cra.dbo.resource
  where  (active = 1 or
   dateinactive >= @starttime)

  if (@debugflag = 1)
  insert sqllogging(eventtime, message) values (getdate(),
    'AgentStateInterval: got resource list')
end

Thanks

sd

[keithgarris]

My version of UCCX Historical Reports is 7.0(1.9). I wouldn't think that there would be that much of a difference between the two. Deconstructing the stored procedure that the XML file uses I get the following:

sp_agent_detail - > getAgentDetail -> parse2 -> parseString

This populates a couple temporary tables that are used to get your results and return them so the crystal reports templates can give you a report. How did you get to the point that you were looking at the getListofAgents stored procedure?

[danths]

ICD_Agent_Call_Summary [ us_en ] version which refers to

Exec db_cra.dbo.sp_agent_call_summary '$StartDate', '$EndDate', $SortBy, N'$ResourceGroup', N'$AgentName', N'$SkillName', N'$TeamName'

which then calls getListOfAgents which takes in the resourcegroup passed and does parse2 [ so far so good ].

* Parse2 in turn calls ParSeString which inserts the each token ( rather resource name ) in a temporary table selected_names

--defective code

Then irrespective of the resourcegroup passed, it does select on resource table to get a list of agents and inserts into the temp table #selected_agents.

So both temporary table selected_names and selected_agents are now populated.

Back @ sp_agent_call_summary immediately following exec of getListOfAgents, it does not touch selected_names again, selected_agents is touched only for joins with other tables in my version.  I would think that even for most sloppiest code, selected_names would be used to delete agents from "selected_agents" who do not belong to any resourcegroup in the selected_names table before using it for joins.

In some of the other reports I see how parse2 and selected_names and selected_agents is used to get the right list of agents, however not on this one.

Thanks for your help.

sd

[keithgarris]

Ok, can you copy your sp_agent_call_summary into a text file and attach it? I think I might know where the difference is but I'd like to have your source code to compare to mine.