| Shreeman's profileShreemanBlogLists |  Tools  Help |
|
December 19 F# chapter for download and resourcesDon Syme has provided few links to download the next book chapters on F# the language which is a combo of functional and OOP and used in the XNA tools.
For more detailc check out the links:-
ILX http://research.microsoft.com/projects/ilx/ilx-ref.doc and abstract IL http://research.microsoft.com/projects/ilx/absil.aspx
c# 3.0 to f# http://tomasp.net/articles/csharp3-concepts.aspx
Enterprise Reporting New Dimension with SAP-.NET Inter op with SSRS PART-II:-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";
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 Duet white paper from MS :-SAP with SqlServer 2005 Blogs:- Enterprise Reporting New Dimension with SAP-.NET Interop with SSRS PART-I:-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:- SqlServer 2005 FeaturePAck That Includes MySapBusinessSuite:- http://www.microsoft.com/downloads/details.aspx?familyid=D09C1D60-A13C-4479-9B91-9E8B9D835CDC&displaylang=en
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) :-
Blogs:- http://blogs.technet.com/chitpro-de/archive/2006/12/06/event-nachlese-sap-microsoft-infotag.aspx
MicrosoftSAP events:- http://www.microsoft-sap.com/events.aspx
SAP Network:- https://www.sdn.sap.com/irj/sdn/developerareas/dotnet
SSO using websvc in .net and SAP :- https://www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/4872
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:-
BizTalk Server 2006 SAP Labs :- http://www.microsoft.com/downloads/details.aspx?familyid=49d09411-2211-4549-9de8-ff3a136202d1&displaylang=en
BizTalk Adapetr For SAP:- http://www.microsoft.com/biztalk/evaluation/adapter/adapters/sap/2002/default.mspx
SmartClient use Websvc to Interface with SAP :- http://www.microsoft.com/technet/itshowcase/content/smartclientandsap.mspx
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.
December 04 Office 2007 LaunchedOfifce 2007 has been launched.You can try the software online or can runa trial version.
Get more details here : http://office.microsoft.com/en-us/products/HA101741481033.aspx
Does Microsoft DO Innovation??Today while surfing the web i found an interesting discussion regarding Innovation at Microsoft.The interviewer starts the topic with Does Microsoft realy do Innovations??He raised the question becaused of many softwares which was not started or originated by microsoft itself but rather were taken thereafter.
Read the interview here :- http://online.wsj.com/public/article/SB116490323676636989-HnHPKLzkyy9xKy2wnokbd2bc_bE_20071130.html
To a certain extent its true that the ideas,methods and/or the concept and features in few/many of the softwares were not originated by MS and in many cases its true that you ll found the softwares or products developed by MS were analogous or similar to few others .I know you can name quite a few starting from the windows to sqlserver to c# to the latest AJAX but i m not agree with the fact that there is less innovation in Microsoft and its only playing the catching .Even i don't mind if they are truely only catching up ,i didn't ever found any organisation which has released so many products and has continually and in parallel has worked on so many products.See the msresearch site for the research areas ,see the MS involvements in [although i believe in late] DEVICE& MOBILE,SECURITY ,WEB technologies and many more...if those were not innovation let it be but that does certainly put a lot of efforts and has produced a lot of qulaity software for the industry.
My view point is solely based on the involvement MS has shown in recent past in different software categories be it devices,security,desktop,mobile or web.The point is does the product MS is providing from the analogous one or taken over one are not far better??...i believe in most of the cases it were came out as far better product.Better i say those never were the same Products but always in 99% were a far superior product.
As i said above ,these are merely my view and i am no where trying nullifying the efforts of a new innovation but if somebody is producing a better products then the innovated one it should always be welcomed be it Microsoft or somebody else it does not matter much to me.
Finally the interview was a good read and its true that the debate will continue....
TECHED Videos on MSDN ShowTimeIF you want to get or to watch the TechED demo and presentation then this news is for you.The msdn ShowTime has been updated with TECHED Session .
Go and check out the cool session demonstrating many features from different events like sqlpass,winhec,teched in msdnshowtime which contains quite a few session now including .net fx3.0,c# 3.0,Ajax,Office,sharepoint etc;
Here is the link : http://www.microsoft.com/emea/msdnshowtime/default.aspx |
|
|
