Enterprise Reporting New Dimension with SAP-.NET Inter op with SSRS PART-II:-

Posted: December 19, 2006 in Uncategorized
This post is a continuation of the Reporting scenario using SSRS with SAP .Read the First Part HERE.
In partI we saw how we are interfacing with SAP from within .NET as well as I have provided few resources around .NET -SAP interop .In part1 we saw how to levergae the SSRS Local Mode Reports and in this part we ll continue the discussion with SSRS REMOTE mode as well as i ll provide SSRS links and Resources which ll be useful for developing SSRS reports.
 
SSRS REMOTE Mode:-So what are the development options here for the REMOTE mode for the scenario where by I need to get the data from SAP? First of all can I connect to the SAP system from with in SSRS? Ofcourse YES and the MYSAP suites of Microsoft and the NETWEAVER framework helps you in achieve these [links provided below] and you can directly get the data using the RFCs call as a dataset in SSRS and you are SET.

The next option is let your SAP exposes its data via Web Services and from within SSRS you can set the report data source as XML and in connection string pass the WebSvc Uri and you are through [I was thinking of providing sample for this but later I found that there are quite a few thus providing link for this scenario.].This would be a great scenario and we are consuming XML and Emitting XML but that does comes with more consideration of security and you SAP team’s Web Service expertise.

However you might ask what if I need some processing of the SAP data or what if
I didn’t have configured with the MySap suites or I don’t have a NetWevaer Framework?
You can still use the SAP functionality in following way fall to writing an extension [see the extension sample on] or Custom Assembly. I ll emphasize the Custom Assembly Scenario a bit since this is where I found quite  a few folks messed up with security[including me during config changesJ].
 To use Custom Assembly you need to create your assembly and needed to deploy it to
Deployment Server:-Reportservers: – C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin folder as well as to
[development]
 C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies [for design and preview mode] 

However do remember that you need to meet the security criteria here for Permissions and assertions and for that you might need to set the AllowPartialCaller attribute [assembly: AllowPartiallyTrustedCallers] at assembly level and assert the permission explicitly. Further for debugging the assembly you can then add the class library project with [Debug mode not active debug] to your report server project and set the dependency and Run the report. Do note that during the Preview Mode your Code Run Under Full Trust and thus you can bypass few Permission but the same would not be the scenario while you deploy the report and I found quite a few developers trapped into this.

