You can create a report that hosts a subreport using Data Dynamics Reports.

This walkthrough illustrates how to create a report using a subreport.

The walkthrough is split up into the following activities:

• Creating a report for the subreport
• Connecting the subreport to a data source
• Adding a dataset with a parameter to the subreport
• Adding a report parameter to the subreport
• Adding report items to display data on the subreport
• Creating the main report
• Connecting the main report to a data source
• Adding a dataset to the main report
• Adding report items to display data on the main report
• Viewing the report

To complete the walkthrough, you must have access to the Reels.mdb sample database included with this installation.

When you are finished you will have a report that looks similar to the following.

Creating a report for the subreport

To create a report in Visual Studio

1. Create a new project in Visual Studio.
2. From the Visual Studio Project menu, select Add New Item.
3. In the Templates window, select Report and name the report Sales.rdlx.
4. Click Add in Visual Studio 2005. 

Connecting the subreport to a data source

To connect the Sales report to a data source

1. If the Data Explorer is not in view, from the View menu, select Other Windows, then Data Dynamics Reports Data Explorer (at the bottom).
2. Click the Add icon and select the Data Source... option.
   
3. In the Report DataSource smart panel that appears, select the General page.
4. Change the Name to Reels.
5. Select the Shared Reference checkbox.

    

6. Click the Browse button and select ReelsDataSource.rdsx, which is located in C:\My Documents\Data Dynamics\Reports\build number\Samples\Reels.

   Note: In Windows Vista, the path is C:\User\your name\Documents\Data Dynamics\Reports\build number\Samples\Reels\.

7. Click the Accept button in the lower right corner to close the smart panel and see Reels appear in the Data Explorer.

   

Adding a dataset with a parameter to the subreport

When you add a query parameter using the syntax required by your database you must add a parameter to the Parameters page to ensure that the parameter value is passed to the query from the Report Parameters collection.

To add a dataset with a parameter to the subreport

1. Click the Add icon and select the Data Set... option.
   
2. In the DataSet smart panel that appears, select the General page.
3. Change the Name to EmployeeSales.
4. On the Parameters page under Parameter Name enter EmpID.
5. Under Value enter

   =Parameters!EmpID.Value

6. On the Query page, paste the following SQL command into the Query text box:
    

   SELECT * FROM EmployeeSales;

7. Click the Validate icon to validate the query and to populate the Fields list.
   
8. Select the Fields page to populate the Fields list.
9. Click the Accept button in the lower right corner to close the smart panel and see EmployeeSales and the selected fields appear in the Data Explorer.
   

Adding a report parameter to the subreport

To add a report parameter to the subreport

1. On the Layout tab of the report, from the Report menu, select Report Parameters.
2.  Click the Add icon.
   
3. Under Name enter EmpID.
4. Under Data type select Integer.
5. Under Prompt enter Enter employee ID. (This is not necessary for the subreport, but it will allow you to preview the report.)
6. Click the Accept button in the lower right corner to close the smart panel.
   

Adding controls to display data on the subreport

To add a Table control with grouping to the subreport

1. From the toolbox, drag a Table data region onto the body of the report.
2. With the table selected in the combobox at the top of the property grid, set the DataSetName property to EmployeeSales.
3. Click inside the table to display the column and row handles along the top and left sides of the table.
4. Right-click the handle above the rightmost column and select Insert Column to the Right.
5. Click the column handle at the top of each column in turn to select it, and in the property grid, set the Width property as indicated in the table. 

   Column	Width
   First	1.5in
   Second	1.5in
   Third	1.2in
   Fourth	1.55in

   Tip: In most cases it is easier to resize existing columns before adding new columns because this prevents the table from growing horizontally and pushing the report width beyond what will fit on paper.

6. Right-click the handle to the left of the Detail row and select Insert Group.
7. In the Groups smart panel, select Table1_Group1.
8. Under Expression select

   =Fields!EmployeeID.Value

   This groups all details from each employee.
9. Change the Name to Employee.

   Note: You cannot change the name of a table group until after you have set the expression.

10. Click the Accept button in the lower right corner to close the smart panel.
    
11. Right-click the handle to the left of the detail row and select Edit Group to access the Detail Grouping smart panel.
12. Under Expression select

    =Fields!SaleID.Value

    This lists the total amount of each sale instead of listing each item sold within each SaleID.
13. Click the Accept button in the lower right corner to close the smart panel.
    
14. Right-click the handle to the left of the header row for the grouping and select Insert Row Below. We will use this new row for static labels which will repeat at the top of each new group.
15. Right-click any handle to the left of the table and select Table Header to toggle off the table header.
16. Right-click any handle to the left of the table and select Table Footer to toggle off the table footer.
17. Select Body in the property grid and set the Size property to 5.75in, 1in so that it will fit inside the subreport control on the main report.

