Configuring Crystal Reports with a Service Desk ORACLE Database

Version 4

    Verified Product Versions

    Service Desk 7.7.x

    Why use this guide?

     

    The set of crystal reports which are supplied with Service Desk connect to the Oracle database using the Oracle OLEDB driver. The OLEDB connection will be pointing to a different server name when the reports are supplied.  In order to use them you will need to change the reports so that they will connect to your own database server.

     

    ** Please note it is recommend to install the 64bit Oracle client the 32bit Oracle client maybe required for Crystal Reports (workstation) installs **

     

    You might also need to follow this guide if you have moved your database to a different server or if you are setting up your reports to run on a test or development system.

     

    This short guide will run you through the steps involved in pointing your reports to use the correct database connection details and also to setup Service Desk to store the report with its associated connection details.

     

    Before you start:

    Install Crystal

     

    You will need to have access to a machine with the version of Crystal designer installed that is compatible with the version of Service Desk that you use.

     

    NOTE:  This is the full version of Crystal not the Crystal Component that you see within console.  Ideally this would be installed on your Service Desk application server.

     

    NOTE: In this guide we will be using version 7.3.2 of Service Desk with Crystal 10.5, windows 2008, and Oracle 10 client.  Different versions will differ slightly but where possible we have tried to high-light the differences.

    Install 64 bit Oracle Client

    On the machine that you have Crystal installed you will need to have Oracle client tools installed so that you can connect to the server.  If you do not have 64bit Oracle client tools with the 64bit OLEDDB drivers installed on your machine please see ADDENDUM 3 of this guide if you need advice on how to proceed.

    Know your database connection details

    You will need to know the database connection details for your site.  If you are the Oracle database server administrator for your Service Desk database then you may want to consider creating a new reporting database login specifically for this purpose which will be able to read all of the Service Desk tables and views.

    Getting Started

    Step 1 – Set the database location on the report(s)

     

    Place any reports that you want to get working within one folder.  Then to set the database location on all the reports in this folder you can run the crystalreportsdsnsetter.exe.  This is usually located in \program files\landesk\landesk reports\ on your application server.  Ensure that when running this application you have file permissions to create a file in this location.

     

    When running the application you are asked to select the folder where your reports are located and you are asked for your database connection details:

     

     

    1.PNG

     

    When you have completed all fields click on the Run button.  The progress bar will then show you the application’s progress as the reports are updated.  Click Close when the update has completed.

     

    Step 2 - Checking one report within Crystal (this step can be skipped when just doing a 64bit install)

     

    To check that the crystal dsn setter has worked.  Locate your report folder and double-click on one of the reports to open it in Crystal.  It’s better not to pick the incident.rpt, problem.rpt, task.rpt or change.rpt for this test.  This is because these reports are designed to be run within console and they prompt you for a guid value (a guid is a auto-generated unique string within your Service Desk database).  Once the report is open, within Crystal click on the refresh button to run the report:

     

    crystal2.PNG

     

    NOTE: The icon for this button has changed through different versions of Crystal so if you are unsure about the button to use, pressing F5 to run the report should work on all versions.

     

    PLEASE NOTE: If you are using a report which contains subreports in it, the subreports are not currently automatically updated by the crystaldsnsetter.  However you can manually set the database location on these and this is explained in ADDENDUM 2 of this guide.

     

    Step 3 – Creating a reporting file share

     

    When a report is run from a console client, the local crystal report component on that client will run the report locally.  This means that all clients need to have access to read the crystal reports.  Please see ADDENDUM 4 – Reports Architecture Overview Diagram for more information on this.  Therefore you will need to create a reporting file share that all console clients and other applications using the reports (such as your web server or your mail server for example) can access and read.  You will then specify the share name under Step 3.

     

     

    crystal3.PNG

    NOTE: On some environments we have seen issues with using a UNC path when used along with a database login that has a password specified.  If you think you have hit this issue then you should be able to either use a mapped drive or a database login with no password to verify if this is the case.

    Step 4– Add the reports into console

     

    Run the Crystal component within console.  Under the main reports tree navigate to the required folder or create a new folder for your reports if one doesn’t exist.

     

    Right-click on the folder and select New Report.

     

    On the screen that appears, browse to one of your saved reports then type in your Oracle login and password details.

     

     

    4.PNG

     

     

    Double-click on the report to run it or click View Report from the actions panel.

     

    NOTE:  With some reports, if you try to run the report within Crystal you may be prompted for a GUID.  These reports are not designed to be run in isolation but at runtime when in a record e.g. when you are in an incident record and you click on the Print icon it will run the incident.rpt report for the record that you are currently in.  If you later want to remove the prompt for the GUID parameter please see this article:

    http://community.landesk.com/support/docs/DOC-16057

     

     

    ADDENDUM 1 – Setting location on just one report (requires the 32bit client to access the OLEDB drivers)

     

    There are instances when you might want to set location on just one report rather than run the crystalreportsdsnsetter.exe.  We will run through these steps in this section.

     

    Open the report in Crystal and select Database – Set Datasource Location.  Click on Create New Connection and select OLEDB.  Then select the Oracle OLEDB provider.

     

     

    5.PNG

     

     

    Type in your oracle service name, and then select Next.  Select the Back button and then you are able to enter your database user and password:

     

    6.PNG

     

     

     

    When you have entered the connection details click Finish.  You can then select the database icon in the top of the screen and the bottom (circled below) and click Update.  This will change the report to be pointing to your required database and server.  Save the report changes.

     

     

    7.PNG

     

    NOTE:  With some reports, if you try to run the report within Crystal you may be prompted for a GUID.  These reports are not designed to be run in isolation but at runtime when in a record e.g. when you are in an incident record and you click on the Print icon it will run the incident.rpt report for the record that you are currently in.  If you later want to remove the prompt for the GUID parameter please see this article:

    http://community.landesk.com/support/docs/DOC-16057

     

    ADDENDUM 2 – Subreports (requires the 32bit client to access the OLEDB drivers)

     

    Reports which include a subreport (an embedded report within the main report) need some extra setup.  This is because either running the crystalreportsdsnsetter.exe or setting location manually on the main report will not automatically change the connection details on the subreport.  In order to set the database location on the subreport, follow ADDENDUM 1 to set location and then click any subreports shown on the top half of the screen then click your database connection then click Update.  Repeat this for each subreport.  Save the report.

     

     

    crystal9.PNG

     

    ADDENDUM 3 – Installing Oracle Client Tools

     

    Each client machine running reporting (for example console machines, the web server running webdesk, etc..) will need to have Oracle client tools installed.  The only component that is required for the reports to run is the Oracle OLEDB driver.  You will also need to copy a tnsnames.ora file off a machine which has more Oracle client tools installed such as Netmanager.  The tnsnames.ora is the file which holds the server name or ip address of your database server whereas when configuring reports you reference your service name as specified in the tnsnames.ora file.  The steps required are:

     

    1. Run the Oracle client installer and select the custom option:9.PNG
    2. Untick other options (if not needed for any other reason) and select Oracle Windows Interfaces and Oracle Provider for OLE DB:8.PNG
    3. Copy the tnsnames.ora file from a machine that has a Oracle netmanager installed and configured to connect to your Oracle Service Desk database. This is normally located within \oracle\product\<version>\client_1\network\admin

     

    NOTE: A machine that is likely to have this installed is the server where your tps (connection to the database that console and webmanager uses) installed.  This is likely to be your web server or application server for Service Desk.

     

    OPTIONAL STEPS

     

    In some instances (depending on Oracle client and operating system versions) users have found that they need to also follow the following steps in order to get Oracle client to connect:


    1. Log on to Windows as a user with Administrator privileges.

    2. Launch Windows Explorer from the Start Menu and and navigate to the ORACLE_HOME directory.

    3. Right-click on the ORACLE_HOME folder and choose the "Properties" option from the drop down list. A "Properties" window should appear.

    4. Click on the "Security" tab on the "Properties" window.

    5. Click on "Authenticated Users" item in the "Name" list (on Windows XP the "Name" list is called "Group or user names").

    6. Uncheck the "Read and Execute" box in the "Permissions" list (on Windows XP the "Permissions" list is called "Permissions for Authenticated Users"). This box will be under the "Allow" column.

    7. Check the "Read and Execute" box. This is the box you just unchecked.

    8. Click the "Apply" button.

    9. Click the "OK" button.

    10. Reboot your computer after these changes have been made. Re-execute the application and it should now work.

    Extract from  http://forums.oracle.com/forums/thread.jspa?threadID=909975&tstart=0

    ADDENDUM 4 – Reports architecture overview diagram

    reports architecture.jpg