Convert Crystal Formulas to Reporting Services Expressions

• rpt2ssrs

  SSC Eights!

  Points: 897

  More actions

  September 1, 2010 at 9:42 am

  #225494

  After automatic conversion from Crystal Reports to Reporting Services with tools such as Crystal Reports Migration Services (http://www.crystalmigrater.com/Default.aspx), there are still lots of manual jobs to do. Crystal Formula is an area that usually conversion tool can not do well. Let us discuss how to convert Crystal formulas to Reporting Services Expressions.

  Crystal Reports supports two formula languages, one is VB (Modified VB syntax), the other is "Crystal Syntax". Convert the VB syntax to SSRS expressions is easier, so let us focus on how to convert Crystal syntax formula to SSRS expressions.

  Let us go by an example, here is a Crystal Syntax formula.

  Formula Name: Heading Calendar Period

  Formula Syntax: crystalSyntax

  Formula:

  // convert the To date from character to a date

  Datevar AdjDateTo;

  If Mid({?Ending Period},6,2) < "13" then

  AdjDateTo:= Date (val(Left({?Ending Period},4 )), // YYYY

  val(Mid({?Ending Period},6 ,2 )), // MM

  val(Right({?Ending Period},2 )) ) // DD

  Else

  AdjDateTo:= Date (val(Left({?Ending Period},4 )),12 ,31 );

  // Format the To date to "MMMM dd, yyyy" format

  StringVar CharDateTo;

  If Mid({?Ending Period},6,2)< "13" then

  CharDateTo := Uppercase(ToText(AdjDateTo,"MMMM dd, yyyy")) else

  CharDateTo := Uppercase(ToText(AdjDateTo,"MMMM dd, yyyy") + " Adjusted through Month " +

  Mid ({?Ending Period},6,2));

  // Merge From and To dates together

  //"From " + CharDateFrom + " To " + CharDateTo;

  "As Of " + CharDateTo;

  Here are the steps to do the conversion.

  Create VB Function Name based the Crystal Formula name. Crystal Formula name is not unique and may have some invalid characters.

  Pass database fields and parameter fields as function parameters.

  Convert Crystal comments to VB comments, replace “//” with “’”

  Convert Variable definitions to VB, for example “Datevar AdjDateTo;” will be replaced by “Dim AdjDateTo As Date”

  Assign statement, replace “:=” by “=”

  Remove end of statement “;”

  Crystal functions to VB functions, for example, “Uppercase” to “UCase”

  Handle condition, “If” closes by “End If”

  Continuous lines, in VB, the “_” must be added for continuous line.

  The formula above will be converted as follows.

  ' v is the parameter field "{?Ending Period}"

  Public Function DateConvert(ByVal v As String) As String

  ' convert the To date from character to a date

  Dim AdjDateTo As Date

  If Mid(v, 6, 2) < "13" Then

  AdjDateTo = New Date(Val(Left(v, 4)), _

  Val(Mid(v, 6, 2)), _

  Val(Right(v, 2))) ' DD

  Else

  AdjDateTo = New Date(Val(Left(v, 4)), 12, 31)

  End If

  ' Format the To date to "MMMM dd, yyyy" format

  Dim CharDateTo As String

  If Mid(v, 6, 2) < "13" Then

  CharDateTo = UCase(Format(AdjDateTo, "MMMM dd, yyyy"))

  Else

  CharDateTo = UCase(Format(AdjDateTo, "MMMM dd, yyyy") + " Adjusted through Month " + _

  Mid(v, 6, 2))

  End If

  ' Merge From and To dates together

  '"From " + CharDateFrom + " To " + CharDateTo

  DateConvert = "As Of " + CharDateTo

  End Function