To add data fields to the Table data region

1. From the EmployeeSales dataset in the Data Explorer, drag the following field onto the first group header row of the table.

   Data Field	Column	Special Formatting
   Name	TableColumn1	Bold

2. Use the Shift key and the mouse to select the first two cells in the first group header row, right-click and select Merge Cells. This allows the employee name to span two columns in the table.
3. Using the handle to the left of the first group header row, select the row and set the BackgroundColor property to LightSteelBlue.

   Tip: Even if you will not be using colors in your finished report, it is often helpful to do so during the design of a report to make identification of the various sections easier for troubleshooting when you preview it.

4. Enter the following text into the cells in the second group header row of the table.

   Text	Column	Special Formatting
   Sale Date	TableColumn1	Bold, TextAlign Right
   Sale Number	TableColumn2	Bold, TextAlign Right
   Quantity	TableColumn3	Bold, TextAlign Right
   Total	TableColumn4	Bold, TextAlign Right

5. Since the details are grouped by SalesID, only one detail row is shown for each SalesID. To show a summary of the quantity and total fields for each SalesID, add the Sum aggregate to the expression for each of those two fields:
   • =Sum(Fields!Quantity.Value)
   • =Sum(Fields!Total.Value)
6. Using the handle to the left of the second group header row, select the row and in the BackgroundColor property select LightGray.
7. From the EmployeeSales dataset in the Data Explorer, drag the following fields onto the detail row of the table.

   Data Field	Column	Special Formatting
   SaleDate	TableColumn1	Format property: d (uses short date formatting)
   SaleID	TableColumn2
   Quantity	TableColumn3
   Total	TableColumn4	Format property: C (uses currency formatting)

8. Using the handle to the left of the group footer row, select the row and in the BackgroundColor property select LightGray.
9. From the EmployeeSales dataset in the Data Explorer, drag the following fields onto the group footer row of the table. Notice that the value of fields dragged onto the group footer row automatically use the Sum aggregate function.

   Data Field	Column	Special Formatting
   Quantity	TableColumn3
   Total	TableColumn4	Format property: C (uses currency formatting)

10. Enter the following text into the indicated cell in the group footer row of the table.

    Text	Column	Special Formatting
    Employee Total:	TableColumn2	Bold, TextAlign Right

11. From the File menu, select Save.
12. Go to the Preview tab, enter 1035 for the employee ID, and click the View Report button.
13. Your report should look similar to this.

    

Creating the main report

To create a report in Visual Studio

1. From the Visual Studio Project menu, select Add New Item.
2. In the Templates window, select Report and name the report Employee.rdlx.
3. Click Add in Visual Studio 2005. 

Connecting the main report to a data source

To connect the Employee report to a data source

1. If the Data Explorer is not in view, from the View menu, select Other Windows, then Data Dynamics Reports Data Explorer (at the bottom).
2. Click the Add icon and select the Data Source... option.
   
3. In the Report DataSource smart panel that appears, select the General page.
4. Change the Name to Reels.
5. Change the Type to OLEDB.
6. Use the following Connection String, replacing the text your name and build number with the actual name and build number on your machine: 

   Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\your name\My Documents\Data Dynamics\Reports\build number\Samples\Reels\Data\Reels.mdb;User id=admin;password=;

   Note: In Windows Vista, the path is C:\User\your name\Documents\Data Dynamics\Reports\build number\Samples\Reels\Data\Reels.mdb.

7. Click the Accept button in the lower right corner to close the smart panel and see Reels appear in the Data Explorer.
   

Adding a dataset to the main report

To add a dataset to the main report

1. Click the Add icon and select the Data Set... option.
   
2. In the DataSet smart panel that appears, select the General page.
3. Change the Name to EmployeeInfo.
4. On the Query page, paste the following SQL command into the Query text box:
    

   SELECT * FROM EmployeeInfo;

5. Click the Validate icon to validate the query and to populate the Fields list.
   
6. Click the Accept button in the lower right corner to close the smart panel and see EmployeeInfo and the selected fields appear in the Data Explorer.
   

Adding report items to display data on the main report

To add a static label to the top of the main report

1. On the Layout tab of the report, drag a TextBox report item onto the body of the report from the toolbox and set the properties as follows.

   Property	Setting
   FontSize	14pt
   Location	0in, 0in
   Size	6.5in, 0.25in
   TextAlign	Center
   Value	Employee Report by City and Store

To add a List data region that repeats data for each city

1. Drag a List data region from the toolbox onto the report and set the properties as follows.

   Property	Setting
   BackgroundColor	Silver
   DataSetName	EmployeeInfo
   Location	0in, 0.25in
   Size	6.5in, 2.375in

