This guide explains how to create a database connection in Crystal Reports Developer Report Writer for hosted clients.
Double check the database you should be connecting to
Open the Admin tool
Click Database Manager
Note the SQL instance name.
Return to Crystal to confirm if this SQL instance is set up as an existing connection.
From the Access Gateway
Launch Crystal Reports.
To create a new report, select File Menu, New, Blank Report. A Database Exert window appears.
Click Create New Connection, OLE DB (ADO).
Select Microsoft OLE DB Provider for SQL as the Provider.
Enter the Server name. This can be found on the Admin tool in the Database Manager area.
Select Integrated Security.
Select the HR Database. This can be found on the Admin tool in the Database Manager area.
Click Finish. A new connection to the HR database has now been created.
When starting a new report click on Current Connections.
Expand the schemas within the connection, double-clicking tables to include them in the report.
A Crystal report may be returning data from the test system rather than live, this common issue is caused when a Crystal report has been built and tested with the Test environment.
The connection details associated with the Crystal report will need updating.
To ensure the Crystal report uses the correct databases, follow these steps:
Note the SQL instance and Database from the Database Manager area of the Admin tool.
In the Admin tool, navigate to the Document store.
Locate the report and right-click and select Edit.
Note: Ensure the Crystal report is unlocked. Clicking Edit will open the Crystal report in Crystal Designer.
In Crystal Designer, click Database.
Click Set Database Location.
Click Current Connections.
If the live database is already stored as a connection, go to step 13.
If the connection is not to the live database, this will need to be added, click Create New Connection.
Scroll to and select OLE DB (ADO).
Scroll to and select Microsoft OLE DB Provider for SQL Server.
Enter the details for the server and CLOUD credentials.
Once complete, highlight the new connection under OLE DB (ADO).
On the right, click Update.
Click Close. The report can now be saved and updated where necessary.
If your connection already exists and you are unable toUpdate a view or table from one Database to another, you may need to remove this from the report and re-add it by following these steps.
Remove and replace tables and views in Crystal
Click Database.
Click Database Expert.
Expand the SQL instance you require (new connection).
Expand the Database to show all schemas.
Find the equivalent tables and views in the left hand pane, using the existing Selected Tables section.
Highlight them and click the right arrow to include them.
A prompt will appear saying: The table 'Yourtablename' has already been added to this report with alias 'Yourtablename'. Do you really want to add another alias to this table?
Click Yes. - You'll see the table/view appear under the Selected tables section under the new connection's SQL instance, with an _1 appended to it.
You can now highlight the table/view from the old connection, clicking the left arrow to remove it.
Repeat step 6 - Add the correct table again from the left hand pane. - It will appear this time without the _1.
Remove the duplicate _1 table from the Selected table section.
This method ensures you do not remove a table before first replacing it with an appropriately named alternative, preventing you from losing any table/view.
You may need to re-build the report as all the tables and views have been replaced rather than updating the connection.
If this does not work for you, you can also try this alternate method.
Modify the SQL generated by using the Add Command functionality:
Click Database.
Click Show SQL Query.
Copy the SQL generated.
Click Database.
Click Database Expert.
Locate your Data Source and open the database folder
Select Add command (top of the list) and add to selected tables by clicking on the right arrow. This opens the Add Command To Report window
Paste the SQL generated;
Modify the SQL as required and click OK - replacing the SQL instance names in all places.
To rename the table, select and press F2
Go to the Links tab and check the relationships
Create the new report using the new table.
Change the Cloud account you are logging in with
Navigate to the Access Hosting website (RDWeb) and click onto SAP Crystal Reports 2016
click on the downloaded connection file and select More choices
if this is not available close the connection and then right click on the same connection file
Select Edit, this will reset the credentials
Select Close
Left Click the connection file and once again select More Choices
Select use a different account
Enter your alternative cloud credentials
click OK