Go Back   CodeCall Programming Forum > Software Development > Tutorials > CSharp Tutorials
Register Blogs Search Today's Posts Mark Forums Read

CSharp Tutorials Tutorials for C#

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 08-04-2009, 01:02 PM
Parabola's Avatar
Programming Professional
 
Join Date: Jul 2009
Location: Texas
Age: 25
Posts: 299
Parabola will become famous soon enoughParabola will become famous soon enough
Send a message via AIM to Parabola Send a message via MSN to Parabola Send a message via Yahoo to Parabola Send a message via Skype™ to Parabola
Post 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.


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:
Code:
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:
Code:
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:
Code:
=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:
Code:
=FormatCurrency(Fields!ProductSales.Value)
Same with CostofGoods
Code:
=FormatCurrency(Fields!CostofGoods.Value)
Now the GPPCT, needs to be formatted as a percent.
Code:
=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:
Code:
        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:
Code:
        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)
Code:
            this.reportViewer1.RefreshReport();
We don't want the report to run immediately.
Also, under public LowMarginReportForm, it should look like this:
Code:
        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
Attached Files
File Type: rar LowMargin.rar (193.7 KB, 107 views)
__________________
Programmer (n): An organism that can turn caffeine into code.
Programming would be so much easier without all the users.

Last edited by Parabola; 08-05-2009 at 02:46 PM.. Reason: added some color to the code blocks..
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #2 (permalink)  
Old 08-04-2009, 01:46 PM
WingedPanther's Avatar
Super Moderator
 
Join Date: Jul 2006
Age: 36
Posts: 11,435
WingedPanther has much to be proud ofWingedPanther has much to be proud ofWingedPanther has much to be proud ofWingedPanther has much to be proud ofWingedPanther has much to be proud ofWingedPanther has much to be proud ofWingedPanther has much to be proud ofWingedPanther has much to be proud ofWingedPanther has much to be proud of
Re: C# Tutorial: Creating a report using a Date Picker

Very nicely done! +rep
__________________
CodeCall Blog | CodeCall Wiki | Shareware
Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #3 (permalink)  
Old 08-04-2009, 05:09 PM
MathX's Avatar
Guru
 
Join Date: Oct 2008
Location: Kosovo
Age: 19
Posts: 3,994
MathX has a spectacular aura aboutMathX has a spectacular aura about
Send a message via MSN to MathX
Re: C# Tutorial: Creating a report using a Date Picker

Cool! +rep
__________________
My Blog
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #4 (permalink)  
Old 08-04-2009, 08:01 PM
Jordan's Avatar
Administrator
 
Join Date: Nov 2005
Location: Hendersonville, NC
Posts: 24,556
Jordan is a name known to allJordan is a name known to allJordan is a name known to allJordan is a name known to allJordan is a name known to allJordan is a name known to all
Send a message via ICQ to Jordan Send a message via AIM to Jordan Send a message via MSN to Jordan Send a message via Yahoo to Jordan
Re: C# Tutorial: Creating a report using a Date Picker

Lots of information, well presented. +rep!
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #5 (permalink)  
Old 08-04-2009, 11:33 PM
Parabola's Avatar
Programming Professional
 
Join Date: Jul 2009
Location: Texas
Age: 25
Posts: 299
Parabola will become famous soon enoughParabola will become famous soon enough
Send a message via AIM to Parabola Send a message via MSN to Parabola Send a message via Yahoo to Parabola Send a message via Skype™ to Parabola
Red face Re: C# Tutorial: Creating a report using a Date Picker

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...)
__________________
Programmer (n): An organism that can turn caffeine into code.
Programming would be so much easier without all the users.
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #6 (permalink)  
Old 08-05-2009, 02:21 PM
Newbie
 
Join Date: Aug 2009
Posts: 13
proggcat is an unknown quantity at this point
Re: C# Tutorial: Creating a report using a Date Picker

great work, keep it up
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Reply

Tags
c sharp, report, tutorial, visual studio 2008



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Reporting against a domain model Kernel News 0 07-01-2009 07:50 AM
Calendar Script xXHalfSliceXx JavaScript and CSS 7 05-25-2009 01:26 PM
A date showing tutorial ahsan16 VB Tutorials 3 12-11-2008 08:33 PM
John's Java Tutorial Index John Java Tutorials 0 01-11-2007 04:05 PM
PHP:Tutorial The Date John PHP Tutorials 0 01-10-2007 07:10 PM


All times are GMT -5. The time now is 08:21 AM.


vBulletin v3.8.0 ©2010, Jelsoft Enterprises Ltd.


no new posts

LinkBacks Enabled by vBSEO 3.1.0