Custom Reports - connection to UCCX 7.0 database fails

Unanswered Question
Jul 6th, 2009

Hi,

Let me start off by saying that I have very limited knowledge on SQL.

This is for a two node UCCX Ver 7.0 cluster (hence SQL database).

Customer wants to create customized reports but is unable to pull data from the SQL database - please refer to attachments (you will see a screenshot showing connection failure). Different Login ID and passwords have been attempted.

Please could somebody tell me what the credentials are for accessing data from the UCCX database.

I would also appreciate any information on what the procedure is for accessing data from the SQL database.

Many Thanks

Attachment: 
I have this problem too.
0 votes
  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 4.5 (2 ratings)
Loading.
Jonathan Schulenberg Mon, 07/06/2009 - 16:13

Direct ODBC connections are not supported. The supported path for custom reports is to write new Crystal XI reports and integrate them into the existing Historical Reporting Client. This involves new stored procedures within the db_cra SQL database on CCX and/or changes to the XML and RPT files on the HRC workstation. Note that any custom report files created cannot be rolled into the HRC installer and must be propagated to HRC user workstations (and backed up) manually.

Historical Reports User Guide for Cisco Unified CCX and Cisco Unified IP IVR, Release 7.0(1): http://cisco.com/en/US/docs/voice_ip_comm/cust_contact/contact_center/crs/express_7_0/user/guide/uccx701HR_English.pdf

Database Schema Guide for Cisco Unified CCX and Cisco Unified IP IVR, Release 7.0(1): http://cisco.com/en/US/docs/voice_ip_comm/cust_contact/contact_center/crs/express_7_0/user/guide/uccx70dbschema.pdf

Historical Reporting Administrator and Developer Guide for Cisco Unified CCX and Cisco Unified IP IVR, Release 7.0(1): http://cisco.com/en/US/docs/voice_ip_comm/cust_contact/contact_center/crs/express_7_0/programming/guide/uccx70hradmdev.pdf

Also, there was a session at Cisco Live 2009 on CCX custom reporting and future road map plans. One of the developers presented and provided a good orientation. The same orientation to custom reporting is also supposedly posted to PEC (if you are a partner); however, I haven't seen this VoD myself.

P.S. Don't bother getting around the ODBC restrictions. Version 8.0 (due out Q1CY10) will be a sealed Linux appliance identical to UCM. Don't waste your time writing something that puts the customer into an unsupported state.

sovannary.huy Wed, 07/08/2009 - 01:54

Hi,

Did you try to connect from client computer? If so, please make sure that client computer and CRS server use the same Administrator password.

lohjintiam Tue, 07/21/2009 - 01:21

"Also, there was a session at Cisco Live 2009 on CCX custom reporting and future road map plans. One of the developers presented and provided a good orientation. The same orientation to custom reporting is also supposedly posted to PEC (if you are a partner); however, I haven't seen this VoD myself"

j.schulenberg, do you have the link for this?

sovannary.huy,

Must the client computer login (into Windows) using the same Administrator username and password as the UCCX server? Any better way of doing this? I'm assuming port 1433 must also be opened between UCCX server and client computer.

Thanks!

-JT-

joesnyde Tue, 07/21/2009 - 09:30

Must the client computer login (into Windows) using the same Administrator username and password as the UCCX server? Any better way of doing this? I'm assuming port 1433 must also be opened between UCCX server and client computer.

Answer is no, you would login with your Windows account on the PC. You would login to HRC with the credentials that are created in UCCX. Be sure that the user has the reporting capability added.

lohjintiam Tue, 07/21/2009 - 10:02

hi joesnyde,

Thanks for your response. The issue I'm facing is not related to HRC access which is understood that existing uccx user (with reporting capabilites) is able to login.

The issue is related to creating custom report using Crystal in the same client pc having HRC. I wouldn't be able to create odbc connection as the uccx user is neither a uccx database user (Security->Logins) nor a uccx server user (Computer Management -> Users).

The only workable solution currently is to have the same administrator username/password in both client pc and uccx server (as well as logging into client pc using administrator credentials).

Thanks!

-JT-

joesnyde Tue, 07/21/2009 - 10:09

What about adding that user to the local admin account? Or adding that user as part of the Domain Admin group? Is this a possiblity?

gsidhu Fri, 07/31/2009 - 08:43

A video for creating custom reports can be found on PEC.

Go to "Advanced" search. Select the packages tab and type in CCX Ignite and it will return 4 packages. Under CCX Ignite - Support there is a course on creating custom reports.

mansssorange Thu, 03/25/2010 - 03:36

Hi gsidu

Hope all is fine at your side.

Can you tell me please if you were able to reach anything regarding the SQL access??

I am facing the same problem now and if you can give me a solution that would be really great. I have been searching on tne net for about a week now but with no clue.

Appreciate your assistance please, thanks a lot.

gsidhu Thu, 03/25/2010 - 05:24

Hi,

My issue did not get resolved and I haven't taken any action since my last post

Aaron Harrison Thu, 03/25/2010 - 08:35

Hi

See my post on this issue.

https://www.myciscocommunity.com/message/34036#34036

To get SQL access to the SQL server on UCCX if running in Windows Auth mode from a domain workstation, you need to create a local account on the UCCX box with the same username and current password as your domain account. You then grant that local account access to SQL.

Regards

Aaron

Please rate helpful posts...

mansssorange Sat, 03/27/2010 - 04:27

Hi Aron,

Many thanks for your post, but can you help me please with the following:

1. How can I grant that user access to the SQL database?

2. Will I need the CRSAdministrator account to give him that access? Long story short the password is not available.

3. If I will need the CRSAdministrator password, is it OK to change it through the UCCX Admin Utility? We have one UCCX server only so I guess it will not cause a problem, but I am afraid changing it will ruin anything else.

Sorry for all these questions but I am not an experienced in SQL that much, thanks in advance.

Regards

Aaron Harrison Sat, 03/27/2010 - 05:14

Hi

To grant permissions to the DB, you need a local administrator account on the UCCX server (not an application administrator/ccm user).

A quick way is to grant add user you create on the UCCX to the local administrator group in Computer Management/Users - any local admin gets SA rights to the SQL server.

Otherwise:

Steps:

Go into SQL Enterprise Manager in start/programs/Microsoft SQL server.

Click on the 'localserver' or your UCCX server name under Microsoft SQL Servers

Expand the 'Security' item

Click 'Logins'

On the right, right click and hit 'New Login'

You should be able to select the user by clicking a ... button or enter it in the username box in servername\username format. If you do this right, password should be greyed out as it will use the windows password

Set the default database to db_cra

Click on the Database access tab, and then click db_cra in the top box.

Tick the 'Db_owner' role in the bottom box

Hit OK.

Regards

Aaron

mansssorange Sat, 03/27/2010 - 05:35

Hi Aaron,

Thank you very much for your help, will try that and feed you back with the results.

Regards

mansssorange Sun, 03/28/2010 - 23:51

Hello Aaron,

It worked great the way you said

Really thanks a lot, will get back to you in case we are stuck again. Many thanks.

Regards

Actions

This Discussion