Email Hyperlink Formula

[paulosinuk]

Hi,

I'm using CR XI SP2 and SQL Server 2000. I'm trying to code a mailto hyperlink on a text object. The hyperlink sits in the report footer and is a generic email address that the user can send emails to in the event that they have any enquiries about the report. As part of the email I want to send information on the report paramaters as I'm not likely to get this information if I asked the user.

Code as follows:
"mailto:Email@MyCompany.com" +

"?subject="+ ReportTitle +

"&body= &body=Enquiry Description" +
"%0D"+
"%0D"+
"%0D"+
"%0D"+
"The following section is for support purposes only. Please do not alter or delete this section." +
"Report Name: "+
"%0D"+
"Parameters Values of Report: "+
"%0D"+
" OrgLevel: "+ totext({?@OrgLevel},0) +
"%0D"+
" Location: " + {?@Location} +
"%0D"+
" Dept_or_Division_Switch: " + totext({?@Dept_or_Division_Switch}) +
"%0D"+
" Division: " +{?@Division} +
"%0D"+
" Time Period: " + {?@Period} +
"%0D"+
" Partner : " + {?@Partner} +

Code passes syntax check but if you leave the Email address in the hyperlinking information dialog blank nothing happens. If you enter an email address it uses that instead of the formula associated.

Has anyone experienced this or have a workaround?

 

[paulosinuk]

Discovered that a formula on the mailto hyperlink of any text object requires that any parameters used are evaluated for null. A mailto hyperlink cannot contain contain null params and connot use if-then-else statments during the generation to evaluate and replace null field valies.

Consequently I had to create local variables and evaluate for null in their assignment before concaternating the variables into the final mailto string.

Example of working code as follows:

//variable declaration
local stringvar UserLogin;
local stringvar OrgLevel;
local stringvar Location;
local stringvar DeptSwitch;
local stringvar Division;
local stringvar Period;
local stringvar Partner;
local stringvar Currency;
local stringvar PartnerResponsibility;
local stringvar RankLevel;
local stringvar Rank;
local stringvar TrendAnalysis;
local stringvar SortOrder;

//variable assignment
if currentceusername ="" then userlogin := "Report Designer" else userlogin := totext(currentceusername);
if isnull({?@OrgLevel}) then orglevel := "Null" else orglevel := totext({?@OrgLevel},0);
if isnull({?@Location}) then location := "Null" else location := totext({?@Location});
if isnull({?@Dept_or_Division_Switch}) then DeptSwitch := "Null" else DeptSwitch := totext({?@Dept_or_Division_Switch});
if isnull({?@Division}) then division := "Null" else division := totext({?@Division});
if isnull({?@Period}) then period := "Null" else period := totext({?@Period});
if isnull({?@Partner}) then partner := "Null" else partner := totext({?@Period});
if isnull({?@Currency}) then currency := "Null" else currency := totext({?@Period});
if isnull({?@PartnerResponsibility}) then PartnerResponsibility := "Null" else PartnerResponsibility := totext({?@PartnerResponsibility});
if isnull({?@RankLevel}) then RankLevel := "Null" else RankLevel := totext({?@RankLevel},0);
if isnull({?@Rank}) then Rank := "Null" else Rank := totext({?@Rank});
if isnull({?@TrendAnalysis}) then TrendAnalysis := "Null" else TrendAnalysis := totext({?@TrendAnalysis});
if isnull({?@SortOrder}) then SortOrder := "Null" else SortOrder := totext({?@SortOrder});


//finalised mailto statement
"mailto:Email@MyCompany.com" +

"?subject="+ totext(ReportTitle) + " - Report Enquiry" +
"&body= &body=Enquiry Description - (Please enter you enquiry below)" +
"%0D"+
"%0D"+
"%0D"+
"%0D"+
"%0D"+
"%0D"+
"%0D"+
"The following section is for support purposes only. Please do not alter or delete this section." +
"%0D"+
"----------------------------------------------------------------------------------------------------------------------------------------"+
"%0D"+
" Report Name : "+ totext(ReportTitle) +
"%0D"+
" Data Validation Date : " + totext(date({sp_Rpt2_Last;1.ReportValidationDate}),"dd MMM yyyy") +
"%0D"+
" Print Date : " + totext(printdate,"dd MMM yyyy") + " " + totext(printtime,"hh:mmtt", "AM", "PM") + " " + left(totext(PrintTimeZone), instr(PrintTimeZone," ")) +
"%0D"+
" User Login : " + totext(userlogin) +
"%0D"+
"%0D"+
"Parameters values of report: "+
"%0D"+
" @Org_Level : " + totext(OrgLevel) +
"%0D"+
" @Location : " + totext(Location) +
"%0D"+
" @Dept_or_Division_Switch : " + totext(DeptSwitch) +
"%0D"+
" @Division : " + totext(Division) +
"%0D"+
" @Period : " + totext(Period) +
"%0D"+
" @Partner : " + totext(Partner) +
"%0D"+
" @Currency : " + totext(Currency) +
"%0D"+
" @PartnerResponisibility : " + totext(PartnerResponsibility) +
"%0D"+
" @RankLevel : " + totext(RankLevel) +
"%0D"+
" @Rank : " + totext(Rank) +
"%0D"+
" @TrendAnalysis : " + totext(TrendAnalysis) +
"%0D"+
" @SortOrder : " + totext(SortOrder)