C# Tutorial: Creating a report using a Date Picker

[Parabola]

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.


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:

private void LowMarginReportForm_Load(object sender, EventArgs e)
        {
            int Month = DateTime.Now.Month;
            int Year = DateTime.Now.Year;
            DateTime useDate = new DateTime(Year, Month, 1, 0, 0, 0);
            endDateEdit.DateTime = useDate;
            Month--;
            if(Month == 0)
                Month = 12;
            useDate = new DateTime(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.


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:

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

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.


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:


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:

        private void btnRun_Click(object sender, EventArgs e)
        {
            this.LowMarginTableAdapter.Fill(this.marksDataSet.LowMargin, endDateEdit.DateTime, startDateEdit.DateTime);
            this.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:

        private void reportViewer1_Load(object sender, EventArgs 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)

            this.reportViewer1.RefreshReport();

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

        public LowMarginReportForm()
        {
            InitializeComponent();
            this.reportViewer1.ZoomMode = Microsoft.Reporting.WinForms.ZoomMode.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

[WingedPanther]

Very nicely done! +rep

[MathX]

Cool! +rep

[Jordan]

Lots of information, well presented. +rep!

[Parabola]

thank you guys, much appreciated. Like I said, was my first time ever writing a tutorial for such things (I've written others, like setting up multiple monitors, Stereo 3D for games, etc...)

[proggcat]

great work, keep it up