Reports Using MS Access 2

[mt6322]

I was asked by our new call center manager the following question -

I notice that the Symposium reports we run use Crystal Reports. I typically prefer MS Access. However, in order to design my own reports I would need to know the location of the tables. Is there a way I can locate the tables and write reports in MS Access?

If it is possible - how ? Thank you for your help.

 

[lretrievers]

All that type of information is in the Historical Data Dictionary.....however, I don't think you will be able to do it with Access.

 

[brewerdude]

Using MS Access to write Symposium reports is actually fairly easy. If you're experienced with Access, you'll find it easier than Crystal; however, it is easier to distrbute reports with Crystal b/c they can be imported and run from Symposium. Anyway, to access the Symposium tables:

1. Make sure you install the thick client. If you're using the webclient, you will need to install the Sybase application on the PC you're going to use (this installs the ODBC driver for Sybase)

2. From MS Access, go to the tables tab, and right-click and select 'Import...'

3. Change 'Files of Type' to ODBC Databases ()

4. On Select Data source box, change tab to Machine Data Source

5. Scroll down and select ICCM_PREVIEW_DSN (Symposium installs lots of these, so make sure you get the proper one)

6. You'll get a 'Logon to Sybase' box. Make sure the server name says ICCM_PEVIEW. The login id and password will be what you use to login to Symposium. Change the database to 'blue'.

7. You will now be presented with a list of tables to link. The definition and contents of each table is in the data dictionary, you may want to peruse that for the report you'd like to build.

8. Select your report and click ok

9. Happy querying and reporting!


If you have issues, try the following...
1. Open your control panel, and select Administrative Tools, then Data Sources (ODBC)
2. Go to the System DSN tab and select ICCM_PREVIEW_DSN and click Configure.
3. Click on the Connection tab and look at the field for Interfaces file and make sure it has C:\Program Files\Nortel Networks\Symposium Call Center Server\SqlIni\ini\sql.ini
4. If it doesn't have it, put that in, click OK


If it still doesn't work, try this:
1. Navigate to C:\Program Files\Nortel Networks\Symposium Call Center Server\SqlIni\ini\sql.ini
2. there should be a section that looks like this:

[ICCM_PREVIEW]
$BASE$01=NLNWLINK,ICCM_Server
$BASE$00=NLWNSCK,ICCM_Server,5000
query=$BASE$00;$BASE$01;

3. Change the ICCM_Server to the CLAN ip addy of your Symposium. It will look like this:

[ICCM_PREVIEW]
$BASE$01=NLNWLINK,ICCM_Server
$BASE$00=NLWNSCK,XXX.XXX.XXX.XXX,5000
query=$BASE$00;$BASE$01;

4. then save the sql.ini file


Enjoy!

 

[mt6322]

Thanks a lot for such a detailed info. I will follow the instructions myself and will keep you posted.

 

[markus115]

You don't need to install the thick client to get the ODBC driver for Sybase. Here are some instructions I have written and used several times to get folks access to the DB.



Setting up an ODBC Connection to Symposium database (SYBASE)

On the machine you want to setup this ODBC connection insert the Nortel Client Application CD-ROM and navigate to the ASE12CLT folder and run setup.exe.

The Sybase Installer should start. Select Customized Install. Click CLEAR SELECTIONS on the list of applications to install then select only the Sybase ASE OBDC Driver to install.
Once installed the machine will ask you to reboot.
***Make sure you leave the CD in when you reboot.

Once the machine reboots click on START, RUN and type in dsedit
Click OK on the Select Directory Service window.
Choose Server Object ??Add and give it a name like Symposium_Reports.
Now under attributes click on Server Address and add the IP address of the Symposium server and port 5000. (Example: 10.55.12.34,5000)

Now go to ODBC connections under Control Panel, Administrative Tools and click on the System DSN tab

Click ADD and scroll all the way down to select the SYBASE ASE ODBC Driver
Under the Datasource name you can pick any name you want
In the server name field you must specify the name you setup in the dsedit step above (i.e. Symposium_Reports)

Now click on Test Connection

You will need a Symposium account to connect to the database. This account must be built in the Classic Client.

The Database name field can be left blank to connect to the database.


You can now access the Symposium database:

 

[jahillia]

brewerdude,
I am getting the following error when trying to connect:
"[SYBASE][ODBC Sybase Driver][Sybase]ct_connect(): network pakcet layer: internal net library error: Unable to find protocol driver structure"

I tried all of your steps, but stil unable. Is there something that was not installed when they installed the Nortel Networks?

 

[jstoehner]

Not sure if you're still watching this thread but did you ever figure it out? I've been asked to do some custom reports for our call center as well and can't figure out how to connect to the database and the tables I need.

 

[jahillia]

