Configuring Crystal Reports with a Service Desk SQL Database

Version 10

    Verified Product Versions

    Service Desk 7.6Service Desk 7.7.xService Desk 7.8.xService Desk 2016.xAsset Manager 2016.xService Desk 2017.x



    Why use this guide?

     

    The set of crystal reports which are supplied with Service Desk connect to the SQL database using OLEDB. 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.

     

    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 SQL Server 2008.  Different versions will differ slightly but where possible we have tried to high-light the differences.

    Know your database connection details

     

    You will need to know the database connection details for your site.  If you are the database server administration for your Service Desk database then you may want to consider following the instructions within ADDENDUM 1 of this document “Creating a Reporting database user” before you go any further.

    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:

    crystal1.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

     

    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 reports 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 3 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 4.

     

     

    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. Also, replacing the server name with the IP address has been reported to resolve this.

    Step 4– Add the reports into console

     

    Run the Crystal component within console.  Firstly, select the Set Default Reports Path action and navigate to your report share then click OK.  Next, 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 SQL login and password details.

     

    crystal4.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 – Create a reporting database login in SQL

     

    You may decide to provide a database login for reporting purposes only. However you can skip this step if you want to use your existing SQL database connection details for the reports.  In SQL Server Management Studio (called Enterprise Manager in previous versions of SQL), go into Security – Logins. Right-click and select New Login:

     

    crystal5.PNG

     

    Give the login a name (in our example this is sdreports), ensure it uses SQL authentication, set the password as required, review or change the password policy settings at this point, and ensure the correct database is selected under Default Database.

     

    NOTE: For ease of use and because we are giving only read-only access to the database you may want to consider giving this user a blank password.

     

    Lastly on the User Mapping tab, tick the correct database, leave the public role membership assigned but also give the db_datareader role:

     

     

    crystal6.PNG

    ADDENDUM 2 – Setting location on just one report

     

    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 from the menus Database – Set Datasource Location.  Click on Create New Connection and select OLEDB.  Then select the Microsoft OLE DB Provider for SQL Server.

     

    Select from the Server dropdown or type the server name as required to match your database server name. Then type in the database User ID and password.  Then click on the drop-down list next to Database.

     

     

    crystal7.PNG

    NOTE: If no databases or the required database is not listed here then this will indicate that the OLEDB connection is not able to connect to the server or that the User ID that you typed does not have access to the database you require.  Please start by double-checking that you have entered the correct connection details.

     

    When you have selected the Database 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.

     

     

    crystal8.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 3 – Subreports

    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 2 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 4 – Reports architecture overview diagram

     

    reports architecture.jpg