One of the new features which we’re supporting in the BizTalk Adapter Pack V2 is the ability to use the SAP ADO.NET Provider from SSRS. In this post, I’ll briefly outline the steps you need to perform in order to get this to work.

Firstly, install CTP3 of the WCF LOB Adapter SDK V1 SP2, and the BizTalk Adapter Pack V2 (making sure that you install the SAP ADO Provider).

Next, you need to make a few changes to the SSRS related config files, in order to have the provider show up in SSRS projects:

  • Modify the RSReportDesigner.config file under the Visual Studio 2005 installation directory (on my machine, this is present at C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies).

In the above screenshot, the entries with the name “SAP-AP” are the ones added by me. The entries I added were:

<Extension Name=”SAP-AP”
           Type=”Microsoft.Data.SAPClient.ReportingServicesExtension.SAPConnectionWrapper,
           Microsoft.Data.SAPClient.ReportingServicesExtension,
           Version=3.5.0.0,
           Culture=neutral,
           PublicKeyToken=31bf3856ad364e35″/>

and

<Extension Name=”SAP-AP” Type=”Microsoft.ReportingServices.QueryDesigners.GenericQueryDesigner,
           Microsoft.ReportingServices.QueryDesigners”/>

  • Modify the RSReportServer.config file under the SQL 2005 installation directory (on my machine, this is present at C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer).

In the above screenshot, the entry with the name “SAP-AP” is the one added by me. The entry I added was:

<Extension Name=”SAP-AP”
           Type=”Microsoft.Data.SAPClient.ReportingServicesExtension.SAPConnectionWrapper,
           Microsoft.Data.SAPClient.ReportingServicesExtension,
           Version=3.5.0.0,
           Culture=neutral,
           PublicKeyToken=31bf3856ad364e35″/>

 

We’ll now create a simple SSRS project using the “SAP-AP” provider.

  • Start Visual Studio 2005, create a New Project using the template under Business Intelligence Projects -> Report Server Project. Click OK in the dialog below.
  • Right click on the “Shared Data Sources” node, choose “Add New Data Source”.
  • Create a new data source named “SAPDataSource”, as shown in the screenshot below:
  • In the above dialog, in the Credentials tab, make sure that “No Credentials” is selected (since we’re entering the credentials directly in the Connection String). Click OK to save the information and close the dialog.
  • Right click on the “Reports” node, and choose “Add New Report”. Choose the “SAPDataSource” entry from the “Shared Data Source” drop down list. Click Next to move to the next dialog.
  • Enter the query to use in the Query String section. Click Next a few more times (the dialogs after this allow you to customize the layout – for now, you can stick with the defaults).
  • Right click on the project node in Solution Explorer, to set the start-up report. In the screenshot below, I’ve set the “StartItem” to the newly created report above (named Report1.rdl). (Note – I had named my project “Report Project1DEL”). Click OK to close the dialog.
  • Hit F5 to run the Project. A screen similar to the one below should appear. Note that there is an empty text box labeled “P1” below – this is where you would enter the value to use for the P1 parameter, in our query which we used earlier (“SELECT TOP 10 KUNNR, NAME1 FROM KNA1 WHERE NAME1 LIKE @P1”). I’ve entered the string “AB%” – which thus means that I want the first 10 customer numbers and names from the KNA1 table, where the customer name starts with “AB”.
  • Click the “View Report” button (top-right corner) to execute the query and view the data. On my machine (running against my SAP server), I see the data below:

 

Question – do you have a need to use the Siebel ADO.NET Provider from SSRS? If so, please leave a comment here (leave your email address too, so that we can contact you in case we need to understand your scenario more, etc).