Converting Custom Crystal Reports to Work in the Cloud

Is there anything I need to do in order to prepare my Crystal reports for the cloud?

Author:  Eric Vasbinder 

IMPORTANT

When Editing Crystal Reports in the Cloud, please ensure that you have a pure SQL authentication account to use with Crystal Reports.  Here is a Vista Cloud FAQ article that will provide more details on this topic:

  I need a dedicated SQL account for my integration to Vista in your cloud.  How do I set that up? 

Overview

Viewpoint's Vista Cloud is quite compatible with multiple types of Crystal reports.  However, there are a few specific report settings that need to be changed in order to ensure that custom reports will work properly in our cloud:

Update Old Data Source Names (DSNs) to Point to Cloud Vista Server

1. CRITICAL:  Ensure your data connection strings that were pointing to your old, on-premise server are now pointing to your new Vista server in our cloud.  These reports will fail to run if they are left pointing to the old server.

   1. This FAQ Article and Video will walk you through the steps to update the Data Source Names (DSN) as well as other differences with on-premise Vista:  https://player.vimeo.com/video/471384022 

2. NOTE:  Viewpoint now has a tool, a PowerShell script, that can update both ADO/RDO and DSNs across all reports simultaneously for you.  We recommend using this script first, then reviewing any reports that the script was not able to update.

Convert All Connections from ADO (OLE DB) to RDO (ODBC)

Crystal Reports need to use the RDO connection option instead of ADO.  This can be configured in Crystal Reports Designer 2016.  Here are the steps to update this:

1. 1. Open your report in Crystal Reports designer.

   2. Go to the Database menu and select Set Datasource Location.

   3. In the Set Datasource Location dialog, you will see two sections: Current Data Source and Replace With.

   4. Under Current Data Source, expand the current data source by clicking the ‘+’ plus sign3. Select the object you want to replace.

   5. Under Replace With, click on Create New Connection and scroll down to ODBC (RDO).

   6. You will need to set up a new ODBC connection. This will involve selecting a driver, and providing the necessary connection information such as server name, database name, and authentication details.

   7. Once you’ve set up your new ODBC connection, select it in the Replace With section.

   8. Click the Update button to replace the old data connection with the new data connection.

Please note that these steps may vary slightly depending on your version of Crystal Reports and your specific database setup.

NOTE:  Viewpoint now has a tool, a PowerShell script, that can update both ADO/RDO and DSNs across all reports simultaneously for you.  We recommend using this script first, then reviewing any reports that the script was not able to update.

Integrated Security / Trusted Connection Should be OFF

A specific property in ODBC-connected (RDO) Crystal reports exists called, "Trusted Connection".  When you consider ADO (OLE DB) connected reports, the option is called "Integrated Security".   A Trusted Connection setting is designed to save time for end users to allow them to connect to the database using the workstation credentials of the user currently logged in and running Crystal Reports.  Please note that this setting can be encountered frequently in environments where users are logging in to their local machines using Microsoft Windows Active Directory authentication.

To edit this setting, please open the Crystal Report in Crystal Reports Builder and then edit the Data Source Name (DSN) to turn the property "Trusted Connection" to OFF.  If your reports are still in ADO, please update the "Integrated Security" setting to be OFF, prior to changing the report to RDO (ODBC), as per the above instructions.

Enable Use of VPNarrow Fonts Instead of Arial Narrow

Update All Custom Reports to Use VPNarrow

1. Any custom Crystal Reports need to be updated to use the VPNarrow font instead of Arial Narrow, as Arial Narrow is not supported in our cloud.

   1. NOTE:  Here is a link on how to update custom reports to point to VPNarrow instead of Arial Narrow.

   2. NOTE:  Our engineering team can also run a Font Updater tool that will automatically update your custom Crystal Reports to reference the VPNarrow font family instead of Arial Narrow.  Please reach out to your cloud transformation team for more details on running this tool.

Install VP Narrow Fonts on All Local Workstations Using Vista

