Wednesday 23 March 2011

Mircosoft ReportViewer with Sub Reports

Creating a report for report viewer and including a sub-report.

1. Create a new .ASPX page.
2. Go to the Toolbox and double click on Microsoft ReportViewer.
3. Right click on the project name in the Solution Explorer and right click.
4. Select Add New Item
5. Select Data Set and rename the Data Set.
6. Click Add
7. Click Yes to save the new Data Set in the App_Code folder.

In this instance I will be using a Stored Procedure already created.

8. Click on Server Explorer in the design view of the Data Set.
9. In the Server Explorer Window expand the folders for the database and then the Store Procedure folder to find the stored procedure you wish to use.
10.Hold the left button of your mouse down on the stored procedure you wish to use and drag the stored procedure onto the design view of the Data Set.
11. Save and close the data set.
12. Right click on the project name in the Solution Explorer and right click.
13. Select Add New Item
14. Select Report and rename the Report.
15. Click Add
16. Ensure your new report is open.
17. Expand Web Site Data Sources
18. Expand the Data Set that you created from step 8.
19. Drag and drop the field to be displayed in your report on to your report document in design mode.
20. Save your report document.
21. Go to your .ASPX page created in step 1.
22. Click on the arrow to the top right of the ReportViewer.
23. Under the drop drown list for choose report, select the report that you have created from step 14.
24. Then click on Choose Data Sources from below the drop down list called “Choose Report”
25. The Data Set you created in step 8 should automatically be chosen as Report Data Source for the Data Source Instance.
26. On the Object Data Source created below the ReportViewer click on the right arrow and select Configure Data Source.
27. On the page titled “Choose a Business Object”, the business object should be automatically created. Click Next
28. The next page titled “Define Data Methods” you will need to select the method from the drop down list.
29. Select the appropriate method and click Next.
30. On the page titled “Define Parameters”, you would only need to define a parameter if the SQL statement requires one.
31. Input your parameter method if needed.
32. ClickFinish
33.You can now test this report.
34. Now to create a sub report.
35. Again you will need a data set for your sub report so repeat steps 3 to 11.
36. Now you need to create the report form for your sub-report. For this you need to repeat steps 12 to 19 Only.
37. Next right click on the square on the top left axis of where both the axis meets.
38. From the context menu select Report Parameters.
39. In the Report Parameters wizard. Click add and enter a name in both Name and Prompt in the properties area. I tend to keep these names the same. Make a note of these names for future reference.
40. Click OK on the Report Parameters wizard.
41. Save and close your new sub report.
42. Find and open your original report created between steps 12 to 20.
43. Open the toolbox and drag and drop subreport on to your report. Resize the subreport so its shape will be displayed correctly in your report.
44. Right click on the grey area of the subreport and click properties in the context menu.
45. Under the General tab, find the drop down list subreport and find the name of the report you have created for you subreport and then click on it.
46. Then click on the parameters tab.
47. Click to the right of the star under the heading Parameter Name and enter the name of the parameter your created in step 39.
48. Click then under the heading Parameter Value and select the value you wish to use as a parameter to connect the report to your subreport, for which the subreport will need for its stored procedure.
49. Click OK.
50. Save and close the report.
51. Goto the code page of the ASPX Page you created in step 1. It will end in aspx.vb or aspx.cs dependent on which language you are using. In this case we are using VB.NET so it will be aspx.vb.
52. Above the Parital Class of the .aspx.vb document enter the following import commands

Imports Microsoft.Reporting.WebForms
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlClient.SqlCommand

53. In the Page Load sub of the .aspx.vb page enter:

AddHandler Me.ReportViewer1.LocalReport.SubreportProcessing, AddressOf ReportViewer1_SubreportProcessing

Note. ReportViewer1 is the name(ID) of your reportviewer in the HTML code. ReportViewer1_Subreportprocessing is the name of the subroutine in step 54.

54. Create the following subroutine.

Public Sub ReportViewer1_SubreportProcessing(ByVal sender As Object, ByVal e As SubreportProcessingEventArgs)

Dim strParameter1 As String = e.Parameters("").Values(0).ToString()
Dim rdS As New ReportDataSource("", (strParameter1))
e.DataSources.Add(rdS)

End Sub

Note: is the name of the parameter you created in step 39.
is created from the name of your dataset and the name of your stored proceedure in this fomrat “dataset_storedproceedure”.
is the name of the subroutine in step 55 used to call the data for the subreport.

55. Finally enter the following code for subroutine to open the database to and collect the data for the subreport.

Public Function (ByVal strParameter1 As String)
Dim dta As New SqlDataAdapter()
Dim con As New SqlConnection("")
Dim ds As New DataSet()
con.Open()
Dim cmd As New SqlCommand("", con)
dta.SelectCommand = cmd
dta.SelectCommand.Connection = con
dta.Fill(ds, "subrep_table")
con.Close()
Return ds.Tables("subrep_table ")
End Function

56. Save all document.
57. If you now run your report the report will display all data from both the report and the subreport.

No comments:

Post a Comment