Download JumpToSSRS.zip - 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 can very well able to render the SSRS rdl remote reports into MOSS portal without being 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 thouroughly seen once go deep dive into this article.

Problem Statement

In real time business case we have scenarios where we need to provide custom reports to client and there we find limitations of SSRS reporting framework. It provide 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. The again challenge is to embed SSRS report in .net without reportviewer control and this can be tackle using IFrame feature of HTML.

Prerequisites

The prerequisites 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 simple SSRS report using BIDS IDE. Add three text box into Layout sections. The idea is to showcase tradeoff between SSRS data coming from .net. This integration will help you to built around more complex solution based on this solution.

Create three hidden Report Parameter such as Report_Parameter_0,Report_Parameter_1 and Report_Parameter_2. Once these parameters are created ,add expression to each of the textbox mentioned above and assigned the value to these parameters respectively.

You can download the source code and can 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 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 its 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 system is integrated using Post/Get method of ASP.net.

Below code snippet demonstrate the illustration given above. In the 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 reader is known to sharepoint 2007.)Create asp.net solution and deployed 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 webaprts property and assign the .net relative path. _layout/yyy/JumpToSSRS.aspx. Once this settings are applied ,publish and checkin 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 key highlights of it.

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. 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 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