Archives

Opening SSRS Reports Automatically From Other Systems

Users typically love SQL Server Reporting Services Reports (SSRS), especially if they’re accustomed to more primitive tools such as Visual FoxPro. However, One of the biggest complaints I hear about SSRS is that users want to be able to launch it directly from their ERP, accounting, and other systems. A conventional way to do this is through URL (Universal Resource Locator) manipulation.

Depending on the capabilities of the source system, reports can be launch from a button, combo box, list box, etc. and display a specific recordset based upon parameters you specify. The best way I’ve found to launch SSRS from Made2Manage is through VBA, which is a free optional module that any M2M customer can use.

Steps to Create a URL Link Customization

  1. Create a basic SSRS report. For this demo, I’ve created a very simple report which pulls a set of records from the Sales Order Master (SOMast) Table, which will be launched from the Sales Screen (SO).
  2. Determine the actual URL link. I’m not going to go through each step of obtaining the proper URL because there’s an excellent step by step tutorial here. In my case, the URL will be:
  3. http://jeditemple/ReportServer/Pages/ReportViewer.aspx%2fBlog+Projects%2fSales+Details+for+URL+Link&rs:Command=Render
    Use the tutorial link instructions to find your URL, and the rest of this article is about how to integrate this into M2M.

  4. Properly install VBA on the systems which will use this link. There are instructions on M2MExpert as well as on the M2M Install Disks, but beware that they are confusing and hard to follow.
  5. Customize your M2VEvents.prj file in VBA. There is a downloadable class on M2M’s website for VBA programming.
  6. You manipulate your M2VEvents.prj file by selecting Tools, Macros, and then Macros or by clicking on the Transfer button and typing VBPROJ. If your VBA project file is empty, you will have to access it by opening a screen in M2M, in this case SO, right clicking on the screen mnemonic, and selecting components. Choose INIT and the VBA Editor will open and the SO module will be created for you.
  7. Right click on the Module folder and select Insert and Module.
  8. Click on the new module and change the name to “DefaultModule” in the properties box.
  9. Open the default module and paste the following code into it. This is a public function that is called by each module when creating the report URLs. Notice that I’ve ended it after the Blog Projects folder. My test SSRS Server only has the one folder on it at this time. The point is that this function returns the base portion of the URL for every report you intend to launch from M2M. The reason I’ve used the DefaultModule is if I decide to commission a new server, with a new name and such, I only need to change this base portion in one place, not in every module of the VBA project. Keep in mind that you will need to customize this code to match your server, folder structure, report names, etc.
  10. Public Function GetReportString() As String
        GetReportString = "http://jeditemple/ReportServer/Pages/ReportViewer.aspx?%2fBlog+Projects%2f"
    End Function
  11. Open the SO Module, press CTRL-A to select all of the text in it and delete that text. Then copy and paste in the following code into the SO module.
  12. Public ReportStr As String
    Public Function INIT() As Boolean
     
     
       With ThisForm.m2mPageFrame1.page1
         .addobject "lstReports", "m2mListBox"
         .lstreports.Top = 300
         .lstreports.Left = 500
         .lstreports.Width = 80
         .lstreports.Height = 39
         .lstreports.Visible = True
         .lstreports.AddItem "Sales"
        End With
     
       ReportStr = GetReportString()
     
        INIT = True
    End Function
     
    Public Function M2mpageframe1_Page1_lstReports_CLICK() As Boolean
     
    Dim Rpt As String, Param As String, lcSomastSono As String, Newsite As Object
     
    Set Newsite = CreateObject("InternetExplorer.application")
     
    Select Case ThisForm.m2mPageFrame1.page1.lstreports.Value
     
        Case "Sales"
                lcSomastSono = Trim(ThisForm.gettablevalue("somast", "fsono"))
                Rpt = "Sales+Details+for+URL+Link&rs:Command=Render"
                Param = "&SONO=" + lcSomastSono
                Newsite.Navigate (ReportStr & Rpt & Param)
                Newsite.Visible = True
        Case Else
                MsgBox ("Problem occured. Contact your administrator for help. ")
    End Select
        M2mpageframe1_Page1_lstReports_LICK = True
    End Function
  13. The INIT code draws a listbox on the SO form, assigns it’s properties, adds a selection to it called “Sales”. I chose a listbox because it’s one of the few controls on an M2M form that isn’t constrained by the Edit State of the screen. In other words, you can click on it even if you’re just viewing orders rather than editing them. I’d like to find a way to create something that looks like a hyperlink, but have not yet been successful.
  14. The second function, which fires when the listbox is clicked, creates the URL from it’s basic parts.
    • ReportStr as stated before is the base of your URL including your server name and base folder.
    • Rpt is the URL portion with the name of your report.
    • Param is the name of your parameter for this specific report.
    • lcSomastSono contains the record value, in this case the current Sales Order Number.
  15. If you’ve coded it properly, when you open the SO screen in M2M, you should see the following:
  16. If you’ve modified the code properly to connect to your SSRS server, then the report should open up to the corresponding sales order. In my case the URL created is shown below. Notice that I’ve bolded the custom parameter portion.
  17. http://jeditemple/ReportServer/Pages/ReportViewer.aspx?%2fBlog+Projects%2fSales+Details+for+URL+Link&rs:Command=Render&SONO=000064

  18. There are additional parameter options shown in that tutorial which allow you to suppress the report toolbar, the parameter portion, etc.

Although the code may look cumbersome, once you work through it, it’s very easy to understand and replicate. The VBA Modules are available for download as well.

Any questions or suggestions for improvement?

Related posts:

6 comments to Opening SSRS Reports Automatically From Other Systems

  • Scott

    Thanks for the info. I have been wanting to know how to do this for a long time.

  • Andrew

    Love the name of your server. Great post too.

  • Thank you for the information in details.

  • Nick L

    Thank you.

    Any way to do this from .NET version (7.03). I know that 7.5 will build this in anyway, but looking to try this while we wait for 7.5.

  • Mike C

    I know this is an old post but I would like to bump Nick L’s comment. We have used vba to open SSRS reports in classic but can’t figure out how to do this in the 7.03 .net client. I have made it as far as adding a command to the INV screen and assigning an onclick command using the built in screen customizer. I then created a vb project in VS to open a url and pass parameter. Works fine but I.E. Opens on the server. I cannot figure out how to get it to open on the client.

    Any help on getting the vb code to run on the client would be appreciated!

    Thanks!!!

  • 10. If you ticked in the faetureā€™s section to install Reporting Services, then here it will ask you whether to have the default configuration or do installation only and do Reporting Services configuration manually later. For this exercise, I am leaving as the default to do both installation and configuration. Read the following article if you want to learn how to do configuration manually: Installing and Configuring Reporting Services

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>