Create RDLC Report in VS 2010 and SQL Server 2008

Introduction

This article explains how to create an RDLC report using VS 2010 and SQL 2008.

I will create one RDLC report to fetch order details for a given date range.

Please use the following procedure

1. Create a new ASP.NET Empty Project and add a new aspx page named RdlReport.aspx

RDC1.jpg

2. Download the latest AjaxControlToolkit and add the reference to it.

3. Add the Script Manager tag to RdlReport.aspx.

<body>
    <form id="form1" runat="server">
    <ajaxToolkit:ToolkitScriptManager ID="ScriptManager" runat="server" AsyncPostBackTimeout="3600">
    </ajaxToolkit:ToolkitScriptManager>
    <div>      
   
    </div>
    </form>
</body>
</html>

4. Add a new item.

RDC2.jpg

5. Select Reporting category and select Report Wizard and click the "Add" button

RDC3.jpg

Considered that the Stored Procedure has already been created to fetch the Order result for a given date.

6. Create a new connection object.

RDC4.jpg

7. Enter the server details.

RDC5.jpg

8. Click on the "Next" button.

RDC6.jpg

9. Set the connection string name and click on the "Next" button.

RDC7.jpg

10. Expand the Stored Procedure node.

RDC8.jpg

11. Select the particular Stored Procedure and click on the "Finish" button.

RDC9.jpg

12. It will list the output columns of the Stored Procedure then click on the "Next" button.

RDC10.jpg

13. Drag the column from the Available fields to the Values block.

RDC11.jpg

Note

Use the Column and Rows groups to group column and rows values in the report respectively.

14. All numeric field values are automatically converted to the Aggregate function Sum. So there is a need to manually remove it; just right-click and uncheck the option.

RDC12.jpg

15. Drag the rest of the required columns to be displayed in the report and click on the "Next" button.

RDC13.jpg

16. Select the Layout. The option will be enabled if you use either Row Groups or Column groups.

RDC14.jpg

17. Select a different style and click on the "Finish" button. It will create OrderReport.rdlc and NorthwindDataSet.xsd.

RDC15.jpg

RDC16.jpg

18. Click on "Report Data" (on the left side) to add input parameters.

RDC17.jpg

19. Add two parameters for StartDate and EndDate by selecting the Data type as Date/Time.

RDC18.jpg

RDC19.jpg

20. Assign the parameter value to the DataSet. Right-click on the Report window and click on "Report Properties".

RDC20.jpg

21. Click on "Variables" and Add a button.

RDC21.jpg

22. Enter the details of the variables. Provide the name StartDate and click on the "fx" button. Click on "Parameters" and select StartDate. Do the same for EndDate.

RDC22.jpg

RDC23.jpg

RDC24.jpg

23. Click on the NorthwindDataSet.xsd. On the windows right-click on "TableAdapter" and click on "Configure".

RDC25.jpg

24. Configure the TableAdpter (by default it will be configured).

Select the Stored Procedure and click on the "Next" button.

RDC26.jpg

Enter the details as in the following.

RDC27.jpg

Click on the "Finish" button.

RDC28.jpg

25. Go to the ASPX and insert the following code for the Start Date and End Date fields:

<table border="0">

    <tr>
        <th colspan="2">
            Test Order Report
        </th>
    </tr>
    <tr>
        <td>
            Start Date
        </td>
        <td>
            <asp:textbox id="TxtBxStartDate" width="70px" runat="server"></asp:textbox>
        </td>
    </tr>
    <tr>
        <td>
            End Date
        </td>
        <td>
            <asp:textbox id="TxtBxEndDate" width="70px" runat="server"></asp:textbox>
        </td>
    </tr>
    <tr>
        <td colspan="2" style="text-align: center;">

            <asp:button id="BtnViewReport" text="View Report" runat="server" onclick="BtnViewReportClick" />
        </td>
    </tr>
</table>

26. Drag and drop a Report Viewer from the Toolbox.

RDC29.jpg

27. Choose OrderReport.rdlc from the option and click on Save (Visual Studio) button. Compile your project.

RDC30.jpg

28. Configure the data source.

RDC31.jpg

29. Select the DataAdapter that was created initially and click on the "Next" button.

RDC32.jpg

Select the method (GetData) and click on the "Next" button.

RDC33.jpg

Define the parameters by selecting the Parameter source as Control and selecting the respective field.

RDC34.jpg

RDC35.jpg

Click on the "Finish" button.

30. Write the following code.

public partial class RdlReport : System.Web.UI.Page

{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)

        {
            ReportViewer1.ShowParameterPrompts = true;
            ReportViewer1.Visible = false;
        }
        DataBind();
    }


    protected void BtnViewReportClick(object sender, EventArgs e)
    {
        ReportViewer1.Visible = true;
        var startDate = TxtBxStartDate.Text;
        var endDate = TxtBxEndDate.Text;

        var reportParameterCollection = new ReportParameter[2];
        reportParameterCollection[0] = new ReportParameter { Name = "StartDate" };
        reportParameterCollection[0].Values.Add(startDate);
        reportParameterCollection[0].Visible = true;

        reportParameterCollection[1] = new ReportParameter { Name = "EndDate" };
        reportParameterCollection[1].Values.Add(endDate);
        reportParameterCollection[1].Visible = true;


        ReportViewer1.LocalReport.SetParameters(reportParameterCollection);
        ReportViewer1.LocalReport.Refresh();

    }
}

31. Run the application. Enter the start and end date and click on the "View Report" button.

RDC36.jpg

Set the report viewer width and height in ASPX.

RDC37.jpg

32. Format the Order Date column to only display a date. Go to the RDLC file and right-click on the OrderDate column. Click on "Text Box Properties".

RDC38.jpg

33. Click on the "fx" button.

RDC39.jpg

34. Set the expression and click the "OK" button.

RDC40.jpg

35. Add the "NO RECORDS" option. Right-click on the RDLC and click on "Insert" -> "Text Box".

RDC41.jpg

36. Adjust the Text Box and right-click to set the properties.

RDC42.jpg

37. Enter the text in the Value field and set the Font style.

RDC43.jpg

RDC44.jpg

38. Set the Visibility of this text box, since it should display only if there is no record from the query. Click on "Visibility" -> "Show or Hide based on the expression". Click the "fx" button.

RDC45.jpg

39. Set the expression to check if the count for OrderId (considered any column) is zero or not.

=IIF(Count("OrderId","DataSet1")=0,False,True)

RDC46.jpg

40. Set the Alignment for the center and middle.

RDC47.jpg

41. Run the application and view the report.

RDC48.jpg

For no records.

RDC49.jpg

In the Report Viewer, you can export the output to Excel, PDF, and Word.

RDC50.jpg

You can refresh and print the output by clicking on the "Refresh" and "Print" icons.

RDC51.jpg

You can search data using the Find control.

RDC52.jpg

You can zoom the data with the Zoom control.

RDC53.jpg

You can hide Zoom, Find, Refresh and Print a control by setting the properties in the Report Viewer.

Conclusion

In this article, we learned about Create RDLC Report in VS 2010 and SQL Server 2008.


Similar Articles