How to Migrate Access Reports to SSRS (SQL Server Reporting Services)

[Rx_]

Does anyone else have any suggestions for converting Access Reports to SSRS?

After answering a question on this forum about moving Access Reports to SSRS, a little more research was done. It has been almost a decade since using the tools, so I thought it was worth revisiting.

It would appear that the basic reports will convert. My experience always started SSRS from scratch. When I used Access reports, custom VBAfunctions were often used to enhance them. Those probably won't convert as SSRS doesn't support VBA.

Here are some of the more interesting sites I found about the subject.

Those Aussie's are great! Read this over my lunch break.
Realizing this is a few years old, here is a 1.4 MB Word document with some useful step-by-step for converting MS Access Reports into SSRS.
It is also similar for Crystal developers.
http://www.ssw.com.au/ssw/standards...ts_To_Reporting_Services_Whitepaper_ver26.doc

A re-hash shorter version found here:
http://technet.microsoft.com/en-us/library/cc966391.aspx

A 2009 Magazine HowTo on the subject here:
http://sqlmag.com/reporting/importing-access-reports-sql-server-reporting-services

A 2013 On-line Tutorial with a free e-book download
http://www.phpring.com/how-to-import-reports-from-ms-access-to-ssrs-sql-server-reporting-services/

 

[Vassago]

SSRS does support VB! Many times, you can convert VBA scripts to VB to port over your functions. I've had to do that for a few of the reports I've converted from Access to SSRS.

Just recode the function to VB and place it in the Report Properties under Code, then you can call the function in expressions within the report.

 

[Rx_]

I am stunned!!
Would have never suspected that SQL (any part of it) would in any why support VB/VBA.
Thanks so much for that. I will want to know more if you have time.

So, would this indicate there is a sandbox for VB on the server?

So just to be clear, is this VB.NET (CLR) or classic Visual Basic?

 

[Vassago]

I am stunned!!
Would have never suspected that SQL (any part of it) would in any why support VB/VBA.
Thanks so much for that. I will want to know more if you have time.

So, would this indicate there is a sandbox for VB on the server?

So just to be clear, is this VB.NET (CLR) or classic Visual Basic?

I'm not sure. I took a modified bit of code that was written by our own raskew for time conversions and pushed it into SSRS and it works perfectly:

Function TimeConvert(ByVal pTimecount As Double, _
                       Optional ByVal pTimeType As String = "s", _
                       Optional ByVal pDisplay As String = "dhns", _
                       Optional ByVal pBooZero As Boolean = True) _
                       As String
'*******************************************
'Purpose:   Convert number of days, hours,
'           minutes or seconds (default)
'           to a string showing any combina-
'           tion of days, hours, minutes,
'           seconds
'Coded by:  raskew
'Modified by: Vassago
'Arguments: pTimeCount - count of time elements
'           (Opt) pTimeType - defaults to "s"
'           (Opt) pDisplay - defaults to "dhns"
'           (opt) pBooZero - show zero elements
'                 defaults to True
'Inputs:    1) ? TimeConvert(200000)
'           2) ? TimeConvert(200400, "s", "hns", False)
'           3) ? TimeConvert(200400, "s", "hns")

'Output:    1) 2 days 7 hours 33 minutes 20 seconds
'           2) 55 hours 40 minutes
'           3) 55 hours 40 minutes 0 seconds

'Modified:  1) 2:7:33:20
'           2) 55:40:00
'           3) 55:40:00

'*******************************************

Dim TimeHold     As Double
Dim i            As Integer
Dim n            As Integer
Dim intAmt       As Double
Dim intervalHold As String
Dim strHold      As String
Dim strSay       As String
Dim strType      As String

    intervalHold = "dhns"
    TimeHold = pTimecount
    
    'clarify options
    pBooZero = IIf(IsNothing(pBooZero), True, pBooZero)
    pTimeType = IIf(IsNothing(pTimeType), "s", pTimeType)
    
    'correct common input error (representing minutes as "m", rather than "n")
    pTimeType = IIf(pTimeType = "m", "n", pTimeType)
    
    TimeHold = TimeHold * Choose(InStr("snhd", pTimeType), 1, 60, 3600, 86400)
    pDisplay = IIf(IsNothing(pDisplay), "dhns", pDisplay)
    'correct common input error
    If InStr(pDisplay, "m") > 0 Then
       pDisplay = Left(pDisplay, InStr(pDisplay, "m") - 1) & "n" & Mid(pDisplay, (InStr(pDisplay, "m") + 1))
    End If
    
    'verify display sequence
    strHold = ""
    For n = 1 To 4
       If InStr(pDisplay, Mid(intervalHold, n, 1)) > 0 Then
          strHold = strHold & Mid(intervalHold, n, 1)
       End If
    Next n
    pDisplay = strHold
    
    ' ...let it rip!
    
    For i = 1 To Len(pDisplay)
        strType = Mid(pDisplay, i, 1)
        intAmt = Int(TimeHold / Choose(InStr("dhns", strType), 86400, 3600, 60, 1))
        If intAmt <> 0 Or (intAmt = 0 And pBooZero) Then
           'changed
           strSay = strSay & Format(intAmt, "00")
           'added
           strSay = strSay & IIf(i < Len(pDisplay), ":", "")
           'strSay = strSay & " " & Choose(InStr("dhns", strType), "day", "hour", "minute", "second")
           'strSay = strSay & IIf(intAmt <> 1, "s ", " ")
        End If
        TimeHold = TimeHold - (intAmt * Choose(InStr("dhns", strType), 86400, 3600, 60, 1))
    Next i
    
    TimeConvert = strSay
    
End Function

This made it very very easy to convert different time fields into a readable time format for the users of the report without needing to code something complicated in SSRS expressions. It's just:

=Code.TimeConvert((Fields!LoginSeconds.Value),"s","hns")

As an example.

Give it a try yourself with a VBA function you created. With the report opened, go to Report-> Report Properties. Choose the "Code" tab on the left. You just literally copy/paste the code in here. That's it. Your function should be callable with "Code" as shown above.

 

[Rx_]

This is amazing! Thanks so much. I am going to forward this to a MS SQL expert of mine. This is actually very exciting. Will be installing SSRS again soon just for this.

 

[Vassago]

This is amazing! Thanks so much. I am going to forward this to a MS SQL expert of mine. This is actually very exciting. Will be installing SSRS again soon just for this.

Make sure you test it out to suit your purposes. Some might not work as well as others. I hope it works for you!