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
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.
Use the Report Wizard to create the report.
In the Set Datasource Location click on OLE DB(ADO) and click on Make New Connection.
Connect to the data provider, in this case SQL Server 10.0.
Select the Server, Database and Integrated Security and click Finish.
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.
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.
In the Field explorer, notice that the pic field is now listed.
Drag and drop the fields on the 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.
Run the report by clicking on the Main Report Preview. The items are listed as expected.
Code Dynamic Formatting in the Crystal Report
Return to Design View and right click on the Restaurant Name and select Format Object.
Click on the button next to the Underline effect (the X+2 button with a pencil).
Change the Syntax from Crystal to Basic for the code to enter.
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.
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.
