Unable to use SELECT TOP (10) on CUIC Report Definition?

Version 1
    This document was generated from CDN thread

    Created by: Gerard O'Rourke on 21-10-2012 01:02:00 PM
    I would like to be able to create a report which outputs just the first 10 results from an SQL query.
     
    My SQL query is fine when I run it on the server, but if I create the Report Definiation, it will not allow you use TOP (10) in the Query.
    Anyone have any ideas to be able to work around this issue?
    Is there another way to achieve the same result or does anyone know why I am unable to use TOP (10) in the SELECT Query?
    Regards,
    Gerry
     
     
     
     

    Subject: Automatic reply: New Message from Gerard O'Rourke in Contact Center Reporti
    Replied by: Michael Oldham on 21-10-2012 01:03:08 PM
    I'm currently overseas till Nov 5th  with limited access to email. I will try to respond to your request in a timely fashion.

    Subject: AUTO: Gregory Grebin is unavailable (returning 10/26/2012)
    Replied by: GREG GREBIN on 21-10-2012 01:04:07 PM
    I am out of the office until 10/26/2012.

    I am on PTO with limited access to email and phone.



    Note: This is an automated response to your message "New Message from
    Gerard O'Rourke in Contact Center Reporting (CCR) - Contact Center
    Reporting: Unable to use SELECT TOP (10) on CUIC Report Definition?" sent
    on 10/21/2012 01:02:05 PM.

    This is the only notification you will receive while this person is away.


    Prime Therapeutics made the following annotations
    ---------------------------------------------------------------------
    CONFIDENTIALITY NOTICE: The information contained in this communication may be confidential, and is intended only for the use of the recipients named above. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication, or any of its contents, is strictly prohibited. If you have received this communication in error, please return it to the sender immediately and delete the original message and any copy of it from your computer system. If you have any questions concerning this message, please contact the sender.


    ---------------------------------------------------------------------

    Subject: RE: Unable to use SELECT TOP (10) on CUIC Report Definition?
    Replied by: Gerard O'Rourke on 24-10-2012 12:03:25 PM
    Just trying again...
    Any idea on why I cannot do an SQL query with a SELECT TOP (X) command in CUIC?
    Example:
     
    SELECT TOP (10)
    Route_Call_Detail.DateTime,
    Route_Call_Detail.ANI,
    Route_Call_Detail.DialedNumberString,
    Route_Call_Detail.RoutingClientID,
    Routing_Client.EnterpriseName AS RoutingClient_EnterpriseName,
    Call_Type.EnterpriseName AS CallType_EnterpriseName,
    Master_Script.EnterpriseName AS Master_Script_EnterpriseName,
    Route_Call_Detail.FinalObjectID,
    Route_Call_Detail.Label

    FROM Route_Call_Detail

    INNER JOIN Script ON Route_Call_Detail.ScriptID = Script.ScriptID
    INNER JOIN Master_Script ON Script.MasterScriptID = Master_Script.MasterScriptID
    INNER JOIN Call_Type ON Route_Call_Detail.CallTypeID = Call_Type.CallTypeID
    INNER JOIN Routing_Client ON Route_Call_Detail.RoutingClientID = Routing_Client.RoutingClientID

    ORDER BY

    Route_Call_Detail.DateTime DESC
     

    Subject: RE: Unable to use SELECT TOP (10) on CUIC Report Definition?
    Replied by: Gerard O'Rourke on 15-03-2013 05:34:02 AM
    Got this working ages ago.
    Should have update this dicussion.
    below is a Report which grabs the last 20 calls.
    You can filter on ANI so you only see the calls you made etc.
    Very useful for troubleshooting and finding out what the Dialed number is, if the dialplan is not well designed.
    http://orourke.tv/web/doku.php?id=cisco:uc:icm:sql
    http://orourke.tv/web/lib/exe/fetch.php?media=cisco:uc:icm:last20calls-callroutedetail_v1-02.zip
    Below is an extract of the SQL
    SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON
    SELECT TOP (20)
    StartDateTime = Route_Call_Detail.DateTime,
    DBDateTime = Route_Call_Detail.DbDateTime,
    ANI = ISNULL(Route_Call_Detail.ANI,'NULL'),
    DialedNumberString = Route_Call_Detail.DialedNumberString,
    RoutingClientID = Route_Call_Detail.RoutingClientID,
    RoutingClientName = Routing_Client.EnterpriseName,
    ..
    ..
    ..
    ..
    FROM
    Route_Call_Detail(nolock),
    Script(nolock),
    Master_Script(nolock),
    Call_Type(nolock),
    Routing_Client(nolock)
    WHERE
    (Route_Call_Detail.ScriptID = Script.ScriptID) AND
    (Script.MasterScriptID = Master_Script.MasterScriptID) AND
    (Route_Call_Detail.CallTypeID = Call_Type.CallTypeID) AND
    (Route_Call_Detail.RoutingClientID = Routing_Client.RoutingClientID)AND
    (Route_Call_Detail.DbDateTime > GetDate()-1)
    ORDER BY
    Route_Call_Detail.DbDateTime DESC