2. Verify the VP Narrow Font library is installed on each local workstation that needs to use Vista's rich client

   1. Download here

   2. NOTE:  Vista's client installer should install this automatically upon Vista install.  However, you may check to see if this font is installed by looking at the font list in Microsoft Word, Wordpad, or the font library itself in Windows.

Custom MICR Fonts MUST be Sent to Viewpoint and Installed on the Vista Server

1. If you use a custom check report that has a custom MICR font, you must send a copy of that MICR font to Viewpoint for our team to install on the app server (or Crystal Reports Load Balancing nodes if in use), so that reports can be generated properly.  In addition, please make certain that your workstations that print these reports have the same MICR font installed.

   1. Please note that for Enterprise Architecture customers, we will need to install the custom MICR font on EACH Load Balanced Crystal Reports node.

IMPORTANT:  There are licensing and cost considerations for customers who want to use commercial MICR fonts.  Please see this FAQ for more details on using custom MICR fonts and recommendations on alternatives:   Custom MICR Fonts 

Update Reports to Use Crystal Reports 2016 Format or Higher

1. Update Crystal Reports to use Crystal Report 2016 format instead of the older 2008, 2013, etc. formats.  To do this, please open the reports in Crystal Reports Designer 2016 or 2020 and then save them as 2016 format.

Ensure Formatting and Layout Are Correct

From time to time Viewpoint has encountered customers with unique, custom reports that, when moving to our VRL based cloud, can be formatted incorrectly.  Some of the symptoms observed can be items such as overlapping columns, text being truncated, and more. 

In most cases these issues are corrected by ensuring the proper fonts are installed on client workstations as well as updating the format for the Crystal Report to be Crystal Reports 2016 or higher.  However, in some cases a more detailed update to the custom report is necessary.

Forcing Landscape Mode

Sometimes, reports that should be viewed and printed in landscape mode in VRL are opened in portrait mode.  To force landscape mode the following steps should be attempted:

1. Open a copy of the report file in Crystal Reports 2016 (this is the editing and report building tool from SAP).

2. Open File -> Page Setup on the tool menu.

3. Review the highlighted items shown in the screenshot below:

   • "No Printer" Selection

   • Paper Size Dropdown

   • Orientation

   • "Adjust Automatically" under Margins section

In many cases, the page set up options for landscape custom Crystal Reports are set up to look like the below screenshot.  This is incorrect, especially for VRL.

To ensure proper printing and viewing of Landscape reports using VRL, please update the Crystal report to use the following settings:

1. Uncheck the No Printer and Adjust Automatically checkboxes.

2. Change format from Portrait to Landscape.

3. Check the Dissociate Formatting Page Size and Printer Paper Size checkbox.

Here are the final steps to complete this process:

1. Once these steps are complete, please run and save the report in Crystal Reports 2016.

2. Upload the newly saved report into Vista, overwriting the old report.

3. Test the report within Vista itself, using VRL.

Update Reports to Use Can Grow

This is for report fields that you wish to have word wrap, rather than be cut off at the column line.  This is done by editing the field properties for each overlapping field to check the checkmark known as "Can Grow."  This option allows you to specify that "word wrapping" is ok, allowing for fields to grow to new lines, rather than being cut off.  This option must be selected for each field where you want to have the report word wrap to the next line, rather than be truncated.

Specifically, we can often fix this issue by performing the following steps:

1. Open Crystal Reports Builder and load the report in question.

2. Open the Format Editor by right clicking on the field and selecting the Format Field option.

3. On the Common tab, check the Can Grow option

4. (Optional) you may need to set a Maximum number of lines to enforce this setting (e.g. to 1 if you wish to truncate the lines at 1).

   1. NOTE:  this will truncate the text if the max number of lines is reached

5. NOTE:  We also apply a registry fix to your app server / Crystal Nodes (see next section) to allow for fields with data too long to fit to be truncated, rather than overlapping another field to the right.  The "Can Grow" option allows for the fields to word wrap rather than be truncated once the registry option is in place.

Registry Key Fixes for Wrapping and Truncation

