Enterprise Reporting New Dimension with SAP-.NET Interop with SSRS PART-I:-

Posted: December 19, 2006 in Uncategorized

 Integrating SAP, SSRS 2005 and ASP.NET 2.0:- 

For part2 Click HERE

You might have noticed that I have started few posts around asmx to WCF titled "last lap around asmx before moving to wcf" but I had only 2 posts and got interrupted with some interesting issues that is for preparing the architect articrafts, specs and docs as well as POC for one of the next project which was quite interesting.  

The primitive part of project was about preparing Enterprise reporting system together with data from SAP and SQLserver using SSRS 2005 and SAP-.NET interop.Actually the project scope was more then that like integrating enterprise search, image manipulation and processing[[creating thumbnail,b&w ,upload, download,search,resolution,print,size.etc..;] as well as few other processing including auditing,notifications,logging etc;; The primitive technology involvement was asp.net 2.0,enterprise library,SQLServer2005,SAP.Net 2.0 etc..;  

The most interesting part was of course Enterprise Reporting which was integrated with Data from SAP and SQLServer both. There were a quite a few options available and quite a few decisions needed to be taken from the architectural point of views which hinges around SAP and SSRS interops. 

Like How you ll get the data/images [yes images too resides in SAP] from SAP into Asp.Net 2.0 process them and send to SSRS?

How you push data into SAP?
How you expose your functionalities as Services for Reusability?
How you attain goals of scalability and how the same can be consumable in different platforms. 

 Once you delve into the details of how you ll get the data from SAP into Asp.Net  as well as from ASP.Net to SAP, you need to know what version of framework you are using at both end and whether those are available and interact with each other with any inetrop interfaces? Why not create a framework which allow your application to be consumable from both end .whatever the version exists. The specific to this comes around do I ve ASP.NET framework 2.0 or 1.1 and in SAP do I have netweaver and whether the SAP version is older then 4.6.  

My requirement was this should be consumable from both asp.net 1.1 and 2.0 but who knows tomorrow you might consume it from some other system too. Now coming to the other side of the story SAP exposes the interface to .NET in 2 ways [I m not into SAP but necessity is mother of invention] that is using RFC [remote func call] and also in SOAP format. Although the SOAP bits were more facilitated with the new versions, like netweaver BI but those were available in early days as well. Now when to choose what is a scenario driven again and if it’s a old version of SAP and it was meant in the same enterprise Domain you might see the RFCs [using tcp…u can see this as somewhat familiar to remoting which allows you to issue commands to SAP systems and get the data back in result set].  

So now the question is where I can find the details of .NET connector. Here you go:-http://help.sap.com/saphelp_nw2004s/helpdata/en/e9/23c80d66d08c4c8c044a3ea11ca90f/content.htm

And here are few more SSRS with SAP NETWeaver:- http://msdn2.microsoft.com/en-us/library/ms365162.aspx Configuring Reporting Services to Use the Microsoft .NET Data Provider for mySAP Business Suite  :-

Configuring Reporting Services to Use the Microsoft .NET Data Provider for mySAP Business Suite 

Now that you know you can connect to SAP[let me mention it here that now .NET and SAP integration let you integrate with SAP in SharePoint as well as BizTalk adapter is also available for you to integrate with SAP.[I ll provide the links at the bottom of this post].

So now you are coming to the point of what sort of SAP interface I ll be interact with SOAP based or RFC based and here things depends on few scenarios like whether I already have SAP systems developed in earlier bits?

Can as enterprise we afford more SAP interface development for SAP stuff like SOAP and Web Services? Do we have a framework available? [Like netweaver]?What version of SAP , I ve in my production and the Long-term Goals and These are few questions you need to find out for a True SOA [Service Oriented Arch] and SAAS[software As Service] or eSOA in SAP’s term. I am hesitate but I can term the RFC based protocols as legacy –however these more suitable in many scenarios since enterprise deployed their SAP solutions for internal usage and those holy-cow system is running fine[SAP system in their intended means were really superb after all they had put a lot of effort in creating a great persist able metadata system around the ERP domain model[at least I understood SAP this way and I am mere a novice in SAP related stuff and ve learned few bits while working with the current integration thus if I am making mistakes do help me in correct them .] and subsequent extensibility make it a better ERP model. My point here is if I am thinking of interoperability to different systems and technology I need to fall into some standards which understood by all those system and SOAP behind XML is currently driving those. But if you still have a Legacy bit you can still manage and produce a good SOA and Enterprise Service using the .NET features. My point here is if ur SAP is still not capable of producing the SOAP and XML you can still leverage the capabilities with the .NET integration.

How you do that: – if I ve to make it simple connect to SAP using Microsoft/ SAP [not bother who owns] mysap business suites expose the functionality thru .NET. [See the resources link at below]. 

Now that you have taken the decision of SAP interfacing there are quite a few choices left to be made on the reporting system [I was bit diverted into the SAP details thus back again].So here are few SSRS consideration you need to made:-

The primitive reporting requirements were stands between 
Report design and development,
Report Deployment 
Report Subscription and delivery.
Although there are more to this like scheduling, report admin and as well as allow client to author reports using report builder, Report Extension and Security.{from architecture stand point we ll consider the top into the broad category first.}. 
 
SO what are the issues you have for Report development??
First of all you need to know what the report is all about and what reporting functionality are you looking for? That is do you need only the Export to Excel and PDF functionality of you report? [don’t mind I had seen quite  a few have been using SSRS for these functionalities as well and so long as the OFFICE 2007 is not into seen you can’t convert directly to PDF and further you are getting a  nice formatted report so that is also an option with  easy integration ] Do you need to consume the report from Smart Client or Windows Applications? Do you need to process the report data source in client itself? Do you don’t need you report to be deployed and consumed outside your applications.  

If all these answers are yes then probably you are looking for SSRS local mode. That is you are using the SSRS local mode for generating the report itself .For implementing these though you need to have the redistribution pkg of the reportviewer viewer[http://msdn2.microsoft.com/en-us/library/ms251723(VS.80).aspx ] and see the http://www.gotreportviewer.com  for further example on these. For Local Mode your report is an integrated part of your application and you don’t need a Report Server. Although these sorts of reports are not distributed and consumed as a standalone many times these might be a fit for Ur requirement.

 Here is a sample code:-first install the redistributable of the report viewer if you are not seeing a report icon [if you add project new item].

Sample Code for SSRS 2005 local Mode:-

create proc ShowProductByCategory (@categoryID int)
as
select  c.CategoryName,p.productname,p.UnitPrice,p.UnitsInStock
from products p join categories c on
p.categoryID=c.categoryID

I want to dynamically bind a report source to display a report what I ll do is runtime I ll pass a dateset and display the report.fo this sample I m populating the list of categories from northwind in a drop down and for the selected categories I m displaying all the product details.So I m creating a object data source which ll return the above products:-

     <for m id="form1" runat="server">
         <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetData"
TypeName="DataSetProductsTableAdapters.ShowProductByCategoryTableAdapter" OldValuesParameterFormatString="original_{0}">
            <SelectParameters>
 <asp:ControlParameter ControlID="DropDownList1" Name="CategoryName"
                    PropertyName="SelectedValue" Type="String" />
            </SelectParameters>
        </asp:ObjectDataSource>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="ur connstr here "
            SelectCommand="SELECT [CategoryID],[CategoryName] FROM [Categories]"></asp:SqlDataSource>
       
        <asp:DropDownList ID="DropDownList1" runat="server"  DataSourceID="SqlDataSource1"
            DataTextField="CategoryName" DataValueField="CategoryId"  AppendDataBoundItems="True">
            <asp:ListItem>Select Category</asp:ListItem>
        </asp:DropDownList>
<rsweb:ReportViewer ID="ReportViewer1" runat="server" Visible="False">
            <LocalReport ReportPath="Report.rdlc">
                <DataSources>
   <rsweb:ReportDataSource DataSourceId="ObjectDataSource1" Name="DataSetProducts_ShowProductByCategory" />
                </DataSources>
            </LocalReport>
        </rsweb:ReportViewer>
        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Show  Report" />
    </for m>
 
  private string thisConnectionString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString; 
public DataSet ExecuteDataset(string sprocname, SqlParameter[] parametcoll)
    {
         DataSet ds;
        using (SqlConnection con = new SqlConnection(thisConnectionString))
        {
            con.Open();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = sprocname;
                cmd.Parameters.AddRange(parametcoll);
                cmd.Connection = con;
                SqlDataAdapter ad = new SqlDataAdapter(cmd);
                ds = new DataSet();
                ad.Fill(ds);
            }
        }
 
        return ds;
    }
 
    protected void Button1_Click(object sender, EventArgs e)
    {
        ReportViewer1.Visible = true;
        System.Data.DataSet thisDataSet = new System.Data.DataSet();
        SearchValue[0] = new SqlParameter("@CategoryID",
                         DropDownList1.SelectedValue);
 
        thisDataSet = this.ExecuteDataset("ShowProductByCategory", SearchValue);
 
        ReportDataSource datasource = new
          ReportDataSource("DataSetProducts_ShowProductByCategory",
          thisDataSet.Tables[0]);
 
        ReportViewer1.LocalReport.DataSources.Clear();
        ReportViewer1.LocalReport.DataSources.Add(datasource);
        if (thisDataSet.Tables[0].Rows.Count == 0)
        {
            lblMessage.Text = "Sorry, no products under this category!";
        }
        ReportViewer1.LocalReport.Refresh();  
    }

 Now that we saw what we can do with the SSRS Local Mode we need to consider the other options for which you can’t use Local Mode Reporting [Although you have the option to generate and covert your RDLC to RDL after all those are XML].Since your report is integrated part of your application you can’t consume it outside of your application and further the scheduling, delivery ,Role, Authentication, Authorization and configuring Report based on user role[let say I want to provide certain report for certain dept]  and other reporting services utilities are missing here. Further it’s not an Enterprise Reporting Solution where by you can consume mange and deploys your report in a most elegant way and the report can be consumed by different application and if needed report can be exposed to outside world.  

However using the above approach and using the .NET connector for SAP you can pull your SAP data and load the same into a Dataset and you are thru to display the report in a better format. Note that you don’t need a separate deployment server to deploy your report here.

 However I am not saying that you can’t pass a dynamic data in SSRS server mode for that you need to create a CDE [custom data extension], if you have sqlserver 2005 installed check the samples in C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services [see the data extension sample for this in Extension samples]. 

I ll continue with the Server or Local Mode Reporting in the Second PART due to the Space Limitation in a single Post.

Here are few of the SAP and .NET Inetrop Resources you might find Useful:- 

Overview: Microsoft SQL Server and SAP Business Solutions: A Compelling Advantage (.pdf) http://download.microsoft.com/download/8/3/8/83873323-2d00-4085-aaea-d9f2e9bfe2a1/SQLTrifoldFinal.pdf
Insights Document: Microsoft SQL Server and SAP Business Solutions: A Compelling Advantage (.pdf) :-

Using SQL Server 2005 with SAP R/3:-
Technical Whitepaper: Using SQL Server 2005 with SAP R/3 (.doc):-
Technical Presentation: Using SQL Server 2005 with SAP R/3 (.ppt):-
Duet for Microsoft Office and SAP – SAP Documentation:-
Use sql05 to connect to sap and sharepoint :- https://www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/2917
DUET :- http://duet.com/ for intro see the video section in Duet.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s