Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


Crystal Reports - Dynamic Text Formatting

Crystal Reports - Formatting a Hyperlink in a Report

This article describes how to create a Crystal Report with some dynamic text formatting by using the Crystal Reports Editor. By creating a SQL Server table which includes URLs to some restaurant websites, we can use the URL field of the table to determine what the format of the text on the report will look like.

Create a SQL Server Table for Crystal Reports

In SQL Server, create a new table and insert some data into it.

CREATE TABLE tblRestaurants
( ID int identity(1,1) not null primary key clustered,
RestaurantName varchar(255) not null,
URL varchar(255) null,
pic varbinary(max) null )

insert into tblRestaurants
SELECT 'Shore Bird Restaurant',
'http://shorebirdwaikiki.com/', BulkColumn FROM Openrowset
(Bulk 'C:\Data\pics\sb.jpg', Single_Blob) as pic

insert into tblRestaurants SELECT 'House Without A Key',
'http://www.halekulani.com/dining/house_without_a_key/', BulkColumn FROM Openrowset
(Bulk 'C:\Data\pics\halekulani.jpg', Single_Blob) as pic

insert into tblRestaurants
SELECT 'Yard House',
'http://www.yardhouse.com/', BulkColumn FROM Openrowset
(Bulk 'C:\Data\pics\yardhouse.jpg', Single_Blob) as pic

insert into tblRestaurants
SELECT 'Kuhio Beach Grill',
null, BulkColumn FROM Openrowset
(Bulk 'C:\Data\Pics\kuhio.jpg', Single_Blob) as pic

insert into tblRestaurants
SELECT 'Hard Rock Cafe',
'http://www.hardrock.com/locations/cafes3/', BulkColumn FROM Openrowset
(Bulk 'C:\Data\Pics\hardrock.jpg', Single_Blob) as pic

insert into tblRestaurants Values
('Giovanni Pastrami','http://giovannipastrami.com/index2.html', null)

insert into tblRestaurants Values
('Seaside Bar & Grill', null, null)

select * from tblRestaurants

SQL Server View

Create the Crystal Report Project

In a Visual Studio project, right click in the solution explorer and click Add New Item. Select Crystal Report from the Templates and provide a Name.

Crystal Report Template

Use the Report Wizard to create the report.

Crystal Reports Start Up

In the Set Datasource Location click on OLE DB(ADO) and click on Make New Connection.

OLE DB Connection

Connect to the data provider, in this case SQL Server 10.0.

Data Provider

Select the Server, Database and Integrated Security and click Finish.

Connection Information

Click on the server name under OLE DB (ADO) and click on the Plus Signs to open up the Database and tables. Click on the Table Name and press the > button to Select the table for the report.

Table selection

Select the fields for the report by clicking on the >> button. Notice that the varbinary(max) field of pic is not listed in the available fields although it is present in the table. Click on the through the Next buttons to complete the report. Click Finish when done.

Field Selection

In the Field explorer, notice that the pic field is now listed.

Field Explorer

Drag and drop the fields on the report.

Fields and Report

Right click on the report to add a Line to the Page Header and then again in the Details section to separate the items in the list.

Insert Line

Run the report by clicking on the Main Report Preview. The items are listed as expected.

Unformatted Report

Code Dynamic Formatting in the Crystal Report

Return to Design View and right click on the Restaurant Name and select Format Object.

Crystal Reports Design

Click on the button next to the Underline effect (the X+2 button with a pencil).

Font Formatting

Change the Syntax from Crystal to Basic for the code to enter.

Syntax

Type the following code in the code area to show an underline if the URL field is not null and to not show an underline if the URL field is null. This effect will make it obvious to the user that the text is a hyperlink.

Dim bl as boolean

 if IsNull({tblRestaurants.URL}) = false Then
  bl = true
 else
  bl = false
 end if

formula = bl

Click Save and Close to Finish and then click the formula button next to the Font Color to add some code to it. The code will make the text appear blue like a hyperlink if the URL field is not null. Click Save and Close when finished.

dim strColor as number

 if (isNull({tblRestaurants.URL}) = false) Then
  strColor = crBlue
 else
  strColor = crBlack
 end if
formula = strColor

Click on the Hyperlink tab and select Hyperlink Type of "A Website on the Internet". Click on the formula button next to Website Address.

Hyperlink Formatting

Add code to the hyperlink to add it to the field only if the URL field is not null.

dim txt as string

 if (isnull({tblRestaurants.URL}) = true) Then
  txt = ""
 Else
  txt = {tblRestaurants.URL}
 End If
formula = txt

Click on Main Report Preview to view the report and notice how the text is now formatted for those Restaurants where the URL field was not null.

Main Report Preview