Yes. It was in how my ID was setup on the our Symposium Server. I was able to link to the tables through Access and working on reports now

What kind of issue are you having?

 

[jstoehner]

Well, I am a few days away from starting this project but was trying to gather as much 'how to' information as I could. I am fairly decent at Crystal Reports but know next to nothing about Symposium. The guy who did reporting for our call center left on,,,,shall we say,,,short notice. Unfortunately there were few clues left behind on his machine and after spending an hour I could not even figure out how to connect to the DB or which tables to use.

 

[jahillia]

Do you have any manuals? I might have some I can send you electronically. When you or IS installed Symposium, there should have been several ODBC DSN's established. There should be a System DSN called ICCM_PREVIEW_DSN. That is the connection you are looking for. I am still learning Crystal and Symposium. I am an Avaya and Access person historically, but the manuals have helped quite a bit.

 

[jstoehner]

I'm going to show my Symposium ignorance but maybe someone can chime in and get me started in the right direction. I have a one page document left by the guy who did this stuff before. On the top he has:

SCCS
Login: XXXXXXX
Password: xxxxxxx
CLAN IP Address: 10.129.xx.xx
ELAN IP Address: 10.11.xx.xx

Next he has login, password, and URL/IP information for "Symposium Web Client", "Symposium Web Client Server", and "SMI Workbench". What, if any, of the information above will be needed to create a connection from my machine to do custom reports via Crystal? What is CLAN IP vs. ELAN IP?

Our telecom group doesn't even know what I'm talking about when I ask if they have a CD-ROM for the Nortel Client Application. From reading above and elsewhere, I assume this is a must to do any reporting from my machine (which has obviously not had any Nortel/Symposium software installed on it previously).

Help!

 

[brewerdude]

CLAN = Customer LAN - this is your corporate network
ELAN = Enterprise LAN - this is a little network that gets setup just for the PBX applications. It is completely separate from your corporate network.

When connecting to Symposium from your workstation, you'll use the CLAN IP.

You'll definitely need the client CD to access the Symposium tables. If can either install the client itself, and it will create the drivers you need. Or you can just install the SYBASE client, and setup the DSN manually. Both methods are in posts above this one.

 

[jahillia]

Do you not have the Symposium Call Center installed on your PC? Check to see if you have this directory:
C:\Program Files\Nortel Networks\Symposium Call Center Server\Client\en\guides\pdfs

On my system, this is where I have found 5 PDF manuals that have been EXTREMELY helpful.

 

[jstoehner]

No I do not have it installed because I am not part of the call center group. I was just asked to help with some of their custom reports because I know Crystal Reports. Unfortunately, our IT department is undergoing some big changes and there is nobody left in the telecom group who has a clue. I'm trying to track down the Symposium Call Center CD to install the client but they can't even help me with that. All they can say is that a rep is supposed to be in the office soon and they will ask him. Argh!!

I did find the Historical Data Dictionary and it has a wealth of information. If I can just get connected I think I will be able to figure the rest out.

 

[yyrkroon]

you dont need the symposium client cd if all you're doing is using it for the sybase client, just go to sybase web site and download sybase open client 12.

 

[jpstoehner]

Geez, I went to Sybase and it appears you have to have an account with them to get Open Client 12. Anyone know of a way to get the driver I need without accounts/registrations?

By the way, our IT guy came down and handed me two CD-ROM's while saying "I hope this is what you need." One of them is the "Symposium Call Center Web Client 4.5" which happens to have a GIANT crack on the CD, and the other is the "Cleint Supplementary CD-ROM for "Symposium Call Center Server 5.0" but it appears to be patches only. Any chance that one includes the driver I need??

Argh!!!!!!

 

[brewerdude]

Roadblocks!

Unfortunately, what you need is

Client Application CD-ROM
Version 04.01.01
Revision 6.3 for server versions 4.0, 4.2, and 5.0
NT9F43AE A0828089

 

[brewerdude]

try this:

hZZp://

http://www.transferbigfiles.com/Download.aspx?id=aa2f8cf2-5038-4b00-98a2-9962c57d7b27

change the Z's to t's

 

[jpstoehner]

Wow, thank you very much.

I feel so close but yet so far. When I test the ICCM_PREVIEW_DSN connection from the ODBC Data Source Administrator it gives me the following error...

"user api layer: internal client library error: HAFAILOVER: Trying to connect to server."

I tried adding the CLAN IP address to the sql.ini file but no luck with that either.

I also tried the method described by markus115 using just the ODBC driver for Sybase. I end up with the same error message.

Thanks again for helping me get this far.

 

[brewerdude]

Unfortunately I've never seen that error.

Check this:
Click on the Connection tab and look at the field for Interfaces file and make sure it has C:\Program Files\Nortel Networks\Symposium Call Center Server\SqlIni\ini\sql.ini