Integration of SSRS and .NET using Iframe and URL Command Approach

• Download source - 3.77 KB

Table of Contents

1. Introduction
2. Problem Statement
3. Prerequisites
4. Implementation Steps & Approach
5. Summary
6. References

Introduction

The idea behind writing this technical paper is to bring the fact that we can tackle the problem of opening SSRS reports in web application without using report viewer webparts. There are lot many challenges when it comes to show custom SSRS reports with user defined .NET search controls.

Using the below implementation approach, we are very well able to render the SSRS rdl remote reports into MOSS portal without using report viewer webpart. The approach is simple. The easy solution is to make use of IFrame concepts and page viewer webpart. The detailed implementation workaround will be thoroughly seen once you dive deep into this article.

Problem Statement

In real time business case, we have scenarios where we need to provide custom reports to clients and there we find limitations of SSRS reporting framework. It provides a very limited search control unlike .NET. In order to overcome this limitation, the below implementation and idea can help to construct reports with most user friendly UI.

The concept is to call remote SSRS reports into .NET using Report Command URL. Using this feature of SSRS, we can expose SSRS meta data to .NET environment. Moreover, we can pass input parameter from .NET to SSRS report using post form or Get method. Then again, the challenge is to embed SSRS report in .NET without reportviewer control and this can be tackled using IFrame feature of HTML.

Prerequisites

The prerequisites are that the reader must be aware of the SSRS concepts and MOSS/sharepoint report viewer webpart. SQL Server 2005 SSRS, MOSS 2007 and .NET 2.0.

Implementation Steps & Approach

Step 1: Create SSRS Report RDL file

Create a simple SSRS report using BIDS IDE. Add three text boxes into Layout sections. The idea is to showcase tradeoff between SSRS data coming from .NET. This integration will help you to build around more complex solutions based on this solution.

Create three hidden Report Parameters such as Report_Parameter_0, Report_Parameter_1 and Report_Parameter_2. Once these parameters are created, add an expression to each of the textboxes mentioned above and assign the value to these parameters respectively.

You can download the source code and view exact implementations there. It is simple and easy to create.

Step 2: Upload RDL file into Report Manager

Upload RDL file into Report manager of SSRS reports folder. If the RDL is error free, there are no issues uploading it into report manager Reports folder.

Note the URL of the report uploaded in Report manager. The URL would be something like this:

• https://xx.com/reportserver/Reports/Demo

Step 3: Custom Input Fields In ASP.NET

Now it's time to write some .NET code. The aim is to integrate .NET and SSRS report. Here we are going to embed remote SSRS report into .NET form without using Report viewer control.

The essential part of this implementation is to invoke or call server side code using JavaScript. At runtime, we construct form and post it through server side with hidden input field containing values to other website of SSRS report. There is mapping between hidden report parameter of RDL with respect to custom input control of ASP.NET. This is how trade off between these two systems is integrated using Post/Get method of ASP.NET.

The below code snippet demonstrates the illustration given above. In a nutshell, we have Report Command URL via which we can control many settings of reports such rendering, format, input fields and type of export of reports and all.

SSRS Report Server URL Command: Using URL Access Parameters

protected void Button1_Click(object sender, EventArgs e)
{           
    first.Text = ConstructForm(TextBox1.Text, TextBox2.Text, TextBox3.Text);
    String strJS = PostFormThroughJS("form1");
    second.Text = strJS;
}
private String PostFormThroughJS(String strFormId)
{
    StringBuilder strScript = new StringBuilder();
    strScript.Append("< script language='javascript'>");
    strScript.Append("var ctlForm = document.forms.namedItem('{0}');");
    strScript.Append("ctlForm.target='Main';");
    strScript.Append("ctlForm.submit();");
    strScript.Append("< /script>");
    return String.Format(strScript.ToString(), strFormId);
}
private String ConstructForm(String strRDLReportParameter0, 
	String strRDLReportParameter1, string strRDLReportParameter2)
{
    StringBuilder strForm = new StringBuilder();
    strForm.Append("< form id=\"form1\" name=\"form1\"  
    action=\"https://xxx/Reportserver?/yyy/Reports/Demo\" method="post">");
    strForm.Append("< input type=\"hidden\" 
    name=\"rs:Command\" value=\"Render\"<");
    strForm.Append("< input type=\"hidden\" 
    name=\"rs:Format\" value=\"HTML4.0\"<");        
    strForm.Append("< input type=\"hidden\" 
    name=\"Report_Parameter_0\" value=\"{0}\"<");
    strForm.Append("< input type=\"hidden\" 
    name=\"Report_Parameter_1\" value=\"{1}\"<");
    strForm.Append("< input type=\"hidden\" 
    name=\"Report_Parameter_2\" value=\"{2}\"<");        
    strForm.Append("< /form >");
    return String.Format(strForm.ToString(), 
    strRDLReportParameter0, strRDLReportParameter1, strRDLReportParameter2);
}   

< html>
<head runat="server">
    <title>Custom Report:SSRS and .Net </title >
</head>
<body>
    <asp:Literal ID="first" runat=server></asp:Literal>
    <asp:Literal ID="second" runat=server></asp:Literal>
    <form id=form2  runat=server target ="Main" >
        <asp:Label ID="Label1" runat="server" 
	Text="Custom Report:SSRS and .Net Integration" Font-Bold="True" 
	Font-Size="11pt"></asp:Label>
        <br />
        <br />
        <span style="font-size: 10pt">
        Name</span>
         <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>  
        <span style="font-size: 11pt">Location</span>
        <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
        <span style="font-size: 11pt">
        Skill</span>
        <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" 
	Text="View Report" />
        <br />
         <br />
        <span style="font-size: 11pt">SSRS Report View</span>
    </form>
  
    
  <IFRAME ID="Main" name="Main" height=500px width=700px runat=server />
</body>
</html>

Step 4: [Optional] Integrate, Embed or Publish In Sharepoint/Moss

(I assume that the reader knows Sharepoint 2007.) Create an ASP.NET solution and deploy the precompiled in Sharepoint Website under _Layout Virtual directory as sub site. Once the precompiled is deployed under subsite(YYY) of virtual directory of _Layout, the URL in such case will be https://xxx.com/_layout/yyy/JumpToSSRS.aspx.

Next step is to open Sharepoint portal and administrator of the site will edit or add new page using site sittings.In the given page, one needs to add Pageviewer webpart. In pageviewer webpart, one needs to modify shared webparts property and assign the .NET relative path, _layout/yyy/JumpToSSRS.aspx. Once these settings are applied, publish and check in the page and it is available to access as per role-user mapping.

Summary

I tried to cover many important things in this article and below are the key highlights:

• Integration of .NET and SSRS without report viewer webpart (URL Command Approach)
• Integration of SSRS report into MOSS without using reportviewer webpart
• Embed/Integrate other website aspx into ASP.NET page using IFrame/JavaScript approach
• Most Important breakthrough. The drawback of using custom input fields/control in SSRS report. I have decoupled the input parameter control from SSRS and shifted to .NET for more flexibility, thus enabling smooth communication between SSRS and .NET

Hope I tried to put things here more reasonably and in the most understandable way. Any suggestions or corrections are most welcome. Thanks!

References

• SSRS Report Server URL Command:Using URL Access Parameters
• HttpWebRequest Post Request To Another Web Site With Redirection