Contact Center Reporting

« Back to Contact Center Reporting

Anonymous Block Query

Combination View Flat View Tree View
Threads [ Previous | Next ]
Anonymous Block Query
Answer
6/29/12 7:57 PM
Hi all,
 
I am new to CUIC for creating Reports, I was trying to do the accomplish the below task like.
 
1) If the Select the Agent Name from the list, it should show me the AgentTeamName and AgentName
 
Below is the query i given and created a Report Defenition,
 
select  Team.EnterpriseName, Agent.EnterpriseName from Agent_Team_Member TeamMember, Agent Agent, Agent_Team Team where Agent.EnterpriseName = :AgentName and Team.AgentTeamID = TeamMember.AgentTeamID and Agent.SkillTargetID = TeamMember.SkillTargetID
 
By giving the above Query, I was able to create parameters and only field was listed and that was EnterpriseName.
 
 
When i run the Report which was based on this Report Defenition by selecting one AgentName, I am not getting anything and i get 0 records with just one heading "[url=javascript:void(0)]EnterpriseName"
 
I am doing something wrong and my understanding somewhere is wrong. Kindly request someone to correct me.
 
Regards,
Senthil
Flag

RE: Anonymous Block Query
Answer
6/30/12 8:28 PM as a reply to SenthilKumar Sankar.
Hi Senthil,

This was provided to another poster to use in a report definition and this will return the following, the agents full name there login name, team name and the 4 digit team ID.

select Distinct
AgentName = (PERSON.FirstName + ' ' + PERSON.LastName),
AGENT.EnterpriseName as AgentLoginName,
TEAMS.EnterpriseName as Team,
TEAMS.AgentTeamID as TeamID
from t_Agent AGENT
left join t_Person PERSON
on AGENT.PersonID = PERSON.PersonID
left join t_Agent_Team_Member MEMBERSHIP
on AGENT.SkillTargetID = MEMBERSHIP.SkillTargetID
left join t_Agent_Team TEAMS
on MEMBERSHIP.AgentTeamID = TEAMS.AgentTeamID
left join Agent_Team_Member TEAMID
on MEMBERSHIP.AgentTeamID = TEAMID.AgentTeamID
order by TEAMS.EnterpriseName

then build your report from the report definition.

If this is not exactly what your looking for, your best place to start troubleshooting this is within SQL Management Studio.
Debug your query there and when it produces the results your looking for, create a RD (Report Definition) out of it.

Hope this Helps
Steve
Flag

RE: Anonymous Block Query
Answer
7/2/12 12:10 PM as a reply to Stephan Hartman.
Thank You Steve,

This is how my Query looks like

select Team.EnterpriseName as TeamName, Agent.EnterpriseName as AgentName from t_Agent_Team_Member TeamMember, t_Agent Agent, t_Agent_Team Team where Agent.EnterpriseName = :AgentName and Team.AgentTeamID = TeamMember.AgentTeamID and Agent.SkillTargetID = TeamMember.SkillTargetID

In SQL Studio, I get 1 Colums with the name i have given for Agent.EnterpriseName.

Result

TeamName AgentName
Test_Team senthil.kumar

In CUIC, i dont get any resultsm its just blank

Regards,
Senthil
Flag

RE: Anonymous Block Query
Answer
7/2/12 1:36 PM as a reply to SenthilKumar Sankar.
Couple of items to verify.
1. Are you creating this query as 'anonymous block' where you are able to create parameters ?
2. Are you running this query against the AW and not HDS?

-senthil
Flag

RE: Anonymous Block Query
Answer
7/2/12 1:46 PM as a reply to SenthilKumar Sankar.
Sorry Steve,

When i run the report, there is SQL Tab, there one query shows up and that doesnt run in SQL Studio it gives error,

Below is that query

select Team.EnterpriseName as TeamName, Agent.EnterpriseName as AgentName from t_Agent_Team_Member TeamMember, t_Agent Agent, t_Agent_Team Team where Agent.EnterpriseName = '10682' and Team.AgentTeamID = TeamMember.AgentTeamID and Agent.SkillTargetID = TeamMember.SkillTargetID

One thing what i did is in the Report Defenition Parameter Settings, I selected the Value List "Agents" and it doesnt display anything.

If i use the same query with Blank Value list and if i manully enter the AgentID, the results are showing up.

Not sure why, Please clarify.

Regards,
Senthil
Flag

RE: Anonymous Block Query
Answer
7/2/12 1:47 PM as a reply to SenthilKumar Sankar.
Hi

1) Yes it is anonymous block, Parameters are created
2) Yes it is AWDB

Regards,
Senthil
Flag

RE: Anonymous Block Query
Answer
7/2/12 2:45 PM as a reply to SenthilKumar Sankar.
Senthil,
 
It seems that your Value List is wrong.  Can you go to the value lists and select the agent and team and click on show values.  It should show display all the values if not then their wrong.
Attached are two reports similar to whta your trying to do, I think, one shows an agent's membership by selecting an agent and the other shows team members by selecting teams.
When you import a report, its definition and value lists will be created. 
 
Import them to see if you can use them as reference or production.
Attachments:
Flag

RE: Anonymous Block Query
Answer
7/2/12 5:42 PM as a reply to Luis Yrigoyen.
Hi Luis,

I checked my Value List, It list all the agents listed. But am not sure why this is not working when the Value List is choosen as Agents

Regards,
Senthil
Flag

RE: Anonymous Block Query
Answer
7/3/12 1:18 AM as a reply to SenthilKumar Sankar.
Hi Senthil,

The value lists always return SkillTargetID so your where clause should be:

WHERE
Team.AgentTeamID = TeamMember.AgentTeamID
AND Agent.SkillTargetID = TeamMember.SkillTargetID
AND Agent.SkillTargetID IN ( :agents )

Not Agent.EnterpriseName IN ( :agents )
(or whatever you want your parameter to be called)

Cheers,

Nathan Luk
Flag

Re: New Message from SenthilKumar Sankar in Contact Center Reporting (CCR)
Answer
7/4/12 11:58 AM as a reply to SenthilKumar Sankar.
Hi Senthil,

The value lists always return skilltargetid so your where clause should have:

Agent.SkillTargetID = :agents

Not EnterpriseName.

Cheers,

Nathan Luk

On 03/07/2012, at 2:52, "Cisco Developer Community Forums" <cdicuser@developer.cisco.com> wrote:

> SenthilKumar Sankar has created a new message in the forum "Contact Center Reporting":
>
> --------------------------------------------------------------
> Hi Luis,
>
> I checked my Value List, It list all the agents listed. But am not sure why this is not working when the Value List is choosen as Agents
>
> Regards,
> Senthil
> --
> To respond to this post, please click the following link:
>
> <http://developer.cisco.com/web/ccr/forums/-/message_boards/view_message/5976876>
>
> or simply reply to this email.
Flag

RE: Anonymous Block Query
Answer
7/4/12 3:43 PM as a reply to Nathan Luk.
Hi Nathan,

Yes you are right, It is working for me now as expected.

Regards,
Senthil
Flag