C# Tutorial: Creating a report using a Date Picker

First off, this is my first tutorial. I have only been using VS and C# for about a week, so if you have any suggestions, I am always open to constructive criticism.

In this tutorial, I am going to show how to create a report (mainly for business use). In it, I will demonstrate other useful tasks such as formatting columns, using MicrosoftSQL Queries, pre-filling DateTime Picker with variable dates, and using DataSets, plus possibly a couple other tasks.

What I am using:
VS2008 SP1
Microsoft SQL Database
Microsoft .NET Framework 3.5 SP1
DX 8.3
---------------------------------------------------------------------------------------------

Ok, to begin, of course, open a new project. Select Windows Form Application under Visual C# > Windows. Name it as you want, but remember, some lines of code will change depending on the names, so pay attention. I'm calling mine "Low Margin Invoices Report", as that is what it will be.

Go ahead and rename Form1. I'm naming mine LowMarginReportForm. We can go ahead and change the Text for this form. I'm using "Low Margin for Invoices Report." Resize it as well, using (724, 610). Done with this part, so go ahead and unpin the properties tab if you haven't already.

Now open your toolbox, and pin it.
Under 'DX.8.3: Navigation & Layout', there is an item labled 'SplitContainerControl'. Go ahead and add that to your form. If you do not have DX.8.3 tools, there is a substitute under 'Containers' called 'SplitContainer'. Now click the small arrow in the upper right corner of the control, and dock it in the parent.
Posted Image

Now in the Controls properties, change the 'Horizontal' value to False.
Open your Toolbox again. We need to add (from DX.8.3: CommonControls) two DateEdit's, and a SimpleButton. Place them in a row in the top of the form. Rename the first one (on the left) to startDateEdit, and the second to endDateEdit. Also, let's name the button to btnRun. Go ahead and add some labels for the DateEdits, "Start Date: " and "End Date: "

Now we need to preformat these DateEdits. Double click the title bar of your form, and this will bring you to your code.
Change this section where it loads the form to look like this:

[COLOR=Blue]private void[/COLOR] LowMarginReportForm_Load([COLOR=Blue]object [/COLOR]sender, [COLOR=LightBlue]EventArgs [/COLOR]e)
        {
            [COLOR=Blue]int[/COLOR] Month = [COLOR=LightBlue]DateTime[/COLOR].Now.Month;
            [COLOR=Blue]int[/COLOR] Year = [COLOR=LightBlue]DateTime[/COLOR].Now.Year;
            [COLOR=LightBlue]DateTime [/COLOR]useDate = [COLOR=Blue]new[/COLOR] [COLOR=LightBlue]DateTime[/COLOR](Year, Month, 1, 0, 0, 0);
            endDateEdit.DateTime = useDate;
            Month--;
            [COLOR=Blue]if[/COLOR](Month == 0)
                Month = 12;
            useDate = [COLOR=Blue]new[/COLOR] [COLOR=LightBlue]DateTime[/COLOR](Year, Month, 1, 0, 0, 0);
            startDateEdit.DateTime = useDate;
        }

As you can see, we are finding out the current Month and Year, and using these to preset the DateEdit values as the previous month. The DateTime properties, for reference, are as such: DateTime(<year>, <month>, <day>, <hour>, <minute>, <second>);

Let's head back to the design of the form. In your toolbox, under Reporting:, add a Microsoft Report Viewer. Drag this into the bottom of your form, and dock it.
Posted Image

Now, we need a DataSet. So, in your Data Sources tab, click Add New Data Source.... We're going to go with a Database. Now on this one, I can't tell you the name or the server, that depends on you. Get your server entered here, and click next. Now we will name the connection string. Mine is marksConnectionString. Click next. In this screen, we will not select anything at the top, as we will be using a SQL query to get our data, however, you could select the data from this view. At the bottom, notice the name for your DataSet. Mine will be marksDataSet. Click finish, and click yes when told nothing is selected.

In your solution Explorer, double click your Data Set. Go to your toolbox, and add a TableAdapter. Make sure your connectionString is selected, click next. In this screen, we will make sure Use SQL statements is selected, then click next again.

Now for this part, it's best to have already built your SQL query. Here's what I will be using:

[COLOR=Blue][B]SELECT[/B][/COLOR]     h.InvoiceNumber, h.InvoiceDate, h.CompanyName [B][COLOR=Blue]AS[/COLOR][/B] Company, [COLOR=Blue][B]SUM[/B][/COLOR](l.Price) [B][COLOR=Blue]AS[/COLOR][/B] ProductSales, [B][COLOR=Blue]SUM[/COLOR][/B](l.Cost) [B][COLOR=Blue]AS[/COLOR][/B] CostofGoods, ([B][COLOR=Blue]SUM[/COLOR][/B](l.Price) 
                      - [B][COLOR=Blue]SUM[/COLOR][/B](l.Cost)) / [COLOR=Blue][B]SUM[/B][/COLOR](l.Price)  [B][COLOR=Blue]AS[/COLOR][/B] GPPCT
[COLOR=Blue][B]FROM[/B][/COLOR]         invoices_saleshead [B][COLOR=Blue]AS[/COLOR][/B] h INNER JOIN
                      invoices_salesline [B][COLOR=Blue]AS[/COLOR][/B] l [B][COLOR=Blue]ON[/COLOR][/B] h.InvoiceNumber = l.InvoiceNumber
