You can create a drilldown report in minutes using the Data Dynamics Reports Matrix control.

This walkthrough illustrates how to create a matrix report.

The walkthrough is split up into the following activities:

Creating a Data Dynamics Report
Connecting the report to a data source
Adding a Dataset with calculated fields
Adding controls to the report to contain data
- Adding nested grouping and drilldown to the matrix
- Adding subtotals to the matrix
- Improving the appearance of the report
Viewing the report

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

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

Creating a Data Dynamics Report

To create a report in Visual Studio

Create a new project in Visual Studio.
From the Visual Studio Project menu, select Add New Item.
In the Categories pane, select Data Dynamics Reports.
In the Templates pane, select Report and name the report SalesByStore.rdlx.
Click Add in Visual Studio 2005.

Connecting the report to a data source

To connect the report to a data source

If the Data Explorer is not in view, from the View menu, select Other Windows, then Data Dynamics Reports Data Explorer (at the bottom).
Click the Add icon and select the Data Source... option.
In the Report DataSource smart panel that appears, select the General page.
Change the Name to Reels.
Select the Shared Reference checkbox.
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\.
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 add a dataset

Click the Add icon and select the Data Set... option.
In the DataSet smart panel that appears, select the General page.
Change the Name to Sales.
On the Query page, paste the following SQL command into the Query textbox:

SELECT Store, SaleDate, SalesAmount FROM Sale ORDER BY Store, SaleDate;
Click the Validate icon to validate the query.

On the Fields page, enter three new calculated fields with the values in the following table.

Field Name	Type	Value
Month	Calculated Field	=Fields!SaleDate.Value.Month
Quarter	Calculated Field	=Choose(1 + ((Fields!Month.Value - 1)\3), "Q1", "Q2", "Q3", "Q4")
Year	Calculated Field	=Fields!SaleDate.Value.Year

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

Adding controls to contain data

The Matrix data region can be used to group data in complex ways. This walkthrough adds just enough complexity to show you what is possible.

To add controls to the report

From the toolbox, drag a Matrix data region onto the body of the report in the top left corner. The matrix initially has four textbox cells.
From Sales in the Data Explorer, drag the Store field into the bottom left cell in the matrix. This is the row header, and dragging a field into it automatically adds a Row Group which groups data by store in rows down the page at run time.
To concatenate a string with the field value and make the Store value more user-friendly at run time, replace the expression in the value property with this expression:
```
="Store Number " & Fields!Store.Value
```
To make the column wide enough to accommodate the text, click the right edge of the grey column handle at the top of the first column and drag it to the right to the 1.5 inch mark on the ruler.
Drag the Year field into the top right cell in the matrix. This is the column header, and automatically groups data in columns to the right at run time.
With the Year cell selected, in the report formatting toolbar click the Justify Center button to center the year over the information below it.
Drag the SalesAmount field into the bottom right cell in the matrix. This is the detail data cell, and displays aggregated data for the intersection of each column and row at run time.
With the SalesAmount cell selected, in the Property window set the Format property to C for currency.

To add nested grouping and drilldown features to the matrix

From the Data Explorer, drag the Quarter field to the top right Year cell and move the cursor down slightly so that the bar appears at the bottom edge of the cell before dropping it. This adds a cell below the Year cell and automatically adds a Quarter grouping to the Column Groups for the matrix.
With the new Quarter cell selected, in the report formatting toolbar click the Justify Center button to center the quarter over the information below it.
By default, the Year cell is named TextBox3. If you have changed it, make note of the new name.
Right-click the grey column handle above the Year cell and select Properties to open the Matrix Data Region smart panel.
On the Column Groups page, select the Matrix1_Quarter group, and click the Visibility icon below it.
Change the Initial visibility to Hidden and select the check box next to Visibility can be toggled by another report item.

