How to Write this Crystal Report formula in SSRS Expression?

Question

I am Having Problem to Convert this Crystal Report formula in SSRS Expression Can Anyone Help me?

Formula 1:

Dim fromExDay as String
Dim toExDay as String
Dim sYr as String
Dim sMonth as String
Dim sDay as String

fromExDay = ToText({wk_TORIO0460_a.HktrExchngDayFrom})
fromExDay = Replace (fromExDay, ",", "" )
fromExDay = Replace (fromExDay, ".", "" )

toExDay = ToText({wk_TORIO0460_a.HktrExchngDayTo})
toExDay = Replace (toExDay, ",", "" )
toExDay = Replace (toExDay, ".", "" )
if Len (Trim(fromExDay)) > 0 and Len (Trim(toExDay)) > 0 then
    sYr = Right(Left(fromExDay, 4),2)
    if sYr <> "99" then
        sYr = LEFT(CStr(CDbl(sYr) + 12),2)
    end if
    sMonth = Mid(fromExDay, 5, 2)
    sDay = Left(Right(fromExDay, 4),2)
    'fromExDay = sYr + sMonth + sDay    
    fromExDay = sYr + sMonth + sDay
    sYr = Right(Left(toExDay, 4),2)
    if sYr <> "99" then
        sYr = LEFT(CStr(CDbl(sYr) + 12),2)
    end if
    sMonth = Mid(toExDay, 5, 2)
    sDay = Left(Right(toExDay, 4),2)
    toExDay = sYr + sMonth + sDay    
    'toExDay = Right(fromExDay, 2)
    Formula = fromExDay + " ～ " + toExDay    
Else
    Formula = ""
End If

Value of ExchangeFrom and ExchangeTO is coming from Database . ExchangeFrom value = 20031031 ExchangeTo value = 200 Is There in Database

score 0 · Accepted Answer · answered Mar 14 '19 at 17:36

Is the return value supposed to be

151010 ~ 1220

There actually weren't many changes needed to convert it to an SSRS VB function. In SSRS, the function doesn't work directly with the field so you need to pass them to the function as parameters. Most of the rest of the VB in your old function should work the same in SSRS - I just removed the ToText functions that aren't in SSRS.

When you call the function from your text box, you pass the fields.

=code.Formula1(Fields!HktrExchngDayFrom.Value, Fields!HktrExchngDayTo.Value)

And here's the function:

Public Function Formula1(ByVal fromExDay as String, ByVal toExDay as String) as String

Dim sYr as String
Dim sMonth as String
Dim sDay as String

fromExDay = Replace (fromExDay, ",", "" )
fromExDay = Replace (fromExDay, ".", "" )

toExDay = Replace (toExDay, ",", "" )
toExDay = Replace (toExDay, ".", "" )

if Len (Trim(fromExDay)) > 0 and Len (Trim(toExDay)) > 0 then
    sYr = Right(Left(fromExDay, 4),2)
    if sYr <> "99" then
        sYr = LEFT(CStr(CDbl(sYr) + 12), 2)
    end if
    sMonth = Mid(fromExDay, 5, 2)
    sDay = Left(Right(fromExDay, 4), 2) 
    fromExDay = sYr + sMonth + sDay
    sYr = Right(Left(toExDay, 4), 2)
    if sYr <> "99" then
        sYr = LEFT(CStr(CDbl(sYr) + 12), 2)
    end if
    sMonth = Mid(toExDay, 5, 2)
    sDay = Left(Right(toExDay, 4), 2)
    toExDay = sYr + sMonth + sDay 
    Formula1 = fromExDay + " ~ " + toExDay    
Else
    Formula1 = ""
End If

End Function

I think the sDay calculations are incorrect.

sDay = Left(Right(fromExDay, 4),2)

Seems to be getting the month again. It should probably be

sDay = Right(fromExDay, 2)

or, if it can be a longer string use MID:

sDay = Mid(fromExDay, 7, 2)

Which changes the result to:

151031 ~ 12

Your welcome. If it's correct, you should mark it as the answer so others trying to convert Crystal Reports might be able to use this as an answer for similar issues. — Hannover Fist, Mar 15 '19 at 20:12
,Thanks sir , But I have Done this using Expression not Exact Solution you given me . — Rahul Shukla, Mar 18 '19 at 08:23

score 0 · Answer 2 · answered Mar 18 '19 at 08:50

For making this Formula I Taken Two Textbox in Active Report page and I divided this formula Into two Parts.

Textbox1:

=iif(Right(Left( Fields!ExchngDayFrom.Value , 4),2)  <> 99 ,LEFT(CStr(CDbl((Right(Left( Fields!ExchngDayFrom.Value , 4),2) ) + 12),2) + Mid( Fields!ExchngDayFrom.Value , 5, 2) + Left(Right( Fields!ExchngDayFrom.Value , 2),2 ) ," ")

Assuming Value of ExchangeDayfrom is : 20031031 Output is 151031

TextBox2:

="～　" & iif(Right(Left( Fields!ExchngDayTo.Value , 4),2) <> 99 ,LEFT(CStr(CDbl((Right(Left( Fields!ExchngDayTo.Value , 4),2)) + 12),2)  + Mid( Fields!ExchngDayTo.Value , 5, 2) +  Left(Right( Fields!ExchngDayTo.Value , 2),2 ) , Right(Left( Fields!ExchngDayTo.Value , 4),2) + Mid( Fields!ExchngDayTo.Value , 5, 2) +  Left(Right( Fields!ExchngDayTo.Value , 2),2 )

Assuming Value of ExchangeDayTo is : 99999999 Output is ～ 999999

This is How I Solve my Problem.Big thanks to @Hannover Fist sir thanks Your valuable Solution and Yes Your Solution is Right it's also worked Perfectly

How to Write this Crystal Report formula in SSRS Expression?

2 Answers2