Anonymous Block Query

Version 1

    Subject: RE: Anonymous Block Query
    Replied by: Stephan Hartman on 30-06-2012 03:28:03 PM
    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
    This document was generated from CDN thread

    Created by: SenthilKumar Sankar on 29-06-2012 02:57:43 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

    Subject: RE: Anonymous Block Query
    Replied by: SenthilKumar Sankar on 02-07-2012 07:10:56 AM
    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

    Subject: RE: Anonymous Block Query
    Replied by: SenthilKumar Sankar on 02-07-2012 08:46:04 AM
    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

    Subject: RE: Anonymous Block Query
    Replied by: SenthilKumar Sankar on 02-07-2012 08:47:55 AM
    Hi

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

    Regards,
    Senthil

    Subject: RE: Anonymous Block Query
    Replied by: Senthil Kumar Natarajan on 02-07-2012 08:36:21 AM
    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

    Subject: RE: Anonymous Block Query
    Replied by: Luis Yrigoyen on 02-07-2012 09:45:23 AM
    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.

    Subject: RE: Anonymous Block Query
    Replied by: SenthilKumar Sankar on 02-07-2012 12:42:54 PM
    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

    Subject: RE: Anonymous Block Query
    Replied by: Nathan Luk on 02-07-2012 08:18:42 PM
    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

    Subject: Re: New Message from SenthilKumar Sankar in Contact Center Reporting (CCR)
    Replied by: Nathan Luk on 04-07-2012 06:58:13 AM
    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.

    Subject: RE: Anonymous Block Query
    Replied by: SenthilKumar Sankar on 04-07-2012 10:43:06 AM
    Hi Nathan,

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

    Regards,
    Senthil