Views:

There are times when you need to set the datasource location on a report to your database.  This can occur if someone else writes a report for you or if you are updating an old report.

NOTE: There are two videos on this topic.  The first one is the easiest method but be aware, neither of the videos contain as many details, and the second video does NOT show how to create a connection for MET/TEAM version 2.2 or later.

Video: Update crystal report connection information via 'Verify Database'

Video: Update crystal report connection information

Follow these steps:

  1. Open the report from Crystal Reports
  2. Click the Database menu.
  3. Select Set Datasource Location from the list. 

  4. If you are selecting an existing connection, continue to Selecting the Datasource Location section
  5. If you are creating a new connection, use one of the following links:

Click to jump to connection for 1.0.x - 2.1.2

Click to jump to connection for 2.2.x

Click to jump to link for 2.3.0

Click to jump to link for 3.0.0

METTEAM 1.0.x - 2.1.2

Requirement: SQL Server Native Client 10.0

  1. Click the '+' OLE DB (ADO) from the Create New Connection

    mceclip1.png
  2. Select SQL Server Native Client 10 from the provider list:

  3. Click Next
  4. Enter or click the down arrow to locate your server name, enter your database name, click "Integrated Security" or use the username and password that gives you read access to the SQL database
    NOTE:  It is common for the lists to NOT populate and you have to manually type in the server and database information.


     
  5. Click Finish
  6. Continue to Selecting the Datasource Location section

METTEAM 2.2.x

  1. Select ODBC (RDO) from the Create New Connection list:

    mceclip3.png
  2. Select "Enter Connection String" and enter "DRIVER=SQL Server" as shown below.
  3. Press the Next button and on the ODBC (RDO) screen, select the server to connect to from the dropdown, click "Trusted Connection" or enter user credentials and select the METTEAM database from the Database dropdown.  
    NOTE:  It is common for the lists to NOT populate and you have to manually type in the server and database information. 

  4. Click Finish.
  5. Continue to Selecting the Datasource Location section

METTEAM 2.3.0

Requirement: SQL Server Native Client 11.0 x64  (be sure to select the x64 client)

  1. Click the '+' next to OLE DB (ADO) from the Create New Connection list:
    mceclip1.png
  2. Select SQL Server Native Client 11.0 from the Provider list:
    mceclip2.png
  3. Click Next
  4. Enter or click the down arrow to locate your server name, enter your database name, click "Integrated Security" or use the username and password that gives you read access to the SQL database
    NOTE:  It is common for the lists to NOT populate and you have to manually type in the server and database information.
  5. Click Finish
  6. Continue to Selecting the Datasource Location section

MET/TEAM 3.0.0 and later

Requirement: Microsoft OLE DB Driver for SQL Server (also located in the share "\\{MET/TEAM server name here}\METTEAM\Installers\CrystalReportsSupport\msoledbsql.msi)

  1. Click the '+' next to OLE DB (ADO) from the Create New Connection list:
    mceclip1.png
  2. Select Microsoft OLE DB Driver for SQL Server from the Provider list:
    mceclip0.png
  3. Click Next
  4. Enter or click the down arrow to locate your server name, enter your database name, click "Integrated Security" or use the username and password that gives you read access to the SQL database
    NOTE:  It is common for the lists to NOT populate and you have to manually type in the server and database information.
    mceclip1.png
  5. Click Finish
  6. Continue to Selecting the Datasource Location section

Selecting the Datasource Location

  1. Connecting will bring you back to the Set Datasource Location screen, but now you will see your database with a + sign next to it:

  2. Highlight the server name that exists in the report on the top half of the screen,
  3. Highlight your server name on the lower half of the screen: 

  4. Click Update.
  5. Click File>Save.

Test your report again.  If this doesn't fix the issue and the table, view or stored procedure still cannot be found, follow the steps below:

 

Expand the database icon (in my case metteam) in the lower half of the screen, by clicking the plus sign.

1.Expand dbo under the database name.

2.Expand tables, views or stored procedures, depending on what is used in the report that you want to update. In my case I need stored procedures and views:

3.Highlight (just by clicking on it) the first table in the top section of the screen under the server name (in my case Stds_Used).  

NOTE: Sometimes item names do NOT match the actual name of the object being used so it is always a recommended you follow the steps below whenever re-mapping report objects. 

In this example, I happen to know that Stds_Used maps to a table of a different name in MET/TEAM.  if you don't know what it maps to or what to verify the actual object name, simply expand the table and expand the properties to see:

Specifically pay attention to the "Table Name" property which lists the name of the 'real' object.  


Here are specific examples of this in the "Cal Cert w TSR.rpt" report...
In the main report, the "vw_CallSheets" object is actually "vw_CallSheetsWithAttCheck" so you need to select the "Replace with" object that matches the "Properties" value, not the displayed name.
?
And the other one with a similar issue...

If 'misnamed' objects are found, they can be renamed later, see the "Tip" section at the end of this article for instructions.

4. Continue to find the tables, views and stored procedures in your report that require mapping and follow the same process of highlighting it in the top section and in the bottom and clicking update

 

5.  Once you are finished, click to Close the "Set Datasource Location" page.

6.  Click File, Save.  Then test your report.

 

Here is a tip to fix the mismatched name issue:

When finished using the "Set Datasource Location" to correctly select the desired objects and saved your report.  You can now rename the objects to their correct or 'real' name.  To do this, click Database--> Database Expert..., then in the "Selected Tables" list on the right, right click on misnamed object and select rename and change the name to the actual object and click off the name to save.  In the case shown above "Stds_Used" would be renamed to "Ver_8_Stds_Vw".  When finished click OK to close the "Set Datasource Location" window.

Repeat the same steps for any other objects that need to be renamed.  Be aware that if the object to be renamed was in a sub report, you first need to open the subreport and when it's open, click Database--> Database Expert... When the "Database Expert" opens, repeat the steps above.  

For updating pre-version 2.2 reports for use in MET/TEAM version 2.2.x, be aware that the data type of all the primary keys, n...UID, fields has changed from a number to UniqueIdentifier.  Crystal now sees these fields as strings instead of numbers, so it does NOT 'auto map' them.  Instead you need to uncheck the Match type check box which will then allow the matching field to show up in the available fields list on the right.
?


When finished making changes, you want to save your report and retest to ensure everything is correct.  

Keywords: connection, report, Datasource