Today’s post is going to be all about helping system administrators with the web client.  Previously, I have talked about getting your DynaChange Business Rules web client ready.  This installment is going to focus on Crystal forms and reports in the Prophet 21 software.

Out of the box, there is not really anything you need to do if you have not done a bunch of customizing.  However, this is the real world.  Administrators constantly mak changes to forms, add reports, and do everything else that come along with tying to keep up with the pace of business.

Direct Database Connections in Prophet 21 Software

The textbook method of customizing a Crystal form is to work through the Prophet 21 software custom datastreams feature.  You must carefully construct your changes to the data made available to the form.  Then you have to make changes to the XSD file that acts as the form’s data source.  Only then can you go in and make changes to the form layout.  If you have followed this process to the letter, your forms should pretty much be web client ready.

However, this is not the only way to add data to a form.  In some cases, system administrators will opt to bypass the datastream and add in a direct connection to the Prophet 21 database.  This is a quick and convenient way to add sub-reports or other major sections of data.  You can avoid spending hours tinkering with datastreams and XSD files this way.  Using a direct to database connection with integrated Windows authentication is definitely convenient, but in the web client, it simply will not work.

Windows Authentication Errors in Crystal

You will know that you are having a problem with a Crystal form customization using a direct database connection when you see something like the below:

The root cause of the problem is the use of Windows (Integrated) authentication in the report or form.  Fundamentally, the Poephet 21 software Middleware server does not work with Windows Authentication.  The reason is that it is not possible for the server to impersonate the user through IIS and back to the database.  The middleware executes as a local system account on it’s local machine, which is not going to translate well back to the SQL Server.

The Fix

The easy fix for this problem is to get Windows authentication out of your form.  The problem is, if you try to do it through a GUI based connection to the database, you will constantly be prompted for a password at runtime, if it even works at all.  However, there is a workaround.  The way to beat this problem is to change your direct database connection into an ODBC connection, using a hard coded connection string to the SQL database.

Open your Crystal Form for editing, and then open up the Set Datasource Location dialog from the Database top menu.  You will want to expand the folder to create a new connection as shown below.  Then click to expand the ODBC (RDO) connection type.

After clicking this selection, you will be prompted with another dialog box to create the connection, shown below.  Select the Connection String radio button, and then enter a SQL Server connection string, with a hard coded user name and password.  The connection string follows the following format:

Driver=SQL Server;Server=MyServerName;Database=P21;uid=MyUserId;pwd=MyPassword

I recommend creating a SQL user account for executing these reports. This is just like you would for executing business rules with a hard coded SQL connection.  Once this is done, click Finish.  Use the Set Datasource Location dialog to update your direct to database connection to the ODBC connection you just created.   After that, delete the old direct to database connection from the report, and you should be good to go.

Prophet 21 Software Web Client Forms – Short Version

To get around the problem of Crystal forms & reports login problems when using the web client in Prophet 21 software, change your direct to database connections that use Windows integrated authentication as follows:

1. Open the Set Datasource Location dialog from the Database menu in Crystal
2. Create a new ODBC (RDO) connection
3. Select the Connection String option and enter your SQL Server connection string with a hard coded username and password.
4. Update your direct database connection to the new ODBC connection you just created.
5. Delete the old direct to database connection from the form

Hopefully, this guide will give you the workarounds you need to get up and running with Crystal on the web client with the minimum amount of pain and suffering.

Read more P21 articles in our Prophet 21 Knowledge Base.