2. Right-click the list and select Properties.
3. In the List smart panel, select Detail Grouping.
4. Under Expression, select

   =Fields!City.Value

5. Click the Accept button in the lower right corner to close the smart panel and accept the change.
   
6. From the Data Explorer, drag the City field onto the list and set the properties as follows.

   Property	Setting
   FontSize	12pt
   Location	0in, 0in
   Size	6.5in, 0.25in
   TextAlign	Center

To nest a second List data region that repeats data for each store within the city

1. Drag a List data region from the toolbox onto the first list and set the properties as follows.

   Property	Setting
   BackgroundColor	Beige
   DataSetName	EmployeeInfo
   Location	0.125in, 0.3in
   Size	6.25in, 1.9in

2. Right-click the list and select Properties.
3. In the List smart panel, select Detail Grouping.
4. Under Expression, select

   =Fields!StoreName.Value

5. Click the Accept button in the lower right corner to close the smart panel and accept the change.
   
6. From the Data Explorer, drag the StoreName field onto the list and set the properties as follows.

   Property	Setting
   FontWeight	Bold
   Location	0in, 0in
   Size	1.375in, 0.25in

To nest a third List data region that repeats data for each employee in the store

1. Drag a List data region from the toolbox onto the second list and set the properties as follows.

   Property	Setting
   BackgroundColor	White
   DataSetName	EmployeeInfo
   Location	0.125in, 0.25in
   Size	6in, 1.5in

2. Right-click the list and select Properties.
3. In the List smart panel, select Detail Grouping.
4. Under Expression, select

   =Fields!EmployeeID.Value

5. Click the Accept button in the lower right corner to close the smart panel and accept the change.
   
6. From the Data Explorer, drag the following fields onto the list and set the properties as follows.

   DataField	Location	Size	Special Formatting
   Name	0.875in, 0in	2.875in, 0.25in
   Education	1.125in, 0.25in	2.625in, 0.25in
   DateOfBirth	5in, 0in	0.875in, 0.25in	Format: d (short date)
   PhoneNumber	4.875in, 0.25in	1in, 0.25in

7. From the toolbox, drag five text boxes onto the list and set the properties as follows.

   Value	Location	Size	Special Formatting	Name
   Name:	0.125in, 0in	0.625in, 0.25in	Bold
   Education:	0.125in, 0.25in	0.875in, 0.25in	Bold
   Date of Birth:	3.875in, 0in	1in, 0.25in	Bold
   Phone:	3.875in, 0.25in	0.875in, 0.25in	Bold
   Sales Record	0.125in, 0.5in	1.125in, 0.25in	Bold	SalesRecord

To add a Subreport control to the main report

1. From the toolbox, drag a Subreport control onto the list below the text boxes and set the properties as follows.

   Property	Setting
   Location	0.125in, 0.75in
   NoRows	No sales recorded for this employee during 2005.
   ReportName	Sales (ensure that this report is saved in the same directory as the Sales report)
   Size	6in, 1.5in
   Visibility: Hidden	True (hides the subreport initially)
   Visibility: ToggleItem	SalesRecord text box (puts a toggle image next to the text that shows the subreport when clicked)

2. Right-click the Subreport control and select Properties.
3. On the Parameters page of the Subreport smart panel, set the Parameter Name to EmpID (must match the parameter in the subreport exactly) and the Parameter Value to

   =Fields!EmployeeID.Value

   NOTE: You can use the option of having the subreport automatically apply the same theme as the hosting report. This option is available on the General page of the Subreport Properties.

4. Click the Accept button in the lower right corner to close the smart panel.
   
5. From the File menu, select Save.

Viewing the report

Any report designed with Data Dynamics Reports can be opened in the included Data Dynamics Reports Viewer application or you can view it at design time.

To view the report at design time

1. Click the Preview tab for the Employee report. 
2. Click the + to the left of Sales Record to see the subreport.

To view the report at run time

1. Add the ReportPreview control to your Visual Studio toolbox and drop it onto your Windows form.
2. Set the Dock property of ReportPreview1 to Fill so that it will automatically resize if the form is resized at run time.
3. Double-click the viewer to go to the Load event and use code like the following.

   'Visual Basic.NET 
   Dim rpt As New System.IO.FileInfo(Application.StartupPath & "..\\..\\Employee.rdlx") 
   Me.ReportPreview1.OpenReport(rpt)

   //C# 
   System.IO.FileInfo rpt = new System.IO.FileInfo(Application.StartupPath + @"..\..\..\Employee.rdlx"); 
   this.reportPreview1.OpenReport(rpt);

4. Run the project.
5. Click the + to the left of Sales Record to see the subreport.

See Also