Tip: Intuition might tell you to set visibility on the text box or column, but in order to provide drilldown functionality, we must set visibility on the group we want to hide.
In the Report item drop down box that becomes enabled below it, enter TextBox3 so that at run time, users can click the plus sign next to the year to view the quarterly detail.
Click the Accept button in the lower right corner to close the smart panel.
From the Data Explorer, drag the Month field to the Quarter cell and move the cursor down slightly so that the bar appears at the bottom edge of the cell before dropping it. This adds a cell below the Year cell and automatically adds a Month grouping to the Column Groups for the matrix.
By default, the Quarter cell is named TextBox5. If you have changed it, make note of the new name.
Right-click the grey column handle above the Year cell and select Properties to open the Matrix Data Region smart panel.
On the Column Groups page, select the Matrix1_Month group, and click the Visibility icon below it.
Change the Initial visibility to Hidden and select the check box next to Visibility can be toggled by another report item.
In the Report item drop down box that becomes enabled below it, enter TextBox5 so that at run time, users can click the plus sign next to the quarter to view the monthly detail.
Click the Accept button in the lower right corner to close the smart panel.

To add subtotals to the matrix

Without subtotals, viewing the report at run time shows data for each year, or if expanded, for each quarter or month. Adding subtotals displays totals for each group in a new column to the right of the group.

Right click the Year cell and select Subtotal. A new column appears to the right with the text Total and a green mark at the top right corner. (Clicking the green mark displays the MatrixSubtotal1 properties in the Property grid.)
Select the new Total cell and in the report formatting toolbar click the Justify Center button to center the total over the information below it.
Click inside the cell and change the text to Grand Total.
Right click the Quarter cell and select Subtotal.
Select the new Total cell and in the report formatting toolbar click the Justify Center button to center the total over the information below it.
Click inside the cell and change the text to an expression that displays the year and Total:
```
=Fields!Year.Value & " Total"
```
Right click the Month cell and select Subtotal.
Select the new Total cell and in the report formatting toolbar click the Justify Center button to center the total over the information below it.
Click inside the cell and change the text to an expression that displays the quarter and Total:
```
=Fields!Quarter.Value & " Total"
```

To improve the appearance of the report

If you preview the report at this point, you will notice that although all of the data shows correctly, it is very difficult to look at when you start drilling down into the data. This can be improved with background colors and borders.

To display the name of each month instead of a number:
- Select the textbox that contains the month field and change the expression to read:
```
=Fields!SaleDate.Value
```
  Note: Changing the textbox value does not affect the grouping value, so the data is still grouped by month.
- In the property grid, enter MMMM for the Format property.
Holding down the Shift key, select the two textboxes in the top row containing the Year and Grand Total cells and make the following changes:
- In the property grid, change the BackgroundColor property to Gainsboro.
- Set the BorderStyle property to Solid. This draws a border line around the groups that defines their limits when expanded at run time.
Select the two textboxes in the second row containing the expressions =Fields!Quarter.Value and =Fields!Year.Value & " Total" and make the following changes:
- In the property grid, change the BackgroundColor property to LightSteelBlue.
- Set the BorderStyle property to Solid.
Holding down the Shift key, select the two textboxes in the third row containing the month and quarterly total cells and make the following changes:
- In the property grid, change the BackgroundColor property to AliceBlue.
- Set the BorderStyle property to Solid.
Holding down the Shift key, select the two textboxes in the fourth row containing the store number and detail cells and make the following change:
- In the property grid, set the BorderStyle property to Solid.
To add a page header to the report:
- Click below the report to give it focus and from the Report menu, select Page Header.
- From the toolbox, select Textbox and draw a textbox onto the page header section to span the entire width of the report.
- Set the TextAlign property to Center and the FontSize property to 14pt.
- Click inside the textbox and enter the text Sales by Store.
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

Click the Preview tab of the report designer.

To view the report at run time

Add the ReportPreview control to your Visual Studio toolbox and drop it onto your Windows form.
Set the Dock property of ReportPreview1 to Fill so that it will automatically resize if the form is resized at run time.

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 & "..\\..\\SalesByStore.rdlx") 
Me.ReportPreview1.OpenReport(rpt)

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

Run the project.

Data Dynamics Reports Online Help
Walkthrough: Matrix Data Region
See Also Support Forum

Creating a Data Dynamics Report

Connecting the report to a data source

Adding a dataset

Adding controls to contain data

Viewing the report

See Also