Contributed by: Phil McIntosh of Friendly Systems
Crystal Reports can be used to write reports using xTuple data. The first step is to downlaod and install the ODBC driver for PostgreSQL, which can be found at https://www.postgresql.org/ftp/odbc/versions/. The .msi version is preferred. Also, Crystal Reports can only use 32-bit ODBC, not 64-bit, so be sure to download psqlodbc_x86.msi, NOT psqlodbc_x64.msi. Scroll down the page until you find the version that matches your version of PostgreSQL.
Note that the ODBC driver must be installed on each machine that will be running Crystal Reports. The Crystal Reports Designer is only required to create and edit reports. There are a number of programs available, such as Logicity (free and paid versions) and VisualCut, which can be used to allow users to run the reports that others have created, and some of the paid versions also allow you to do things like schedule reports and email them as PDFs or spreadsheets.
Once you have downloaded and installed the PostgreSQL ODBC driver, the next step is to create a data source name (DSN) for each database (company) you want to run reports on. Open up the 32-bit ODBC Data Source Administrator. If you are on a 32-bit OS, you can get to it from the control panel. On 64-bit Windows it can be a little tricky, as the 64-bit program is still called odbcad32.exe, so you need to find the one in C:\Windows\SysWOW64\, which is where the 32-bit versions of programs are kept.
Go to the Systems DSN tab (you will need to have admin rights) and click the ADD button.
Then select the PostgreSQL Unicode (NOT ANSI) driver and click FINISH.
Next you need to configure the DSN, following these steps:
- Give it a name and optionally a description.
- Fill in the database, server, port, SSL mode, and user name and password. The example below uses the admin user, but that is NOT the best thing to do. Instead create a user that has read-only access to xTuple.
- Click TEST to make sure that what you entered is valid.
- If the test succeeds, click SAVE.
If you have multiple companies in xTuple, you will need to create a separate DSN for each one.
Once the DSN is created, you can access xTuple data from Crystal Reports. From the Report Creation wizard, under "Create new connection," choose ODBC (RDO).
In the data source selection screen, choose one of your PostgreSQL DSNs created in the previous step. Then proceed to the selected tables and fields you want to generate reports from.
Here's a screenshot of a quick-and-dirty contact list generated out of Crystal Reports connected to an xTuple demo database: