Create RDLC Report in VS 2010 and SQL Server 2008

Iftikar Hussain
11y
137.9k
0
0

Article

This article explains how to create a 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

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:

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

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

6. Create a new connection object:

7. Enter the server details:

8. Click on the "Next" button:

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

10. Expand the Stored Procedure node:

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

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

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

Note: Use the Column and Rows groups to grouping column and rows value in the report respectively.

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

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

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

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

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

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

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

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

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.

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

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

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

Enter the details as in the following:

Click on the "Finish" button.

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

<tr>

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>

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

</td>

</tr>

</table>

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

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

28. Configure the data source.

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

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

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

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.

Set the report viewer width and height in ASPX.

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

33. Click on the "fx" button.

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

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

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

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

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 expression". Click the "fx" button.

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)

40. Set the Alignment for center and middle.

41. Run the application and view the report.

For no records:

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

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

You can search data using the Find control.

You can zoom the data by the Zoom control.

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

Up Next

Ebook Download

View all

SQL Queries For Beginners

Read by 5.2k people

Download Now!

Learn

View all