SAI430—iLab Week 6: Reports
8 Given a set of business requirements, identify and implement the necessary reporting functions and output to meet the needs of both management and systems support.
Introduction and Rationale
What exactly is a report? In the business world, a report is a physical or electronic document that conveys some type of information which customers, employees, or managers can use to make decisions about the business. For example, a manager may see a daily list of inventory so they can reorder products that have been selling well. Another common example is a sales receipt, like one we all get at the grocery store. A receipt is a report that summarizes your purchases at a particular store. Your bank statement is yet another report that details your monthly financial transactions. Consider, too, the way reports are given to users. A receipt is generally printed on paper, whereas a bank statement may be displayed as a webpage online.
There are many ways in which to generate reports. The basic method is to write software from scratch that puts the information into a desirable format. The other method would be to buy software specially designed by third-party companies for providing reports—such as Crystal Reports (http://www.crystalreports.com/). No two companies have the same reporting requirements, so for this lab we will be creating a simple HTML report that is viewable through your web browser and would be appropriate for posting on a website. Regardless of the simplicity of creating an HTML report, the concepts are similar no matter what the business.
One more concept you should “take away” about reports: Reports are pre-designed by programmers for stakeholders to use in conducting or understanding the business. Generally, the users of the reports do not have the expertise to create or modify the reports themselves. Instead, they ask the programmers to create new reports or edit them as needed. Alternately, some applications may provide raw data that trained users can import into Excel or even Access and then generate their own reports. This would be similar to the way you loaded CSV data into Excel in earlier iLabs. Exact requirements will be specified by the stakeholders.
For this iLab, it is suggested that you leave the database as it was following iLab 5. iLab 5 will have added a few product and provided ten lines of errors in the errorlog table. If you are unsure what state it is in, you can go to Omnymbus and execute the following two SQL queries.
SELECT * FROM item;
SELECT * from errorlog;
If it is not in the post iLab 5 state, you can reset the database, and then run iLab 5’s software twice to recreate to proper state.
Resetting the database before testing iLab 6
If not already in the post-iLab 5 state, before you begin this lab, you should reset the database to a new Week 5 staring condition and then run iLab 5 software two times. This should update the item table and add ten rows to the errorlog table.
To reset your database back to the Week 5 state—rerun the SQL script provided in Week 5: Omni_InventoryDBWk5.sql. You can do this as often as you need. The script has been provided in the zip: SAI430_W5_iLab_Extras.zip located in Document Sharing. This script is run from within the Omnymbus MySQL server. Refer to Figure 1.
Figure 1—Resetting original database state by rerunning setup script
Follow this up by running the Week 5 software twice.
For Week 6, you will create a Visual Studio C#.NET program that will create three HTML reports. These reports are:
1. All inventory
2. All errors from the error log
3. All inventory with less than 100 quantity on hand.
While in the real world these reports might be placed on a website; for this lab you will simply store these HTML files to disk and then open them in your browser to read them. The program workflow is shown in Figure 2. The database ERD is shown in Figure 3.
Figure 2—Generating reports for stakeholders
Figure 3—Inventory Database ERD with errorlog table
All Inventory Report
The All Inventory Report should look like the one shown in Figure 4. It shows the same information as was collected by the earlier CSV file in iLab 2. It should be saved to a file named: AllItems.html
Figure 4—Inventory Listing Report
Error Log Report
This report will display the error log as shown in Figure 5. The file should be named ErrorLog.html.
Figure 5—Error Log Report
This report displays the items that need to be reordered, as they have 100 or fewer quantity on hand (see Figure 6). The file should be named ReOrder.html.
Figure 6—Inventory Items to Reorder Report
This program is very similar to the one used in Week 2 to save the table data to a CSV file. However, now that we are using classes to store data in the database, we’ll also use a Reports class to extract that data and report it. The Main program therefore becomes quite simple (don’t forget to add the appropriate “using” statements at the top):
Things to NOTE:
1. The Reports class contains at least three static methods. Do you remember what “static” means? It means that the methods such as AllInventory do not need to be instantiated before they can be used. This is similar to how we can use the Math.Sqrt(25) method to find the square root of 25 without first instantiating the Math class. Generally, if a class is used to store data, as with the items in iLabs 3, 4, and 5, we need to instantiate a class into an object to hold the data. But if a class is being used to house tools, like the Math class holds mathematical functions, we use the methods without needing to instantiate the class. We don’t need more than one. However, this assumes that the creator of the class defined the methods as “static.”
2. The Reports class needs the database connection so that it can query the database.
3. The Reports class needs the filepath so that it knows where to write the files.
4. We could pass in the filename as well. This would allow us to use the Main to pick the filename based upon a user request.
The Report class with the AllInventory method is shown below. Notice how the program generates actual HTML code and sends it to the file defined by the TextWriter class and stored to the webpage file. Essentially, we’re printing directly to a file rather than the monitor.
Things to NOTE:
1. You may need to refresh your HTML knowledge.
2. You can easily add a CSS file to the header and nicely format this report. This CSS file can be shared between all of your reports.
3. A simple table was used here. More work can be done in making the table look nice—or perhaps you could use a different display mechanic?
4. Could this program generate an ASP.NET or PHP page instead?
You must design and code the Reorder() and ErrorLog() methods and add them to the Reports class.
For iLab 6, you will submit the following deliverables to the course Dropbox in a single ZIP file:
1. Name your ZIP archive LastName_FirstName_W6_iLab.zip
2. Copy and paste your entire C#.NET program into a Word document. Add this document to the ZIP file. Your teacher may request that you include your entire Week 6 project in the ZIP file. If so, you will need to revisit the Student Resources tab to learn about transferring files from CITRIX to your local PC. You should have at least two C# files including: Your main program and your Reports class.
3. Include all three of your report HTML files.
Make sure you close and save your documents before adding them to the ZIP file. This will ensure that the latest version of the files are uploaded.
iLab Deliverables Description Points
Item 1 Document header and comments in the code. 5
Item 2 Well written and functional C#.NET program Main. 5
Item 3 Well written and functional Report class. 10
Item 4 AllItems.html file 10
Item 5 ErrorLog.html file 10
Item 6 ReOrder.html file 10
Total Points 50
Course Project Tie-in
How do we get the proper information to the right people? In Week 6, research the various reporting products available. For class, we will be using a very simple system for reporting (abilities built into Visual Studio). Actual systems are quite expensive and specific to certain needs. Compare and contrast several of the reporting products available that interface with MySQL and/or Visual Studio. Make a recommendation on reporting software or on whether you should design your own. Perhaps your recommendation would be a combination of both.
There is nothing to turn in for the Course Project until Week 8, but your team must not get behind. The final paper is fairly long. To stay on track, complete the Week 6 portion of the Course Project.