Using the SAP ADO.NET Provider From SSRS 2005
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 (EDIT: these changes (to the two config files) are now made as part of the Setup in CTP4 onwards):
- 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).
Add the entry below
<Extension Name="SAP-AP"
Type="Microsoft.Data.SAPClient.ReportingServicesExtension.SAPConnectionWrapper,
Microsoft.Data.SAPClient.ReportingServicesExtension,
Version=3.5.0.0,
Culture=neutral,
PublicKeyToken=31bf3856ad364e35"/>
to the Data section. Add the entry below
<Extension Name="SAP-AP" Type="Microsoft.ReportingServices.QueryDesigners.GenericQueryDesigner,
Microsoft.ReportingServices.QueryDesigners"/>
to the Designer section.
- 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).
Add the entry below
<Extension Name="SAP-AP"
Type="Microsoft.Data.SAPClient.ReportingServicesExtension.SAPConnectionWrapper,
Microsoft.Data.SAPClient.ReportingServicesExtension,
Version=3.5.0.0,
Culture=neutral,
PublicKeyToken=31bf3856ad364e35"/>
to the Data section.
NOTE: I’ve attached the .config files, as they appear on my machine, so that you can have a look at what the .config entries should look like after modification.
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).