Crystal Reports Formula Conversion Samples
All the formulas below have been converted automatically, and some line breaks have been added to fit the width here.Overall our formula conversion accuracy is over 98% for both Crystal and VB formulas.
1. Formula with "IN" Keyword The "In keyword will be automatically converted to a group of "Or" subconditions. Formula Name: Group1 Formula Type: String Formula Syntax: crystalSyntax If {PM00200.VNDCLSID} in ["FIN_GOODS","FREIGHT_IN","PROD_OTHER","RAW_MATRL"] then "PRODUCT" else If {PM00200.USERDEF1} = "" then "1-OPEN" else {PM00200.USERDEF1} Conversion Result IIF (((Fields!VNDCLSID.Value = "FIN_GOODS") OR (Fields!VNDCLSID.Value = "FREIGHT_IN") OR (Fields!VNDCLSID.Value = "PROD_OTHER") OR (Fields!VNDCLSID.Value = "RAW_MATRL")), ("PRODUCT"), IIF (( Fields!USERDEF1.Value = "" ), ("1-OPEN") , (Fields!USERDEF1.Value)))
2. Formula to be converted to external custom code. If the is variable declaration, usually the function will be converted to external custom code. Formula Name: Name Formula Type: String Formula Syntax: crystalSyntax stringVar tw := " "; if not isnull({PATIENTS.Twin}) then tw := " " + {PATIENTS.Twin} + " "; {PATIENTS.LastName} + ", " + {PATIENTS.FirstName} + " " + {PATIENTS.Sex} + tw Public Function Name (ByVal dbField34 As String, ByVal dbField2 As String, ByVal dbField3 As String, ByVal dbField33 As String) As String Dim tw As String = " " If Not (IsNothing(dbField34)) Then tw = " " + dbField34 + " " End If Return dbField2 + ", " + dbField3 + " " + dbField33 + tw End Function Conversion Result: Call this function from page header: =Code.Name (First(Fields!Twin.Value, "DataSet1"), First(Fields!LastName.Value, "DataSet1"), First(Fields!FirstName.Value, "DataSet1"), First(Fields!Sex.Value, "DataSet1")) 3. IsNull will be converted to "IsNothing", ToText will most likely be converted to "Format" Formula Name: Abuse Formula Type: String Formula Syntax: crystalSyntax stringVar s1 := ""; stringVar s2 := ""; stringVar s3 := ""; stringVar s4 := ""; if not isnull({PREGLABOR.Smokes}) then s1 := "Smokes " + ToText({PREGLABOR.Smokes},2) + " ppd. "; if ({PREGLABOR.Alcohol}) then s2 := "Alcohol. "; if not isnull({PREGLABOR.StreetDrugs}) then s3 := "Drugs: " + ToText(Replace({PREGLABOR.StreetDrugs}, Chr(13) + Chr(10), ", ")) + ". "; if not isnull({PREGLABOR.DateUse}) then s4 := "Last used on " + ToText(CDate({PREGLABOR.DateUse})) + "."; stringVar s5 := s1 + s2 + s3 + s4; if (s5 = "") then "" else s5 Conversion Result: =Code.Abuse (Fields!Smokes.Value, Fields!Alcohol.Value, Fields!StreetDrugs.Value, Fields!DateUse.Value) Public Function Abuse (ByVal dbField60 As Decimal, ByVal dbField61 As Boolean, ByVal dbField62 As String, ByVal dbField63 As Date) As String Dim s1 As String = "" Dim s2 As String = "" Dim s3 As String = "" Dim s4 As String = "" If Not (IsNothing(dbField60)) Then s1 = "Smokes " + Format (dbField60, "#,##0.00") + " ppd. " End If If dbField61 Then s2 = "Alcohol. " End If If Not (IsNothing(dbField62)) Then s3 = "Drugs: " + Replace(dbField62, Chr(13) + Chr(10), ", ") + ". " End If If Not (IsNothing(dbField63)) Then s4 = "Last used on " + DateVale (dbField63) + "." End If Dim s5 As String = s1 + s2 + s3 + s4 If s5 = "" Then Return "" Else Return s5 End If End Function 4. A complicated IF then formaula, if the "IF" is simple, it will be converted to embedded IIF, otherwise external custom code. Formula Name: Apgar10 Formula Type: String Formula Syntax: crystalSyntax numberVar Apgar10 := 0; if not isnull({BIRTH.Apgar10}) then Apgar10 := Apgar10 + {BIRTH.Apgar10}; if not isnull({BIRTH.Apgar10_2}) then Apgar10 := Apgar10 + {BIRTH.Apgar10_2}; if not isnull({BIRTH.Apgar10_3}) then Apgar10 := Apgar10 + {BIRTH.Apgar10_3}; if not isnull({BIRTH.Apgar10_4}) then Apgar10 := Apgar10 + {BIRTH.Apgar10_4}; if not isnull({BIRTH.Apgar10_5}) then Apgar10 := Apgar10 + {BIRTH.Apgar10_5}; if not isnull({BIRTH.Apgar10}) or not isnull({BIRTH.Apgar10_2}) or not isnull({BIRTH.Apgar10_3}) or not isnull({BIRTH.Apgar10_4}) or not isnull({BIRTH.Apgar10_5}) then ToText(Apgar10,0); Conversion Result: =Code.Apgar10 (Fields!Apgar10.Value, Fields!Apgar10_2.Value, Fields!Apgar10_3.Value, Fields!Apgar10_4.Value, Fields!Apgar10_5.Value) Public Function Apgar10 (ByVal dbField22 As Integer, ByVal dbField78 As Integer, ByVal dbField82 As Integer, ByVal dbField86 As Integer, ByVal dbField90 As Integer) As String Dim Apgar10 As Decimal = 0 If Not (IsNothing(dbField22)) Then Apgar10 = Apgar10 + dbField22 End If If Not (IsNothing(dbField78)) Then Apgar10 = Apgar10 + dbField78 End If If Not (IsNothing(dbField82)) Then Apgar10 = Apgar10 + dbField82 End If If Not (IsNothing(dbField86)) Then Apgar10 = Apgar10 + dbField86 End If If Not (IsNothing(dbField90)) Then Apgar10 = Apgar10 + dbField90 End If If Not (IsNothing(dbField22)) Or Not (IsNothing(dbField78)) Or Not (IsNothing(dbField82)) Or Not (IsNothing(dbField86)) Or Not (IsNothing(dbField90)) Then Return Format (Apgar10, "#,##0") End If End Function 5. CStr and ToText conversion, by the way CStr is equivalent to ToText Formula Type: String Formula Syntax: crystalSyntax CStr ( {?@_RequestedDateTime}, {?@_ShortDateTimeFormat}) Conversion Result: Format (Parameters!v__RequestedDateTime.Value, Parameters!v__ShortDateTimeFormat.Value) 6. Another "If then else" formula Formula Name: Curr_Trx_Amnt Formula Type: Decimal Formula Syntax: crystalSyntax if {vw_rmall.rmdtypal} = 1 then {vw_rmall.curtrxam} else if {vw_rmall.rmdtypal} = 3 then {vw_rmall.curtrxam} else if {vw_rmall.rmdtypal} = 4 then {vw_rmall.curtrxam} else if {vw_rmall.rmdtypal} = 7 then -{vw_rmall.curtrxam} else if {vw_rmall.rmdtypal} = 8 then -{vw_rmall.curtrxam} else if {vw_rmall.rmdtypal} = 9 then -{vw_rmall.curtrxam} else 0.00 Conversion Result: =Code.Curr_Trx_Amnt (Fields!rmdtypal.Value, Fields!curtrxam.Value) Public Function Curr_Trx_Amnt (ByVal dbField2 As Integer, ByVal dbField6 As Decimal) As Decimal If dbField2 = 1 Then Return dbField6 ElseIf dbField2 = 3 Then Return dbField6 ElseIf dbField2 = 4 Then Return dbField6 ElseIf dbField2 = 7 Then Return (-dbField6) ElseIf dbField2 = 8 Then Return (-dbField6) ElseIf dbField2 = 9 Then Return (-dbField6) Else Return 0.00 End If End Function 7. Function called other functions. It will automatically generate all the function parameter and types. Formula Name: Normal_30_Days Formula Type: Decimal Formula Syntax: crystalSyntax if {RM00101.CUSTCLAS} <> "50 MID MONTH" and {?Date} - {vw_rmall.docdate} > 30 and {?Date} - {vw_rmall.docdate} <= 60 then {@Curr Trx Amnt} else if {RM00101.CUSTCLAS} = "50 MID MONTH" and {?Date} - {vw_rmall.docdate} > 45 and {?Date} - {vw_rmall.docdate} <= 75 then {@Curr Trx Amnt} else 0 Conversion Result: IIF ((Fields!CUSTCLAS.Value <> "50 MID MONTH" And DateDiff("d", Fields!docdate.Value, Parameters!Date.Value) > 30 And DateDiff("d", Fields!docdate.Value, Parameters!Date.Value) <= 60), (Code.Curr_Trx_Amnt (Fields!rmdtypal.Value, Fields!curtrxam.Value)), IIF (( Fields!CUSTCLAS.Value = "50 MID MONTH" And DateDiff("d", Fields!docdate.Value, Parameters!Date.Value) > 45 And DateDiff("d", Fields!docdate.Value, Parameters!Date.Value) <= 75 ), (Code.Curr_Trx_Amnt (Fields!rmdtypal.Value, Fields!curtrxam.Value)) , (0))) 8. Function with date formatting and parameters Formula Name: Date_Range Formula Type: String Formula Syntax: crystalSyntax totext(minimum({?Date Range}),"MM/dd/yyyy") + " to " + totext(maximum({?Date Range}),"MM/dd/yyyy") Conversion Result: Format (Parameters!Date_RangeStart.Value, "MM/dd/yyyy") + " to " + Format (Parameters!Date_RangeEndEnd.Value, "MM/dd/yyyy") 9. Function with ranged value. This is automatically converted to substr with VB "Mid" Formula Name: sort Formula Type: Decimal Formula Syntax: crystalSyntax if {Customer_Master.STATUS_23} = 'H' //credit hold then 4 else if {Customer_Master.SLSREP_23} = 'ZFOR' //International THEN 2 else if {Customer_Master.SLSREP_23} = 'ZFOA' //International then 2 else if {Customer_Master.SLSREP_23} = 'ZFEA' //International then 2 else if {Customer_Master.SLSREP_23} = 'ZFAA' //International then 2 else if {Customer_Master.SLSREP_23} = 'ZFOL' //International then 2 else if {SO_Detail.DUEQTY_28} > {Part_Master.ONHAND_01} then 3 else if {Customer_Master.CUSTID_23}[1 to 2] = 'CN' then 1 else 0 Conversion Result: =Code.sort (Fields!STATUS_23.Value, Fields!SLSREP_23.Value, Fields!DUEQTY_28.Value, Fields!ONHAND_01.Value, Fields!CUSTID_23.Value) Public Function sort (ByVal dbField12 As String, ByVal dbField15 As String, ByVal dbField14 As Decimal, ByVal dbField17 As Decimal, ByVal dbField18 As String) As Decimal If dbField12 = "H" Then Return 4 ElseIf dbField15 = "ZFOR" Then Return 2 ElseIf dbField15 = "ZFOA" Then Return 2 ElseIf dbField15 = "ZFEA" Then Return 2 ElseIf dbField15 = "ZFAA" Then Return 2 ElseIf dbField15 = "ZFOL" Then Return 2 ElseIf dbField14 > dbField17 Then Return 3 ElseIf Mid(dbField18, 1, 2) = "CN" Then Return 1 Else Return 0 End If End Function 10. Long IF and calling other function Formula Name: sortmsg Formula Type: String Formula Syntax: crystalSyntax if {@sort} = 0 then 'Domestic' else if {@sort} = 1 then 'Canadian' else if {@sort} = 2 then 'International' else if {@sort} = 4 then 'Credit Hold' else if {@sort} = 3 then 'Back Order' else 'Inknown' Conversion Result: =Code.sortmsg (Fields!STATUS_23.Value, Fields!SLSREP_23.Value, Fields!DUEQTY_28.Value, Fields!ONHAND_01.Value, Fields!CUSTID_23.Value) Public Function sortmsg (ByVal dbField12 As String, ByVal dbField15 As String, ByVal dbField14 As Decimal, ByVal dbField17 As Decimal, ByVal dbField18 As String) As String If sort (dbField12, dbField15, dbField14, dbField17, dbField18) = 0 Then Return "Domestic" ElseIf sort (dbField12, dbField15, dbField14, dbField17, dbField18) = 1 Then Return "Canadian" ElseIf sort (dbField12, dbField15, dbField14, dbField17, dbField18) = 2 Then Return "International" ElseIf sort (dbField12, dbField15, dbField14, dbField17, dbField18) = 4 Then Return "Credit Hold" ElseIf sort (dbField12, dbField15, dbField14, dbField17, dbField18) = 3 Then Return "Back Order" Else Return "Inknown" End If End Function 11. Function with summary, aggregation functions such as Sum cannot be used in external custom code, so they must be converted to embedded SSRS expression. In addition, the summary scope cannot use field name. Formula Name: SFY02_Dist_Avg Formula Type: Decimal Formula Syntax: crystalSyntax if Sum ({rtblLTCF_CAP.PAYMENT_AMOUNT_02}, {rtblLTCF_CAP.CLIENT_DISTRICT_CODE}) > 0 then Sum ({rtblLTCF_CAP.PAYMENT_AMOUNT_02}, {rtblLTCF_CAP.CLIENT_DISTRICT_CODE}) / Sum ({rtblLTCF_CAP.MEMBER_MONTHS_02}, {rtblLTCF_CAP.CLIENT_DISTRICT_CODE}) /12 Conversion Result IIF ((Sum(Cdbl(Fields!PAYMENT_AMOUNT_02.Value), "Group1") > 0), (((Sum(Cdbl(Fields!PAYMENT_AMOUNT_02.Value), "Group1")) / (Sum(Cdbl(Fields!MEMBER_MONTHS_02.Value), "Group1"))) / (12)), 0) 12. Complicated IF and fields from stored procedure Formula Name: RMCost Formula Type: Decimal Formula Syntax: crystalSyntax if {?TempCostOption} = "Actual" Then {AACostPlusDetails;1.Calc_ActualExtCost} Else if {?TempCostOption} = "Average" Then {AACostPlusDetails;1.Calc_ActualAveCost} Else if {?TempCostOption} = "January" Then {AACostPlusDetails;1.Calc_TempExtCostJan} Else if {?TempCostOption} = "February" Then {AACostPlusDetails;1.Calc_TempExtCostFeb} Else if {?TempCostOption} = "March" Then {AACostPlusDetails;1.Calc_TempExtCostMar} Else if {?TempCostOption} = "April" Then {AACostPlusDetails;1.Calc_TempExtCostApr} Else if {?TempCostOption} = "May" Then {AACostPlusDetails;1.Calc_TempExtCostMay} Else if {?TempCostOption} = "June" Then {AACostPlusDetails;1.Calc_TempExtCostJun} Else if {?TempCostOption} = "July" Then {AACostPlusDetails;1.Calc_TempExtCostJul} Else if {?TempCostOption} = "August" Then {AACostPlusDetails;1.Calc_TempExtCostAug} Else if {?TempCostOption} = "September" Then {AACostPlusDetails;1.Calc_TempExtCostSep} Else if {?TempCostOption} = "October" Then {AACostPlusDetails;1.Calc_TempExtCostOct} Else if {?TempCostOption} = "November" Then {AACostPlusDetails;1.Calc_TempExtCostNov} Else if {?TempCostOption} = "December" Then {AACostPlusDetails;1.Calc_TempExtCostDec} Else 0 Conversion Result: =Code.RMCost (Parameters!TempCostOption.Value, Fields!Calc_ActualExtCost.Value, Fields!Calc_ActualAveCost.Value, Fields!Calc_TempExtCostJan.Value, Fields!Calc_TempExtCostFeb.Value, Fields!Calc_TempExtCostMar.Value, Fields!Calc_TempExtCostApr.Value, Fields!Calc_TempExtCostMay.Value, Fields!Calc_TempExtCostJun.Value, Fields!Calc_TempExtCostJul.Value, Fields!Calc_TempExtCostAug.Value, Fields!Calc_TempExtCostSep.Value, Fields!Calc_TempExtCostOct.Value, Fields!Calc_TempExtCostNov.Value, Fields!Calc_TempExtCostDec.Value) Public Function RMCost (ByVal parameter5 As String, ByVal dbField47 As Decimal, ByVal dbField48 As Decimal, ByVal dbField7 As Decimal, ByVal dbField8 As Decimal, ByVal dbField9 As Decimal, ByVal dbField10 As Decimal, ByVal dbField11 As Decimal, ByVal dbField12 As Decimal, ByVal dbField13 As Decimal, ByVal dbField14 As Decimal, ByVal dbField15 As Decimal, ByVal dbField16 As Decimal, ByVal dbField17 As Decimal, ByVal dbField18 As Decimal) As Decimal If parameter5 = "Actual" Then Return dbField47 ElseIf parameter5 = "Average" Then Return dbField48 ElseIf parameter5 = "January" Then Return dbField7 ElseIf parameter5 = "February" Then Return dbField8 ElseIf parameter5 = "March" Then Return dbField9 ElseIf parameter5 = "April" Then Return dbField10 ElseIf parameter5 = "May" Then Return dbField11 ElseIf parameter5 = "June" Then Return dbField12 ElseIf parameter5 = "July" Then Return dbField13 ElseIf parameter5 = "August" Then Return dbField14 ElseIf parameter5 = "September" Then Return dbField15 ElseIf parameter5 = "October" Then Return dbField16 ElseIf parameter5 = "November" Then Return dbField17 ElseIf parameter5 = "December" Then Return dbField18 Else Return 0 End If End Function 13. Using color in formula, Crystal Color is integer while SSRS color is String type. We can convert color automatically. Formula Name: Back_Color Formula Type: Decimal Formula Syntax: crystalSyntax if {MList.Rev} = "0" or {MList.Rev} = "A" or {Pos.Rev} <> {MList.Rev} then crNoColor else color (226,226,226) Conversion Result: IIF ((Fields!Rev.Value = "0" Or Fields!Rev.Value = "A" Or Fields!Rev.Value <> Fields!Rev.Value), ("Transparent") , ("#E2E2E2")) 14. A function with complicated conditions and logic. This formula is fully converted. Formula Name: NewBalance Formula Type: Decimal Formula Syntax: crystalSyntax //check if item is stocked and if not do not compute new balance (should be 0) If {IMITMIDX_SQL.stocked_fg} = "Y" Then // computes running total of the quantity balance // transaction types that do not impact the balance if {IMINVTRX_SQL.doc_type} = "A" or {IMINVTRX_SQL.doc_type} = "B" or {IMINVTRX_SQL.doc_type} = "C" or {IMINVTRX_SQL.doc_type} = "G" or {IMINVTRX_SQL.doc_type} = "L" or {IMINVTRX_SQL.doc_type} = "O" or {IMINVTRX_SQL.doc_type} = "W" or {IMINVTRX_SQL.doc_type} = "H" or ({IMINVTRX_SQL.doc_type} = "T" AND {@F_FromLocation} = {@F_ToLocation}) then {IMINVTRX_SQL.old_quantity} else // Deleted Item or negative Qty Transactions (Subtract) if {IMINVTRX_SQL.doc_type} = "R" then ({IMINVTRX_SQL.old_quantity} + {@TrxQty}) else // Issue Transactions (Subtract) if {IMINVTRX_SQL.doc_type} = "I" then //if negative old_quantity then add if {IMINVTRX_SQL.source} = "R" and {IMINVTRX_SQL.old_quantity} < 0 then ({IMINVTRX_SQL.old_quantity} + ({@TrxQty} * -1)) else ({IMINVTRX_SQL.old_quantity} - ({@TrxQty} * -1)) else // Transfer Transactions (Add or Subtract) if {IMINVTRX_SQL.doc_type} = "T" then if {IMINVTRX_SQL.lev_no} = 0 then {IMINVTRX_SQL.old_quantity} + ({@TrxQty} * -1) else {IMINVTRX_SQL.old_quantity} + {@TrxQty} // all other transaction types except Transfers else {IMINVTRX_SQL.old_quantity} + {@TrxQty} Else 0; Conversion Result: =Code.NewBalance (Fields!stocked_fg.Value, Fields!doc_type.Value, Fields!old_quantity.Value, Fields!source.Value, Fields!lev_no.Value, Fields!quantity.Value) Public Function NewBalance (ByVal dbField23 As String, ByVal dbField2 As String, ByVal dbField6 As Decimal, ByVal dbField1 As String, ByVal dbField9 As Integer, ByVal dbField12 As Decimal) As Decimal If dbField23 = "Y" Then If dbField2 = "A" Or dbField2 = "B" Or dbField2 = "C" Or dbField2 = "G" Or dbField2 = "L" Or dbField2 = "O" Or dbField2 = "W" Or dbField2 = "H" Or dbField2 = "T" And F_FromLocation () = F_ToLocation () Then Return dbField6 ElseIf dbField2 = "R" Then Return dbField6 + TrxQty (dbField2, dbField12) ElseIf dbField2 = "I" Then If dbField1 = "R" And dbField6 < 0 Then Return dbField6 + (TrxQty (dbField2, dbField12)) * ((-1)) Else Return dbField6 - (TrxQty (dbField2, dbField12)) * ((-1)) End If ElseIf dbField2 = "T" Then If dbField9 = 0 Then Return dbField6 + (TrxQty (dbField2, dbField12)) * ((-1)) Else Return dbField6 + TrxQty (dbField2, dbField12) End If Else Return dbField6 + TrxQty (dbField2, dbField12) End If Else Return 0 End If End Function 15. Another Complicated formula, again it is fully converted by our advanced conversion. Formula Name: CODIGO_ORDEN Formula Type: String Formula Syntax: crystalSyntax if {ADM_EMPRESAS.PAIS_COD} <> "MEX" then {ORTR.TROR_COD}+" / "+ {ORTR.ORTR_COD}+" - "+ToText ({ORTR.ORTR_VERSION},0) else if {ORTR.TIOT_COD} = "3" then "PRO " + {ORTR.CLI_COD} + " - " + ToText({ORTR.MARC_COD},0) + " - " + {ORTR.TROR_COD} +" / " + {ORTR.ORTR_COD}+" - " +ToText ({ORTR.ORTR_VERSION},0) else if {ORTR.TIOT_COD} = "1" then "CRE " + {ORTR.CLI_COD} + " - " + ToText({ORTR.MARC_COD},0) + " - " + {ORTR.TROR_COD} + " / "+ {ORTR.ORTR_COD}+" - " +ToText ({ORTR.ORTR_VERSION},0) else if {ORTR.TIOT_COD} = "2" then "COT " + {ORTR.CLI_COD} + " - " + ToText({ORTR.MARC_COD},0) + " - " + {ORTR.TROR_COD} + " / "+ {ORTR.ORTR_COD} +" - "+ToText ({ORTR.ORTR_VERSION},0) else if {ORTR.TIOT_COD} = "4" then "MED " + {ORTR.CLI_COD} + " - " + ToText({ORTR.MARC_COD},0) + " - " + {ORTR.TROR_COD} +" / " + {ORTR.ORTR_COD}+" - " +ToText ({ORTR.ORTR_VERSION},0) Conversion Result: =Code.CODIGO_ORDEN (Fields!PAIS_COD.Value, Fields!TROR_COD.Value, Fields!ORTR_COD.Value, Fields!ORTR_VERSION.Value, Fields!TIOT_COD.Value, Fields!CLI_COD.Value, Fields!MARC_COD.Value) Public Function CODIGO_ORDEN (ByVal dbField31 As String, ByVal dbField0 As String, ByVal dbField1 As String, ByVal dbField2 As Decimal, ByVal dbField30 As String, ByVal dbField28 As String, ByVal dbField29 As Decimal) As String If dbField31 <> "MEX" Then Return dbField0 + " / " + dbField1 + " - " + Format (dbField2, "#,##0") ElseIf dbField30 = "3" Then Return "PRO " + dbField28 + " - " + Format (dbField29, "#,##0") + " - " + dbField0 + " / " + dbField1 + " - " + Format (dbField2, "#,##0") ElseIf dbField30 = "1" Then Return "CRE " + dbField28 + " - " + Format (dbField29, "#,##0") + " - " + dbField0 + " / " + dbField1 + " - " + Format (dbField2, "#,##0") ElseIf dbField30 = "2" Then Return "COT " + dbField28 + " - " + Format (dbField29, "#,##0") + " - " + dbField0 + " / " + dbField1 + " - " + Format (dbField2, "#,##0") Else Return "MED " + dbField28 + " - " + Format (dbField29, "#,##0") + " - " + dbField0 + " / " + dbField1 + " - " + Format (dbField2, "#,##0") End If End Function