The following registry key fixes are usually applied on the application server (CODE-APP) and the Crystal Report node in an Enterprise Architecture environment.  For a standard environment, it will be installed on the D1 main Vista server.

Registry Key Fix to Truncate Instead of Overlapping Fields:

• Go To:  [HKEY_LOCAL_MACHINE\SOFTWARE\SAP BusinessObjects\Crystal Reports for .NET Framework 4.0\Crystal Reports\SavedData]

• Add new key:  "TruncateClippedFieldStrings"="Yes"

Registry Key Fix to Force Word Wrapping Instead of Truncation or Overlapping Fields:

• Go To:  [HKEY_LOCAL_MACHINE\SOFTWARE\SAP BusinessObjects\Crystal Reports for .NET Framework 4.0\Crystal Reports\SavedData]

• Add new 32 bit DWord Key:  TruncatedAdjustment

  • Set the Value to "1"

Change Horizontal Alignment to "Justified"

When, after updating the field to have the setting "Can Grow" turned on, you still see overlapping fields in the previewer, you should update the horizontal alignment to be something other than "Default".  Ideally, we recommend "Justified".  Once you have done so, the report should now properly activate "Can Grow" and the text should word wrap to the next line.

This setting forces the Crystal viewer to use the word wrapping capability of "Can Grow".

Ensure Parameters Specified in RP Report Titles Match Those in the Crystal Query Itself

1. Please ensure that all parameters specified in RP Report Titles are also in the Crystal .RPT file and visa versa.

NOTE:  If this restriction is not adhered to, you will receive Crystal Reports generation errors.

Disable Displaying of Parameters in the Previewer (a.k.a. View Panel)

Each Parameter in a Crystal Report has a setting called, "Show on (View) Panel".  Please ensure this setting is set to "Do NOT Show".   If you do not specify this setting, you may see Crystal Reports errors, such as "Script Error... lowerboundtype".

To update these to NOT show on the resulting report and thus not cause the error, please use the following steps:

1.  Open the report in Crystal Reports Builder.  For information on downloading and uploading Crystal Reports over VRL, please see the following cloud FAQ:  How do I upload Custom Crystal Reports to Vista in the cloud?  (VRL Method) 

2. Enable the field explorer in Crystal Reports. This usually appears as a pane on the right side.

3. In field explorer, expand the parameter fields subfolder

4.  Review the first line in the Value Options box, called "Show on (Viewer) Panel".

5.  Please change it from Ready Only or Editable to "Do Not Show".

6.  Repeat these steps 3-5 for each parameter field on the report.

This is must be done for EVERY parameter on EVERY custom report

Possibly Increase Performance with "Force Local Render" - RP Report Titles

In some cases, with certain types of reports, generating those Crystal Reports over VRL can take a longer period of time than desired.  In some cases this is due to the need to iteratively request printer configuration settings, while the report is being rendered, from the local workstation.  To increase the performance of Crystal reports generation, it can sometimes be helpful to only send the results of the queries and the report metadata down to the local workstation, without any report layout and rendering information; this information requires the availability of a local printer driver to generate.

Fortunately, there is a workaround that can sometimes assist if you encounter a situation where generating Crystal reports over VRL is excessively slow.  Specifically, you may attempt to work around the issue of print job rendering taking a long time by utilizing an option that will move the report rendering stage of the report generation process to the local client, instead of occurring on the app server.    In other words, with this option on, Vista will run the report queries on the server, but then send those results down to the client where the local Crystal Libraries render and print the report.   Please note that this workaround can cause issues with drilldown reports, so be aware of that when selecting this option.

The upside is you get the report and data with none of the markup or the need to iteratively go back and forth to the client for printer settings whilst rendering the report; this approach can be more efficient.

To try out this workaround, please follow the subsequent series of steps:

1.  Open Vista and then open "RP Report Titles" module.

2.  Select the Report that you wish to try this out on.

3.  Activate the option, "Force Local Render".

4.  Save the settings.

Upload the Reports as You Make Changes

Once all of the above is done, you'll need to upload the reports to Vista.  See this link for instructions on how to upload reports to the Vista report library through VRL.