Click here to Skip to main content
11,705,610 members (53,349 online)
Click here to Skip to main content
Add your own
alternative version

One to Many Reports with VS.NET 2005 (2.0) Report Designer

, 9 May 2006 CPOL 317.9K 2.8K 134
A tutorial on how to create a hierarchical (1:m) RDLC report in Visual Studio .NET 2005 (local mode).
onetomanyrdlcreport.zip
OneToManyRdlcReport
Documentation
AddFilter.jpg
ConfigureTableAdapter.jpg
EditDataSet.jpg
FilteredReport.jpg
My Project
Application.myapp
Settings.settings
NorthwindDataSet.xsc
NorthwindDataSet.xss
OneToManyRdlcReport.vbproj.user
Report1.rdlc
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
    <title>Untitled Page</title>
</head>
<body>
<UL class=download>
<LI><A href="http://www.codeproject.com/dotnet/1tomanyreports_vsnet2005/OneToManyRdlcReport.zip">Download demo project - 59 Kb</A> </LI></UL>
<H2>Introduction</H2>
<P>One of the most common types of reports is a master detail report. The following tutorial provides the most straightforward method for creating this type of a report in Visual Studio 2005. We will be using the Access NorthWind database as our data source. If you do not have the NorthWind database you can download it <A href="http://www.microsoft.com/downloads/thankyou.aspx?familyId=C6661372-8DBE-422B-8676-C632D66C529C&amp;displayLang=en" target=_blank>here</A>.</P>
<P><B>Note</B>: SQL Server 2005 Reporting Services (<I>.rdl</I>) uses a similar method for creating master detail groupings.</P>
<H3>1. Add a new report to the project</H3>
<UL>
<LI>Right click the project in the Solution Explorer. 
<LI>Select Add/New Item. 
<P><IMG height=440 src="http://www.codeproject.com/dotnet/1tomanyreports_vsnet2005/457867o.jpg" width=347></P>
<LI>Select Report: 
<P><IMG height=331 src="http://www.codeproject.com/dotnet/1tomanyreports_vsnet2005/457868o.jpg" width=540></P>
<LI>Name the report and click Add. </LI></UL>
<H3>2. Create the DataSource and TableAdapter for the report</H3>
<UL>
<LI>Open the Data Sources window. 
<P><IMG height=93 src="http://www.codeproject.com/dotnet/1tomanyreports_vsnet2005/458276o.jpg" width=274></P>
<LI>Click the Add New Data Source button (top left). 
<LI>Select Database. 
<LI>Select New Connection... 
<LI>Change the DataSource to Microsoft Access Database File. 
<LI>Select the NorthWind database: <I>C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb</I>. 
<LI>Test Connection and click OK when successful. 
<LI>Click Next. 
<LI>Click Next ("Save the connection string to the application configuration file"). 
<LI>Choose Views, then check the "Sales by Category" view. <B>Note</B>: When doing this for real, you will need to create a flat view in your data source that provides data with the top level information repeated on each line. This sample view has been created in this manner. 
<P>Example:</P><PRE lang=sql>SELECT * FROM Main JOIN Detail ON Main.MainID = Detail.MainID
"Main Info, Detail1"
"Main Info, Detail2"
"Main Info, Detail3"</PRE>
<LI>Click Finish. The NorthWindDataSet should now appear in your Data Sources window. 
<P><IMG height=147 src="http://www.codeproject.com/dotnet/1tomanyreports_vsnet2005/458340o.jpg" width=217> </P></LI></UL>
<P>There are three main ways to create a hierarchical report using grouping. This example will use the "Master List Detail Table" method. The other two methods are "Master List Detail List" and "Single Table Master Detail". An alternate method to using groups is to use sub-reports.</P>
<H3>3. Add the master list</H3>
<UL>
<LI>Open your Toolbox. 
<LI>In the Report Items section, drag a list onto your report. (This will be the master list.) 
<P><IMG height=175 src="http://www.codeproject.com/dotnet/1tomanyreports_vsnet2005/458406o.jpg" width=450></P>
<LI>Open the Data Sources Window. 
<LI>Drag the CategoryID and CategoryName fields into the list (Master section). 
<LI>Right click the list and select Properties. 
<LI>Click "Edit Details Group". 
<LI>In the "Group on" section, select the master record's ID field, CategoryID in this case. 
<LI><I>Optional:</I> In the document map label field, select the master record's description field, CategoryName in this case. This will provide a clickable tree in the report viewer to switch between master records. 
<LI><I>Optional:</I> Click page break at the end. This will put page breaks between each master record. 
<P><IMG height=442 src="http://www.codeproject.com/dotnet/1tomanyreports_vsnet2005/458418o.jpg" width=482></P></LI></UL>
<H3>4. Add the detail table</H3>
<UL>
<LI>Drag a table from the Report Items toolbox section into your master list. 
<P><IMG height=253 src="http://www.codeproject.com/dotnet/1tomanyreports_vsnet2005/458421o.jpg" width=540></P>
<LI>Right-click the top left corner of the table and select Properties. 
<LI>Set the DataSet name to the NorthwindDataSet_Sales_by_Category table. Some clarification: the report designer refers to a result set as a "DataSet". This is not the same as a .NET Framework "<CODE>DataSet</CODE>". The report designer's "DataSet" more closely relates to a .NET <CODE>DataTable</CODE>. 
<LI>Select the Groups tab. 
<LI>Click Details Grouping... <B>Note</B>: In a single table master detail report, you would instead click "Add...". 
<LI>Group on each detail column. In this case: ProductName and ProductSales. 
<P><IMG height=495 src="http://www.codeproject.com/dotnet/1tomanyreports_vsnet2005/515295o.jpg" width=499></P>
<LI>Click OK. 
<LI>Click OK. 
<LI>Drop the ProductName field into the Detail (middle) row of the first column in the table. 
<LI>Drop the ProductSales field into the Detail (middle) row of the second column in the table. 
<P><IMG height=241 src="http://www.codeproject.com/dotnet/1tomanyreports_vsnet2005/458451o.jpg" width=515></P>
<LI><I>Optional:</I> Resize the parent list textboxes, bold the table's column headers, and add another textbox label for the ID. 
<LI><I>Optional:</I> Drag the Product Sales column from the Data Sources into the Detail table's Product Sales Footer row. 
<P>Note: VS automatically inserts the Sum aggregate function which will provide a subtotal of the Products within the Parent category.</P>
<LI><I>Optional:</I> Prepend some descriptive text ("Total: " in this case) into the summary Footer record and format the Detail and Footer values as currency. <PRE lang=text>Detail:  = FormatCurrency(Fields!ProductSales.Value)
Footer:  = "Total: " &amp; FormatCurrency(Sum(Fields!ProductSales.Value))</PRE>
<P><IMG height=242 src="http://www.codeproject.com/dotnet/1tomanyreports_vsnet2005/515444o.jpg" width=477></P></LI></UL>
<H3>5. Create the report form</H3>
<UL>
<LI>Open <CODE>Form1</CODE> from the Solution Explorer. 
<LI>Drop a <CODE>ReportViewer</CODE> onto the form (from the data section of the Toolbox). A dialog should appear. If this dialog disappears, use the triangle button on the top right border of the <CODE>ReportViewer</CODE> to see it again. 
<LI>Select the report from the Choose Report combo. 
<LI>Click Dock in parent container to fill it to the form. <B>Note</B>: This adds the <CODE>DataSet</CODE>, <CODE>TableAdapter</CODE>, data source, and adapter code to fill the adapter. 
<P><IMG height=131 src="http://www.codeproject.com/dotnet/1tomanyreports_vsnet2005/458463o.jpg" width=540></P></LI></UL>
<P>Run the project.</P>
<P><IMG height=340 src="http://www.codeproject.com/dotnet/1tomanyreports_vsnet2005/458477o.jpg" width=467></P>
<P>With Optional Formatting:</P>
<P><IMG height=349 src="http://www.codeproject.com/dotnet/1tomanyreports_vsnet2005/515446o.jpg" width=587></P>
<H2>Advanced</H2>
<H3>1. Filter the results (<code>Where</code> clause)</H3>
    <ul>
        <li>Right-click the DataSet in the Solution Explorer and select <code>View Designer</code>
            <br />
            <img height="367" src="EditDataSet.jpg"
                width="260">
        </li>
        <li>Right-click the TableAdapter section of the DataTable and select <code>Configure...</code>
            <br />
            <img height="156" src="ConfigureTableAdapter.jpg"
                width="338">
        </li>
        <li>Modify the SQL statement to include the Where clause. For SQL server you would use
            a named parameter <code>@CategoryId</code>. OleDb Adapters require <code>?</code>
            parameters.
            <br />
            <img height="515" src="AddFilter.jpg"
                width="600">
        </li>
        <li>Click Finish</li>
        <li>Add appropriate controls to your form to provide the parameter value.</li>
        <li>Move the code to fill the adapter and refresh the report from the Form Load event
            to the event that will load the report. Ex: "Go" button Click event.</li>
        <li>Provide the required parameter in the Adapter's Fill method.
            <br />
            <img height="229" src="FilteredReport.jpg"
                width="600">
        </li>
    </ul>