[COLOR=Blue][B]WHERE[/B][/COLOR]     (h.CompanyName <> [COLOR=DarkOrange]'Backfill Data'[/COLOR]) [B][COLOR=Blue]AND[/COLOR][/B] (h.ProductSales <> [COLOR=Magenta]0[/COLOR]) [B][COLOR=Blue]AND[/COLOR][/B] (h.InvoiceDate <= [COLOR=DimGray]@EndDate[/COLOR]) [COLOR=Blue][B]AND [/B][/COLOR](h.InvoiceDate >= [COLOR=DimGray]@StartDate[/COLOR])
[COLOR=Blue][B]GROUP BY[/B][/COLOR] h.InvoiceNumber, h.InvoiceDate, h.CompanyName
[B][COLOR=Blue]HAVING      [/COLOR][/B](([B][COLOR=Blue]SUM[/COLOR][/B](l.Price) - [B][COLOR=Blue]SUM[/COLOR][/B](l.Cost)) / [B][COLOR=Blue]SUM[/COLOR][/B](l.Price) <= [COLOR=Magenta]0.30[/COLOR])

For those new to SQL, I will try to explain what I can. I am new to SQL as well, with about a week of experience in it.
SELECT: this is where we tell the query what fields (columns) we want. As you can see, we can use aggregate functions to define certain columns. The AS statement basically defines these the way you might define a variable. FROM tells it what tables to use. WHERE gives it certain conditions. This is where the EndDate and StartDate come in. In Microsoft SQL, if you do NOT use a column in an aggregate function, it must be in the GROUP BY. HAVING is much like the WHERE statement, except you cannot use aggregate functions in the WHERE statement. As this is a Low Margin report, I have told the Query to only show invoices with a 30% or less margin.

Now rename the TableAdapter, mine is LowMarginTableAdapter, and the table, LowMargin.
Under the TableAdapter, where it says Fill,GetData, right click and select properties. In the properties tab, under parameters, click the ellipses (...), and we can see the parameters, StartDate and EndDate. On both, make sure dbType is set to DateTime, and ProviderType is SmallDateTime.
Posted Image

Now go back to your form. For the reportViewer, click the arrow in the top right, and click design a new report. Select your table, click next, and next again. Add all the columns under the details section here, then hit next. Keep hitting next, and on the last screen, we need to name the report. I'm naming mine LowMarginReport. Click finish. Now we need to format the reports fields, so data is displayed properly. The first field, Invoice Number, I can leave alone. The Date column, however, needs to be formatted, or you will get a full date AND time. So change the value to this:

=Format(Fields!InvoiceDate.Value, "Short Date")

Company we can leave alone.
Product Sales needs to be changed since it is in currency, so we have:

=FormatCurrency(Fields!ProductSales.Value)

Same with CostofGoods

=FormatCurrency(Fields!CostofGoods.Value)

Now the GPPCT, needs to be formatted as a percent.

=FormatPercent(Fields!GPPCT.Value,0)

Redo the text in the report to make it look nice, resize some, and we have this:
Posted Image

Now return to your form. Go back to the reportViewers menu, and this time select your report from the dropdown.
Double click your Run Report button to get to its code, and make it look like this:

        [COLOR=Blue]private void[/COLOR] btnRun_Click([COLOR=Blue]object [/COLOR]sender, [COLOR=LightBlue]EventArgs [/COLOR]e)
        {
            [COLOR=Blue]this[/COLOR].LowMarginTableAdapter.Fill([COLOR=Blue]this[/COLOR].marksDataSet.LowMargin, endDateEdit.DateTime, startDateEdit.DateTime);
            [COLOR=Blue]this[/COLOR].reportViewer1.RefreshReport();
        }

This let's the form know what parameters to pass the query when the button is clicked, and refreshes the report.
Also make sure this is in the code for the form:

        [COLOR=Blue]private void[/COLOR] reportViewer1_Load(object sender, [COLOR=LightBlue]EventArgs [/COLOR]e)
        {

        }

Go back to the part of code where we preset the DateTimes, and make sure this line has NOT been added (as it is automatically sometimes)

            [COLOR=Blue]this[/COLOR].reportViewer1.RefreshReport();

We don't want the report to run immediately.
Also, under public LowMarginReportForm, it should look like this:

        [COLOR=Blue]public [/COLOR]LowMarginReportForm()
        {
            InitializeComponent();
            [COLOR=Blue]this[/COLOR].reportViewer1.ZoomMode = Microsoft.Reporting.WinForms.[COLOR=LightBlue]ZoomMode[/COLOR].PageWidth;
        }

That extra line of code will make the report resize itself to fit the window.

At this point, you should be done. I will attach the source for reference, but I cannot post an image of the report run, as I am on my company database, that's private information lol.

If you have any questions, feel free to ask.
Also, the reason I used this type of report is that way people could see a real life example, not a "sample". This report is actually in use by my company right now.
_________________
v source is attached v

Attached Files

LowMargin.rar 193.67K 743 downloads

Edited by Parabola, 05 August 2009 - 10:46 AM.
added some color to the code blocks..

I am new to entity framework. I have tried your approach of creating a data table for the report by creating a view on the database and importing it to the edmx model. I am stuck on translating the

private void btnRun_Click(object sender, EventArgs e)

        {

            this.LowMarginTableAdapter.Fill(this.marksDataSet.LowMargin, endDateEdit.DateTime, startDateEdit.DateTime);

            this.reportViewer1.RefreshReport();

        }

part. I tried something like:

this.LowMarginBindingSource.DataSource = marksData.LowMargin.where(endDateEdit.DateTime, startDateEdit.DateTime);

to no avail. Your approach is the best I have seen so far and if I learn how to apply it on Entity Framework, I will be a very happy soul. Also I would like to say that this is one of the most explicit and understandable tutorials I have seen. You are a very good teacher. Thank you.

Edited by Roger, 04 July 2011 - 12:36 PM.
added code tags