Finally if you need any custom permission likes FileIO or any other permission [may be you don’t want to deploy into GAC or you are using a third party [which you didn’t trust fully so can’t install into GAC]. Then you need to create your Permission set and Codegroup and need to modify the

C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\ rsreportserver.config and C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\ rssrvpolicy.config and for design Environment

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ RSPreviewPolicy.config to reflect those changes.[see the msdn or BOL for more on this. 

Here is an assembly I used for fetching the Images from DB using Custom Assembly:-
using System;
using System.IO;
using System.Drawing;
using System.Drawing.Imaging;
using System.Drawing.Drawing2D;
using System.Data;
using System.Data.SqlClient;
[assembly:AllowPartiallyTrustedCallers]
namespace MyClassLibrary
{
            public class ImageManager
            {
                        public ImageManager()
                        {          }
 
                         public static byte[] getimagesfromDB(int id)
                        {
                                    byte[] getbytes=new byte[1];
 
//I am accessing DB thru SqlClient thus I need to assert for //SqlClientPermission
SqlClientPermission perm=new SqlClientPermission(PermissionState.UnRestricted);
Perm.Assert();
 
                                    try
                                    {
string connstr=@"myconnstr other params;initial catalog=AdventureWorks";
string sqlquery="select ProductPhotoID,ThumbNailPhoto,LargePhoto from Production.ProductPhoto where ProductPhotoID="+id;
                                               
                                                using (SqlConnection conn=new SqlConnection(connstr))
                                                {
                                                            conn.Open();
                                                            using (SqlCommand cmd=new SqlCommand(sqlquery,conn))
                                                            {
                                                                        using (SqlDataReader reader=cmd.ExecuteReader())
                                                                        {
                                                                                    while (reader.Read())
                                                                                    {
                                                                                                getbytes=(byte[])reader["LargePhoto"];
                                                                                    }
                                                                        }
                                                            }
                                                            if(conn.State==ConnectionState.Open)
                                                            {conn.Close();}
                                                }
                                                return getbytes;
                                               
                                    }
                                    catch(Exception)
                                    {throw;}                                  
                        } 
            }
}
 
I have signed and added the Assembly to GAC and then went and recreate the reference in the Report Properties to reflect the new PublicKeyToken [although you can modify the RDL directly as well].Do note that there is a difference in calling static [global] and reference method in custom assembly [see the BOL or msdn for further details].
 
However once you deployed you can go to the report properties and Add a reference to the Assembly before using it in Report [Note the public Key token changes for signed and Unsigned Assembly [it null usually]. 

Finally How to deliver the reports:-You have quite a few options here aswell:-Normally you can go for the Report Manager to Define the Roles aswell as restrict the Report Access and also to provide the Notification and Email Subscription etc; However that is not the only way and the Report Viewer Control in Remote Mode is a great companion on this and further you can have the Url Access and the Web Service Access using the Reporting Services SOAP apis. Since you ll finds a lot of posts on the Url Access and accessing reports using the Report Viewer Control there are very few on the SOAP API access. Do note that you can use the FormAuth with SSRS and the C:\Program Files\Microsoft SQL Server\90\Samples\ReportingServices\Extensionsample demonstrates one such sample.

Most of the time you ll use the Report Viewer Control to display the report due to its easy usage at client side for the Remote mode and pulls the report out, However I am providing a simple sample of using the SOAP apis to consume the Reporting services which is useful let say from ASP.NET 1.1 or from other app which didn’t ve the access to report Viewer. 

Accessing SSRS Report using SSRS SOAP Apis:-
First of all you need to add a WebRef to the ReportExecution2005 and ReportService2005 
using mysol.ReportingSvc2005Proxy;
using mysol.ReportingExec2005Proxy; 
 
//I am accessing a report using soapapi  and exporting to EXCEL
private void Button1_Click(object sender, System.EventArgs e)
                        {
      ReportExec2005Proxy.Warning[] wa=null;
                        string reportPath = @"/MyCustomAssemblyReport/MyReport1";
                        string ext="";
string devInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>";
                        string mim="";
                        string enco="";
                        byte[] result;
                        string[] streamIDs;
                                                           
                        ReportExecutionService rs = new ReportExecutionService();
                                                            rs.Url="http://localhost/reportserver/reportexecution2005.asmx?wsdl&quot;;
            rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
 
            //you need to pass parameters then uncomment these if ur report has params
 
//ReportingExec2005Proxy.ParameterValue[] parameters = new //ReportingExec2005Proxy.ParameterValue[1];
            //parameters[0] = new ReportingExec2005Proxy.ParameterValue();
            //parameters[0].Name = "PhotoId";
            //parameters[0].Value = "70";
            ExecutionInfo execInfo = new ExecutionInfo();
            ExecutionHeader execHeader = new ExecutionHeader();
            rs.ExecutionHeaderValue = execHeader;
            execInfo = rs.LoadReport(reportPath, null);
            result=rs.Render("Excel",devInfo,out ext,out mim,out enco,out wa,out streamIDs);
            // Write the rendered report to the Web form
            Response.Clear();
Response.AppendHeader("content-disposition","attachment;filename=FileName.xls");
Response.ContentType = "application/vnd.ms-excel";
Response.BinaryWrite(result);                   
} 

If you found this useful I ll be happy. Finally I could ve published this as an article but I have to post few more and hopefully I succeed to post an in-depth view of the architecture, design and working of a vast topic[ I know I ve only scratch the surface though].

Resources and Links:-

SSRS Home : http://www.microsoft.com/sql/technologies/reporting/default.mspx
Deploying a Custom Assembly  :- http://msdn2.microsoft.com/en-gb/library/ms155034.aspx
Using the XML Data Extension to leverage an ADO.NET dataset in your SQL Reporting Services Report:-
 http://blogs.msdn.com/bimusings/archive/2006/03/24/560026.aspx
http://blogs.msdn.com/gsnowman/archive/2005/10/12/480321.aspx
http://www.devx.com/dbzone/Article/21214
How to grant permissions to a custom assembly :-http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b842419
http://www.codeproject.com/sqlrs/WebAndReportingServices.asp
http://blogs.msdn.com/swisowaty/archive/2006/07.aspx
Local mode export to excel :- http://www.gotreportviewer.com/Export.zip
http://www.gotreportviewer.com/CustomerWebSite.zip
Using CustomCode With Report :- http://www.yukonxml.com/chapters/apress/reportingservices/dotnet/
SSW SSRS Site : – http://www.ssw.com.au/ssw/Standards/Rules/RulesToBetterSQLReportingServices.aspx
Report Control Forum : http://forums.microsoft.com/msdn/showforum.aspx?forumid=75&siteid=1
SSRS Forum:-http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=82&SiteID=1
http://msdn2.microsoft.com/en-us/library/ms157304.aspx
http://msdn2.microsoft.com/en-us/library/ms152787.aspx
http://msdn2.microsoft.com/en-us/library/ms159868.aspx
http://msdn2.microsoft.com/en-us/library/ms157143.aspx
http://msdn2.microsoft.com/en-us/library/ms345248.aspx
tutorials:-http://msdn2.microsoft.com/en-us/library/ms170246.aspx
SQLSERVER 2005 ARTICLES:-http://msdn2.microsoft.com/en-us/library/aa496080.aspx
Crystal report to SSRS:-http://msdn2.microsoft.com/en-us/library/aa964127.aspx

Duet white paper from MS :-SAP with SqlServer 2005

Blogs:-
http://blogs.msdn.com/bwelcker/
http://blogs.msdn.com/rdoherty/
http://blogs.msdn.com/bimusings/archive/2006/07/20/673051.aspx
http://prologika.com/CS/blogs/blog/archive/2006/09.aspx

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