Connecting Crystal Reports to HIFIS 4
In order to build a report for HIFIS 4 using Crystal Reports, there are four prerequisites that must exist for the computer being used:
- SAP Crystal Reports version 2011 or later must be installed
- Microsoft SQL Server Native Client 10.0 or 11.0 must be installed
- The OLE DB connection information must be known
- The computer must be able to connect to the database
1. SAP Crystal Reports version 2011 or later
Crystal Reports is paid software created by SAP that can be downloaded from http://www.crystalreports.com/. It’s typically around $500 for a license, but there are multiple pricing options, depending on how many computers will need to have Crystal Reports installed. Your organization might actually have some unused licenses, so that might be something to look into. Crystal Report version 2011 (14.0) or higher should work.
A free 30-day trial should be available, so for the purposes of training, you could consider installing a free trial on everyone’s computer and then ultimately buy a smaller number of licenses.
2. Microsoft SQL Server Native Client 10.0 or 11.0
HIFIS uses Microsoft SQL Server, which means that any computer that will be connecting directly to the server via Crystal Reports requires a SQL Server Native Client to assist with the connection.
You can download Native Client 11 from https://www.microsoft.com/en-us/download/details.aspx?id=50402. If that doesn’t work, you can also find it as part of the Microsoft SQL Server 2012 Feature Pack. Native Client 10.0 also works, which is part of the Microsoft SQL Server 2008 Feature Pack.
Once the file has downloaded, follow the prompts to install the driver.
Verify that SQL Server Native Client is installed correctly
To verify the installation of SQL Server Native Client, on your computer, open the Control Panel and search for Windows Tools. (Usually, this is found at Control Panel → System and Security → Windows Tools.) Look for ODBC Data Sources.
Windows Tools
← v ↑ é System and Security > Windows Tools
Organize *
- Internet Information Services (IIS) 6.0 Manager
- iSCSI Initiator
- ODBC Data Sources (32-bit)
- ODBC Data Sources (64-bit)
- Performance Monitor
- Power Automate
- Quick Assist
- Recovery Drive
- Registry Editor
36 items 1 item selected
Click on ODBC Data Sources for either 32-bit or 64-bit, depending on your computer's processor. Next, click on the Drivers tab. If correctly installed, SQL Server Native Client should appear under the list of drivers. You can click Cancel once you have confirmed the presence of the SQL Server Native Client 11.0 (or 10.0).
ODBC Data Source Administrator (64-bit)
User DSN System DSN Fle DSN Drivers Tracing Connection Pooling About
| Name | Version | Company | File | Date |
| ----------------------------- | ----------------- | --------------------- | ---------------- | ------ |
| ODBC Driver 13 for SQL Server | 2017,140.1000.169 | Microsoft Corporation | MSODBCSQL13.DLL | 2017-0 |
| ODBC Driver 17 for SQL Server | 2017,174.01.01 | Microsoft Corporation | MSODBCSQL17.DLL | 2019-0 |
| SQL Server Native Client 11.0 | 10,00,22000.01 | Microsoft Corporation | SOLSRV32.DLL | 2021-0 |
| | 2011,110,7462.05 | Microsoft Corporation | SOLNCLI11.DLL | 2018-0 |
| LRDA-HLC | 2011,110 5069 66 | Microsoft Corporation | SQLNCLIRDA11.DLL | 2017-0 |
An ODBC driver allows ODBC-enabled programs to get information from ODBC data sources. To install new drivers, use the driver's setup program.
OK Cancel Apply Help
3. Database Connection String
Crystal Reports connects to HIFIS 4 using the Microsoft SQL Server Native Client 10.0 or 11.0. However, in order to complete the connection, Crystal Reports needs to be told what database to connect to.
The following information is required in order to connect to the data source (HIFIS 4):
- The name of the server that hosts HIFIS
- The name of the database in which HIFIS is stored
- The user ID for a user that has the ability to access the server
- The password for the user ID
All of this information should be available from the database administrator (DBA) that installed HIFIS onto the server you are using to host HIFIS.
Write your community's connection information here. You will likely need to enter the password more than once, so you might want to keep it handy.
| Server | |
| -------- | - |
| Database | |
| User ID | |
| Password | |
4. Connect the Computer to the Database
The computer needs access to the database. Sometimes, this is easy to accomplish but other times there are firewalls protecting the server that may require some special configuration or a virtual private network (VPN).
Connect to HIFIS in Crystal Reports
STEP 1: Create a new report
Using the File menu, select New → Standard Report... This will open the Standard Report Creation Wizard.
STEP 2: Create a new connection
In the Available Data Sources field, double click on Create New Connection to expand the list. Then double click on OLE DB (ADO). Important: If you use another connection type, it may appear to work now but you will have trouble later. Be sure to use the OLE DB connection type!
Once you have connected to the database once, the connection will be remembered and be listed under My Connections. Future reports can be created simply by selecting the shortcut under My Connections.
Standard Report Creation Wizard
Data
Choose the data you want to report on
| Available Data Sources: | Selected Tables: |
| ------------------------------- | ----------------- |
| My Connections | TOAVs HIEIS |
| Create New Connection | Access/Excel(DAO) |
| ADO.NET (XML) | Database Files |
| Java Beans Connectivity | JDBC (JNDI) |
| ODBC (RDO) | OLE DB (ADO) |
| SAP BW MDX Query | |
| SAP Info sets | |
| SAP Operational Data Store | |
| SAP Table, Cluster, or Function | |
| Universes | |
| XML and Web Services | |
< Back Next > Finish Cancel Help
STEP 3: Select an OLE DB Provider
In the next window, scroll through the list of Providers until you find SQL Server Native Client 11.0 (10.0 will also work). Select it (either one will work), and then click on Next.
OLE DB (ADO)
OLE DB Provider
Select a provider from the list or select a data link file.
Provider:
Microsoft OLE DB Provider for ODBC Drivers
Microsoft OLE DB Provider for Oracle
Microsoft OLE DB Provider for Search
Microsoft OLE DB Provider for SQL Server
Microsoft OLE DB Simple Provider
MSDataShape
ft-Birectory Serv
SQL Server Native Client 11.0
Use Data Link File:
The options that appear in the Providers field are a list of drivers that have been installed on the computer currently being used. If neither option appears, then the SQL Server Native Client driver was not installed correctly. See Microsoft SQL Server Native Client 10.0 or 11.0.
STEP 4: Enter the Connection Information for HIFIS
This information should be available from your database administrator. Enter the Server name, the User ID and Password to login to the server, and the Database name. Then click Next.
OLE DB (ADO)
Connection Information
Provide necessary information to log on to the chosen data source.
| Server: | |
| -------------------- | - |
| User ID: | |
| Password: | |
| Database: | |
| Integrated Security: | |
< Back Next > Finish Cancel Help
The next screen contains advanced information about the OLE DB connection. Click on Finish.
STEP 5: You are now connected to the database
You should be returned to the Standard Report Creation Wizard, but under the Available Data Sources field, the database you just connected to will have a little icon next to it with a plug sticking out. This indicates that you are currently connected to that database. You can now proceed with creating your report.
Available Data Sources:
y Connections
Alis HIFIS
B
Create New Connection