<H2>Conclusion</H2>
<P>Please comment on anything that needs further clarification or with any questions on implementing this. Also, please provide suggestions before rating the article less than a 5.</P>

</body>
</html>

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Robert R Freeman
Chief Technology Officer Kiefer Consulting
United States United States
Sacramento, CA based Senior .Net and SharePoint Solution Architect for Kiefer Consulting
(1-800-794-1928)
B.S. in Mathematics from UCDavis
.NET Wizard - Experts-Exchange
MCSD, MCTS: MOSS 2007 Config

Some of the bigger questions:
1. What is the meaning of my life?
To satisfy the purpose of your creator(s). (Meaning must derive from purpose. Those who create you, give you meaning.)

2. Who is my creator?
Ultimately, God is your creator. God designed and created the universe and everything in it. You and others in your life can also be a part of your creation, overriding or furthering God's purpose.

3. What is God's purpose for me?
To love and be loved by your creator and others and to enjoy the life you've been given.
This can be distinguished two ways.
a. Use your built in common sense (morality/feelings)
b. Use the creator's handbook. Fortunately our creator did not abandon us. He is with us now and even lived and died as one of us. Check out his biography in "The Bible"

Note on free-will vs. predetermination:
God exists outside the constraints of time. He exists at every point in time simultaneously and knew of your birth and every decision you will/have made. But this does not mean God predetermined any of it. Pre and post are time related concepts that do not apply to God. God always has been and always will be. He determines our universe to exist, gives it the parameters of natural law, and allows us to make our own way through it (free-will). Note that these are all present tense, it would be more appropriate to use past, present, and future tense combined. God's purpose is for us to love him and one another. But a prerequisite of love is the free-will to love. So even though God wants us to love him/others, he can not ensure it. He can, however, help us if we allow him to. A miracle is God modifying natural parameters in response to human will.

You may also be interested in...

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150819.1 | Last Updated 9 May 2006
Article Copyright 2005 by Robert R Freeman
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid