CIS 338: VB.NET Printing

CIS 338: VB.NET Printing Dr. Ralph D. Westfall May, 2011

Crystal Reports • recognized as leading product for printing and formatting reports • better reporting than the earlier versions that came with VB6, VB.NET 2003, 2005 • Crystal Reports 10 version is packaged with Visual Studio.NET 2008 • May need to download free version that works with VS 2010

Installing Crystal Reports • if didn't do so originally, need to add Crystal Reports to your Visual Studio.NET installation • might also need to reinstall if you upgrade from VS 2005 to VS 2008? • Start>Settings>Control Panel> Add/Remove Programs>Microsoft Visual Studio.NET>Visual Studio.NET>Add or Remove Features>etc.

Get Database and Project • download and unzip Proj3Web.zip file • create a new folder on your desktop • copy and paste the Nations.mdf and.ldf files from the unzipped folder into C:\cis338

Creating a Project • start VB.NET • should see Crystal Reports icon on startup screen • create a new Web (or Windows) project • Web>ASP.NET Web Application • or File>New Project>Visual Windows Form Application

May Need to Attach Database • open SQL Server Management Studio • save Server Name from login window • Connect>right-click Databases>Attach> Add>select .mdf file in new folder>OK>OK • expand Databases and the database you will be using • right-click table that you will be using> make sure that table has a primary key>close the deign window

Add a Report Designer • Project>Add New Item>Reporting> Crystal Report, rename it>click Add • (1st use) accept the license terms>OK • with Using the Report Wizard selected, Standard Report highlighted>OK • expand Create New Connection>expand OLE DB (ADO)>select Microsoft OLE DB Provider for SQL Server>Next>

Add Your Database • check Integrated Security:>Server: (local)\SQLEXPRESS>Database: C:\cis338\Nations.mdf>Next>Finish • select Add Command (in Available Data Sources)>click on angle bracket pointing right>(moves to new window) • type in the following SQL query: select ID, Country, Gold, Pop, Currency, Capital from nations2, currencies where nations2.CID=currencies.CID>OK>Next

Identify Outputs and Groups • expand the command and use the double angle bracket to send all the fields to the right • or select fields and use the single angle bracket to send individual fields>Next • add a field to Group By: that isn't unique so it will have more than one item in its groups (such as Gold or Currency)> change sort order if want>Next

More Options • could select Sum of command.Pop and then select a summarization in ListBox, such as Sum or Average • return other groupings to left>Next • could choose to group by Top 5 or Bottom 5 • sum of command.Pop • could choose a charting option>Next

Even More • could filter by values in any field • could select database values in ListBox or type in value e.g. Pop >=1.0 >Next • review the Report Styles by selecting some and viewing Preview until find one that looks best>Finish

Add a Report Viewer • Project>Add New Item>Web>Web Form>Add • Find Crystal Reports Viewer on ToolBox in Reporting section and drag onto Form • in a Windows project (not ASP), could set Anchor property to link to all four corners of form, so user can resize report

Add Report Viewer - 2 • Specify the ReportSource as the report you just created>OK, or • double-click form to create start of Form_Load code, and add code: Imports CrystalDecisions.Windows.Forms Dim [instance] As New [report object]() [report viewer].ReportSource = [instance] [report viewer].ShowFirstPage( ) 'Notes

Test Report Viewer • run project with web form on screen • can use navigation at top to scroll through Windows form report • review output for possible design changes • note buttons at top • Print Report, Search Text, etc. • Export Report (various file types/formats) • Toggle Group Tree

Modifying Report • in report designer, right click on some white space (not an object) in form> Insert>Text Object>move box to where you want it and type text in it • select fields to widen them and/or move them to better locations • right-click redundant fields>Delete • Format Object on pop-up menu

Changing Report Options • double click the report icon in Solution Explorer to view designer • right click on white section of designer, then click Report • various options allow you to modify previous choices • Report Options allows you to set NULL values to defaults

Fixing NULL Fields in Database • in SQL Server Management Studio, right-click database name>New Query • query can be used to get data (slide 8) • type 1st query below, click the Execute button, then use 2nd query to verify: • update [table name] set [field] = [replace with] where [field] is null • select * from [table name] where [field] is [replace with]

Adding Special Fields • Crystal Reports>Field Explorer • can drag and drop fields such as Print Date, Print Time, Page Number, etc. onto various areas of screen

Open/Show Multiple Forms • Project>Add New item>etc. • follow the preceding steps to create a Crystal Report on the new form, or some other form • put code in a form to select and show the other form (is or loads from Startup object) Private Sub Button1_Click(ByVal sender etc. Response.Redirect("[report file].aspx") 'ASP Form2.Show() 'Windows End Sub

CIS 338: VB.NET Printing