Hi, How add Crystal Report Version 11 in VB6. At present I have been using Crystal Ver 8. I need to upgrade to Crystal Ver 8.5 or above to implement exporting rpt's to pdf. Now, I have installed CRystal report 11 Developer edition in my system and from VB i am not able to add Crystal Report control of version 11. Can anyone pls help.
SAP Crystal is an entry-level solution that enables deeper insights to business information and positions your organization for greater competitive advantage. It combines the pixel-perfect reporting capabilities of SAP Crystal Reports with the secured delivery mechanisms of SAP Crystal Server.
Thanks, priya Hi Priya, I'm also having a simlar problem - using RDC in VB6 with CR8.5 was a breeze - but not so in CR11 - I simply can't get any Designers for CR11 in the VB6 environment.I wonder how it is possible.? I've read the Technical PDF that comes with CR11 but that too goes on saying all is possible using RDC and CR11 etc etc - but exactly how.I'm baffled - did you manage to get it done?
Please let me know how. Will aprreciate your help verty much:) Thanks. Hi, Sailesh thanks for replying.
That is one of the specific reason. And these for Priya also.
In CR XI you have Developer version only will allow you to deploy with VB. And in VB you have to follow these steps to run CRXI reports in VB 1. In VB project properties go to Designer tab and select Crystal Reports 11 and in Controls tab select Crystal ActiveX Report Viewer Liberary 11.0 2. You will get one more tool box in tools window called Crystal Reports 3. And write the code like this in the form load (or which event you want) Dim Report as New CrystalReport1 CrystalActiveXReportViewer.ReportSource = Report.Application.OpenReport( Give Report Path ) CrystalActiveXReportViewer.ViewReport 4.
Run the Application. Let me know if any queries.:cool. Hi, Sailesh thanks for replying. That is one of the specific reason. And these for Priya also. In CR XI you have Developer version only will allow you to deploy with VB.
And in VB you have to follow these steps to run CRXI reports in VB 1. In VB project properties go to Designer tab and select Crystal Reports 11 and in Controls tab select Crystal ActiveX Report Viewer Liberary 11.0 2. You will get one more tool box in tools window called Crystal Reports 3. And write the code like this in the form load (or which event you want) Dim Report as New CrystalReport1 CrystalActiveXReportViewer.ReportSource = Report.Application.OpenReport( Give Report Path ) CrystalActiveXReportViewer.ViewReport 4. Run the Application. Let me know if any queries.:cool: hi, i couldn't find Crystal ActiveX Report Viewer Liberary 11.0 in vb6 reference, can you give me the craxdrt.dll and craxddrt.dll version 11.0.
If you haven't gotten past this issue, I can sympathize with you. It took me many hours as well. The answer is actually in BizObjects Knowledgebase, but it isn't so obvious what to look for. Query their KB for ' OCX RDC' and find the technical paper ' Migrating from the OCX Control to the Crystal Reports 9 Report Designer Component (RDC)'. The RDC is essentially unchanged from versions 9 thru version 11, which is what throws many off.
Get the document and read around page 17 for sample code. I have all my reports pointing to a named data source or DSN. Your VB program must also step through and assign the DSN to each table in the report. See sample code on page 44 of the document. I have 'professional' edition?
And i read on vendor site that only developer edition can do application integration.does it make any sense? When i said about keys - it means developer key or general key - a reply mentioned in the Sailesh's reply. Hi Rajesh, Yes, I finally got the solution - actually during installation of CR11, I was using a registraion Key that was only for CR11 and not for Developer. After unistalling entrire CR11 and re-installong agagin (with Developer Key), well.things couldn't be better. Simply start VB6 IDE and things behave just as they were in the CR8.5 days. Regards, Sailesh.
I just want to share this because this works for me. Private Sub FormLoad Dim report As New CRAXDDRT.report Dim sql As String Dim crapp As New CRAXDDRT.Application Dim tbl As CRAXDDRT.DatabaseTable 'I modified the query for the report by specifying the WHERE clause sql = 'SELECT.WHERE.' With crapp 'I already set the username,password,server parameters in the DSN itself.LogOnServer 'crdbodbc.dll', 'SampleDSN' Set report =.OpenReport(App.Path & ' Report1.rpt') report.Database.LogOnServer 'crdbodbc.dll', 'SampleDSN' report.SQLQueryString = sql End With With CRViewer1.Refresh.ReportSource = report.ViewReport End With End Sub Private Sub Formresize CRViewer1.Top = 0 CRViewer1.Left = 0 CRViewer1.Height = ScaleHeight CRViewer1.Width = ScaleWidth End Sub.
You have two printing approaches with Crystal Reports:. Using the Crystal Reports report designer. This program must be used to design all reports, and it can print them as well. This means you don't need to run a Visual Basic application to print reports on data maintained by your application.
The program, however, can't be distributed to users of your application. Using Crystal Reports OLE custom control (OCX). This OCX is included in the package and can be dropped into a Visual Basic form to control report printing. The control gives your application access to most of the more useful features of the Crystal Reports program. With this control, you can have the report displayed in a Print Preview window, printed directly to a printer, or exported to a file. Data Reports integrates very well into the Visual Basic environment. The reports are generated directly in the Visual Basic IDE.
There are no separate report files, and the report definitions are stored in an ActiveX designer form with the Visual Basic project. This means when a project is compiled, the report definition is compiled within the executable file (EXE). The report retrieves the data to be used from a Data Connection within a Data Environment. This enables the report to retrieve data from this powerful new feature in Visual Basic 6. Crystal Reports takes a totally different approach to creating reports to use with Visual Basic. A separate application creates the report definitions, and these reports are stored in a separate.RPT file that must be distributed with the EXE file. Also, additional DLLs and an OCX control must be included for the Visual Basic application to use a Crystal Reports report.
The distribution requirements are therefore much heavier than those for Microsoft Data Reports. Furthermore, a Crystal Reports report uses ODBC to connect to a data source, not to the new powerful Data Environment. Crystal is a mature and proven product. It might be heavier than Data Reports, but it offers a richer feature set and has upgrade options for scalability. CRYSTAL REPORTS UPGRADE OPTIONS Crystal Reports Professional 6 This version of Crystal Reports includes an updated feature set to Crystal 4.6.1, which is included with Visual Basic. An updated report designer and an ISAPI module is included, which allows reports to be placed on a IIS Web server. Java, ActiveX, and pure HTML viewers can be used to display a report on the Web server.
This version of Crystal Reports enables you to export a report in various formats including HTML, MS-Excel, MS-Word, and Lotus 1-2-3. Crystal Info Crystal Info is a server-based solution that enables multiple users to view, schedule, and manipulate shared reports. It can be used as a central storage for reports, for multiple users to view. It can be used to schedule when complex and time consuming reports will be run so data can be viewed without having to run the report each time.
What SQL Server is to Microsoft Access, Crystal Info is to Crystal Reports. Crystal Report Designer This is an ActiveX designer that works in the same vein as Microsoft Data Reports. It gives you 100% functionality of Crystal Reports 6 with the Visual Basic IDE.
Take the way Microsoft Data Reports integrates into Visual Basic and add the power of Crystal Reports 6. Prior to the release of Visual Basic 6, the only version of this tool available was for Visual Basic 5, but keep an eye on the Seagate Software Web site ( www.seagatesoftware. Com) for a Visual Basic 6 version.
CONTACT Addr1 City State Zip Week Day Resident 4 Goodyear Street Boulder Colorado Monday Occupant 2291 Arapahoe Irvine California Monday Resident 8 Hazelnut Irvine California Monday Occupant 2 Orion Aliso Viejo California Sunday Medical Practitioner 1 Jenner Tacoma Washington Thursday Medical Practitioner Civic Center Anaheim California Tuesday Occupant 2 Park Plaza Irvine California 92714-271Thursday 9.1 Create a report using Microsoft Data Report Designer. Many of the design elements of a Crystal report can be changed on-the-fly in a Visual Basic application. Although there aren't enough control properties to completely change an existing report or create a new report, there are enough changeable properties available that a Visual Basic application can create entirely different reports using the same data. This How-To creates a Visual Basic application and a Crystal report that allows records to be printed in different orders while filtering the records and customizing the report heading and page numbering.
AuthorsDR form shown at runtime. Start Visual Basic and select a new Standard EXE project. Select Add Data Environment from the Project menu.
The window in Figure 9.3 will appear. Right click on the Connection1 item in the Data View window.
Select the Properties item from the popup menu. The following screen is used to set the database and connection type. This How-To will use the BIBLIO.MDB database; therefore, select the Microsoft Jet 3.51 OLE DB Provider, then click on the Next button.
Enter or select the location of the BIBLIO.MDB database; it should be in the same folder as Visual Basic. Click on the Test button to verify the connection. This handy option enables you to verify the database connection without having to write test codes. Run the project. Click the OK button.
Right-click on the Commands folder in the Data Environment window. Select the Add Command item from the popup menu. An item called Command1 will appear under the Commands folder. Right click on it and select the Properties item from the popup menu. The Properties dialog box that appears is used to select a table or query. Click the on Connection drop-down list and select Connection1. From the Database drop down select the Table object.
From the Object drop-down list, select the Authors Table. The dialog should look like Figure 9.4 after the items have been chosen. The Command1 Properties dialog box. From the Visual Basic main menu, select the Add Data Report item from the Project menu. Set the properties of the Data Report as in Table 9.5. Then, right-click on the report and select Retrieve Structure from the popup menu.
Retrieve Structure will make the sections of the report match the database connection layout. Properties for drBasic.dsr AuthorsDR. OBJECT Property Setting DataReport Name drBasic DataMember Command1 DataSource DataEnviroment1 4. The fields to be displayed can be added in two ways.
The first method is to draw the fields on the report, then assign the proper DataMember and DataField properties to them. The second method is to open the Data Environment window, and drag and drop the fields directly on the report. After a field is dropped onto the detail section of the report, the field and its caption are displayed on the report. Arrange the items as shown in Figure 9.5.
Arranged report objects. Set the properties of the objects on the report form as in Table 9.6. Objects on drBasic.dsr. OBJECT Property Setting RptCaption Name lbTitle Font Arial Size 12 Caption 'List of Authors' RptCaption Name lbAuthorTitle Font Arial Caption Author: RptCaption Name lbYearTitle Font Arial Caption 'Year Born:' RptText Name txtAuthor Font Arial DataField Author DataMember Command1 RptText Name txtYearBorn Font Arial DataField 'Year Born' DataMember Command1 DataSource DataEnviroment1 6. Remove the form called Form1 that was automatically created when you started Visual Basic.
Set the Startup Object to the report form. Run the project. HOW IT WORKS. Open and run DataProject.vbp, which displays the Figure 9.7. This displays each author's list of book and a count of the number of books per author.
By using the toolbar at the top of the report you can print or export the report. Start Visual Basic and select a new Data Project. This will add the Data Environment and Data Report to the project. Remove the form that was added to the project and set the Data Report as the Startup object. From the Project menu select the Add Data Environment.
Double-click on the DataEnvironment1 object that was added to the Project window. Right-click on the Connection folder and select the Add Connection item from the popup menu. Right-click on the Connection1 object and select the Properties item from the popup menu.
Select the Microsoft Jet 3.51 OLE DB provider. The next dialog box that appears is used to find the BIBLIO.MDB file on your computer. Click the OK button.
Right-click on the Command folder and select the Add Command item from the popup menu. Command1 will be added under the Commands folder. Right click on Command1 and select the Properties from the popup menu. Using the Data Object dropdown select the Tables item.
Using the Object Name dropdown select the Titles item. Steps 1 and 2 were to set up the Data Environment. Please refer to the chapters in this book for a more detail on the Data Environment.
Under the Grouping tab, check the Group Command Object checkbox. This will enable the items in the tab. Click on the Author field and click the ' button. This will cause the Data Environment to group the data by Author.
Click the OK button. Set the DataMember and DataSource of the Data Report. Right-click on the bottom of the report and select the Retrieve Structure item from the popup menu. This will arrange the sections of the Data Report to match the data used. Place the controls on the data report form as in Figure 9.6. Set the properties of the controls as in Table 9.7.
The project at runtime. Objects and properties for drBasic.dsr. FUNCTION Setting Description Sum 0 - rptFuncSum Adds the values of the field Average 1 - rptFuncAve Averages the values of the field Minimum 2 - rptFuncMin Displays the minimum value of the field Maximum 3 - rptFuncMax Displays the maximum value of the field Row Count 4 - rptFuncRCnt Displays the number of rows in a section Value Count 5 - rptFuncVCnt Displays the number of rows containing non-null values Standard Deviation 6 - rptFuncSDEV Displays the standard deviation Standard Error 7 - rptFuncSERR Displays the standard error. Arrangement of form frmOutPut.frm. RptFunction control functions description. The arrangement of the report DataReport1.dsr.
Object and Properties for DataReport1. The ExportFormat variable ( ExportFormats collection item) is used to set the type of file that will be exported. This variable is a member of the ExportFormats collection. The ExportFormats is a collection that stores the type of report formats that can be exported. The ListBox was populated with the default items of the ExportFormats collection.
The first character of each item in the ListBox is the index of that export type in the ExportFormats collection. The Left$ function is used to grab the number from the item displayed in the ListBox.
Adding and configuring the Data Environment in this How-To is similar to adding one in How-To 9.2. In this How-To, the Data Connection uses a SQL statement instead of retrieving data directly from a table in the BIBLIO database. Start Visual Basic and select a new Data Project.
This will add a Form, Report, and Data Environment automatically to your project. Double-click the DataEnvironment1 item in the Project Explorer window. This causes the Data View window to appear. Right-click on the Connection1 connection and select the Properties item from the popup menu. The dialog box in Figure 9.10 will appear. Select Microsoft Jet 3.51 OLE DB Provider.
Click on the Next button and select the BIBLIO.MDB database, then click the OK button. The BIBLIO.MDB database should be in the same folder as Visual Basic. Right-click on the Command folder in the Data View window and select the Add Command item from the popup menu. You will see Command1 appear under the Commands folder. Right click on the Command1 command and select Properties from the popup menu.
Click the SQL Statement radio button and enter the SQL statement found in Figure 9.10 and as shown below. SELECT Author, ´Year Born´ FROM Authors WHERE (´Year Born´ =?). Form frmDataEnv.frm.
Objects and Properties of the frmDataEnv.frm Form. OBJECT Property Setting Form Name frmDataEnv Caption 'What Year?' CommandButton Name cmdRunReport Caption 'Run Report' TextBox Name txtYear 6. Add the following code in the Click event of the cmdRunReport button.
Private Sub cmdRunReportClick If IsNumeric(txtYear.Text) = True Then DataEnvironment1.Command1 txtYear.Text DataReport1.Show Else MsgBox 'Please enter a valid year, YYYY', 64 End If End Sub This code passes the variable from the text box to the parameter contained in Data Environment. This means the end of messy parsing and building SQL statement in code. The Data Environment is built and the parameters are defined, which makes passing variables to SQL statements as simple as using methods.
Many of the parameters used to print a Crystal report through a Visual Basic application can be set using the Crystal Reports custom control. In this How-To, you'll create a simple report of authors and the computer books they've written. Because our BIBLIO.MDB file contains several of the authors' birth years, you can write a Visual Basic program that enables users to set a range of birth years to be printed, set a minimum or a maximum birth year, or set no limits at all, printing all the authors. Automatic table links defined by Crystal Reports. Click the Next button to proceed to tab 3: Fields. Add the fields listed in Table 9.13 to your report by double-clicking on the field name or selecting the field name and clicking Add. Computer author report tables and fields.
TABLE Field Authors Author Authors Year Born Titles Title Titles Year Published 5. On tab 4: Sort, add the Authors.Author field as a grouping field, and specify ascending order. On tab 5: Total, remove Authors.Year Born and Titles.Year Published from the Total Fields box. Add the Titles.Title field and specify count as the function in the pull-down list, as shown in Figure 9.16. Remove the checkmark next to Add Grand Totals.
Ignore tab 6: Select, and proceed to tab 7: Style. Enter the text A Time for Computer Authors as the report title. Click Preview Sample to view the results. Enter First 500 for the number of records to view. Experiment with the Report Zoom button (three different-sized squares), page navigation (upper-right), and scrollbars. Hint: Crystal's button hints float in the status bar on the bottom right of the screen. The Crystal Format Number dialog box.
Save the report as Authors.rpt for use from the Author Birth Range form. Create a new Standard EXE Visual Basic project in your work area. Save the default form as Authors.frm and the project as Authors.vbp. From the Project Components menu, select Crystal Report Control and Microsoft Common Dialog Control 6.0, and click OK.
Place controls on the form as shown previously in Figure 9.13, setting the properties as shown in Table 9.14. Note that the common dialog and Crystal Report controls are invisible at runtime, so place them anywhere that is convenient.
Objects and properties for Authors.frm. OBJECT Property Setting Form Name frmAuthors Caption 'Author Birth Range' TextBox Name txtEnd TextBox Name txtStart CommandButton Name cmdQuit Caption 'Quit' CommandButton Name cmdReport Caption 'Run Report' CommonDialog Name cdOpenReport CrystalReport Name crptAuthors Label Name Label2 Caption 'End Year:' Label Name Label1 Caption 'Start Year:' 15. Select the Crystal Report control. Invoke the custom property pages for the control by clicking the Custom property in the property box and then the ellipsis button (.). The Property Pages dialog box shown in Figure 9.19 appears.
Setting the Action property of the control to 1 causes the report to print. The Crystal Reports control uses this property as a pseudo-control method. It is very important to note that printing the report does not tie up the program at the point where the Action property is set to 1. In most cases, after the report writer has begun, the Visual Basic program continues executing, so you can't perform any actions that are dependent on the completion of the report.
On the other hand, your program can continue executing and performing other tasks while the report prints. This How-To uses a subset of all the different bulk mail categories.
The categories you'll design into the report are five-digit presort, three-digit presort, state presort, and first class (the 'catch-all' category). When you sort bulk mail, follow these steps: 1. Sort all the mailing pieces into groups that have 10 or more pieces going to the same first five digits of the zip code. Bundle those by the five-digit zip code, count them, and multiply the total by.191, the lowest postage cost of the categories used here. This gives you the total cost of that category of mail.
From the remaining pieces of mail (those that don't have at least 10 pieces per five-digit zip code), sort and extract the pieces that have at least 10 pieces going to the same first three digits of the zip code. Bundle those groups, multiply the postage by.191, and set them aside. Again from the remaining pieces, select and sort all the pieces that have at least 10 pieces going to the same state. Bundle, calculate the postage using.238 per piece, and set the pieces aside.
Finally, gather all the remaining pieces and place first-class stamps on them. They can go with the mailing, but you aren't saving any money on them. To open and run a report in Crystal Reports, select File Open from the Crystal main menu, and select the BULKMAIL.RPT report. To print the report, click the Print button on the toolbar, or select File Print from the main menu. To preview the report onscreen, click the Print Preview button on the toolbar, or select File Print Preview from the Crystal Reports main menu. The general design details are shown in the various tables throughout this How-To. You'll go through the individual steps needed to create the bulk mail report.
Figure 9.21 shows the main report elements in the Crystal Reports design window. This How-To uses the MAIL.MDB database described in the introduction to this chapter. Start Crystal Reports, and start a new report by clicking the New Report toolbar button or selecting File New from the main menu. Click on Standard when the Create New Report Gallery appears. Click on Data File when the Create Report Expert appears. Use the dialog box to find the MAIL.MDB Access database file.
Click Done to close the dialog box. From the Fields tab, add the Contact, City, State, and Zip fields from the MailingList table. On the Style tab, enter Bulk Mail Calculation Report as the title.
Crystal Reports design window for bulk mail reports. Insert a formula field by either clicking the Insert Formula toolbar button or selecting Insert formula Field from the main menu. Name the formula @Zip5Increment. Be sure to leave off the @ when typing the name. Enter the following code in the Edit Formula window: WhilePrintingRecords; NumberVar Zip5Count; Zip5Count:= Zip5Count + 1; The Edit Formula window should look like Figure 9.22 when you have entered the @Zip5Increment formula text. Click the Check button to have Crystal Reports evaluate the formula and check for errors. Click Accept when you're finished.
After the Edit Formula window closes, your cursor is dragging a dotted box around the design window. Move your cursor to the Page Footer section of the screen, and click your left mouse button. This click 'drops' the new field onto the report page. Don't worry about the exact location because we will hide the field later.
Setting the record sort order. Insert the formula field @TotalCountReset, which sets the total count to zero at the beginning of the report. The field will be hidden, so place the field anywhere in the page header band, and make it a small width. Placing hidden fields to the far right is usually convenient.
For convenience, this field also sets the postage amounts for the different classes of mail. WhilePrintingRecords; NumberVar Zip5UnitCost; NumberVar Zip3UnitCost; NumberVar StateUnitCost; NumberVar FirstClassUnitCost; Zip5UnitCost:=.191; Zip3UnitCost:=.191; StateUnitCost:=.256; FirstClassUnitCost:=.320; 31. Next, add the fields to the page footer. This is where you'll place the various counts and total postage for the different classes of mail. Start by adding the @FirstClassPrint formula field, which is the total count of the 'miscellaneous' category of mail. Place the field in the top line of the page footer section, as far right as possible. WhilePrintingRecords; NumberVar FirstClassCount; NumberVar FirstClassCost; NumberVar FirstClassUnitCost; FirstClassCost:= FirstClassCount.
FirstClassUnitCost; FirstClassCount; 32. Add the @FirstClassTotalLine, which is the heading for the total count of the 'miscellaneous' category of mail. Place the field in the top line of the page footer section, just to the left of the @FirstClassPrint field. WhilePrintingRecords; NumberVar FirstClassCount; 'Total First Class Count: '; 33. Now add the various running and final total formula fields. Add the @Zip3CostPrint formula field, which is the total count label of the three-digit zip category of mail. Place the field in the top line of the page footer section, at the far left.
WhilePrintingRecords; 'Zip (3) Totals:' 34. Add the @Zip3Count formula field.
This is the running count of the number of pieces of mail that qualify for three-digit zip bulk rates. Add the field just to the right of the @Zip3CostPrint field.
Format the field to have zero decimal places. WhilePrintingRecords; NumberVar Zip3TotalCount; Zip3TotalCount; 35. Add the @Zip3TotalCost formula field. This field prints the running cost of the three-digit zip mail. Add the field just to the right of the @Zip3Count field.
The cost of each category is calculated as you go, so all you need to do here is print the total. Format the field to have a currency symbol by checking the Currency Symbol checkbox in the Format Number dialog box, shown previously in Figure 9.23.
WhilePrintingRecords; NumberVar Zip3Cost; Zip3Cost; 36. Add the @Zip5CostPrint formula field, which is the total count label of the five-digit zip category of mail. Place the field in the second line of the page footer section, to the far left. WhilePrintingRecords; NumberVar Zip5Cost; 'Zip (5) Totals:'; 37. Add the @Zip5Count formula field. This is the running count of the number of pieces of mail that qualify for five-digit zip bulk rates.
Add the field just to the right of the @Zip5CostPrint field. Format the field to have zero decimal places. WhilePrintingRecords; NumberVar Zip5TotalCount; Zip5TotalCount; 38. Add the @Zip5TotalCost formula field.
This field prints the running cost of the five-digit zip mail. Add the field just to the right of the @Zip5Count field.
The cost of each category is calculated as the mail is processed, so only the total is printed here. Format the field to have a currency symbol by checking the Currency Symbol checkbox in the Format Number dialog box. WhilePrintingRecords; NumberVar Zip5Cost; Zip5Cost; 39. Add the @StateCostPrint formula field, which is the total count label of the state category of mail. Place the field in the third line of the page footer section, to the far left.
WhilePrintingRecords; 'State Totals:'; 40. Add the @StateCount formula field. This is the running count of the number of pieces of mail that qualify for state bulk rates. Add the field just to the right of the @StateCostPrint field. Format the field to have zero decimal places. WhilePrintingRecords; NumberVar StateTotalCount; StateTotalCount; 41.
Add the @StateTotalCost formula field. This field prints the running cost of the state mail.
Add the field just to the right of the @StateCount field. The cost of each category is calculated as the mail is processed, so only the total is printed here.
Format the field to have a currency symbol by checking the Currency Symbol checkbox in the Format Number dialog box. WhilePrintingRecords; NumberVar StateCost; StateCost; 42. Add the @FirstClassCostPrint formula field, which is the total count label of the 'miscellaneous' category of mail that is charged full fare. Place the field in the second line of the page footer section, to the right of the five-digit zip information. WhilePrintingRecords; 'First Class Totals:'; 43.
Add the @FirstClassCount formula field. This is the running count of the number of pieces of mail that don't qualify for bulk rates. Add the field just to the right of the @FirstClassCostPrint field. WhilePrintingRecords; NumberVar FirstClassCount; FirstClassCount; 44. Add the @FirstClassTotalCost formula field.
This field prints the running cost of the first-class mail. Add the field just to the right of the @FirstClassCount field.
The cost of each category is calculated as the mail is processed, so only the total is printed here. WhilePrintingRecords; NumberVar FirstClassCost; FirstClassCost; 45. Add @TotalCostPrint formula field, which is the total count label of all the categories of mail.
Place the field in the third line of the page footer section, below the first-class mail information. WhilePrintingRecords; 'Mailing Totals:'; 46. Add the @TotalCount formula field. This is the running count of the number of pieces of all the mail. Add the field just to the right of the @TotalCostPrint field.
WhilePrintingRecords; NumberVar TotalCount; TotalCount; 47. Add the @TotalCost formula field. This field prints the running cost of all the mail. Add the field just to the right of the @TotalCount field. The cost of each category is calculated as the mail is processed, so only the total is printed here. WhilePrintingRecords; NumberVar Zip5Cost; NumberVar Zip3Cost; NumberVar StateCost; NumberVar FirstClassCost; NumberVar TotalCost; TotalCost:= Zip5Cost + Zip3Cost + StateCost + FirstClassCost; TotalCost; 48.
When you are finished inserting the various fields and group sections, the design should look something like Figure 9.25. This screen shows the design window with the main menu File Options Show Field Names option checked and all the hidden fields unhidden so that they appear more clearly. Select File Print Preview to preview the report, or click on the Print Preview button in the toolbar. To print the report, select File Print Printer in the main menu, or click on the Print toolbar button. Many formulas start with the WhilePrintingRecords function. Although it is probably overkill in some cases, this function forces the formula to be evaluated while records are being printed. This is the normal order of evaluation:.
If no database or group field is included in the formula, the formula is evaluated before the program reads database records. If a database is included in the formula, the formula is evaluated while the program reads database records. If a group field, page # field, subtotal, and so on is included in the formula, the formula is evaluated after database records are read and while the data from the records are being printed in the report. Including WhilePrintingRecords ensures that formulas are evaluated as the report is being printed.
Other functions, BeforeReadingRecords and WhileReadingRecords, can be used to perform formula evaluations at different times. For example, you might want to record the system time at the beginning of a report for use throughout. WhileReadingRecords lets the Crystal Reports formula check to make sure that you haven't included elements in the formula that need to be evaluated while printing, such as group calculations or report elements like a page number.
The steps in this How-To show in detail how to create a shipping label. To open and run a report in Crystal Reports, select File Open from the Crystal main menu, and select the SHIPLBL.RPT report file, as shown in Figure 9.27. To print the report, click the Print button on the toolbar, or select File Print from the main menu. To preview the report on-screen, click the Print Preview button on the toolbar, or select File Print Preview from the Crystal Reports main menu. This How-To uses the MAIL.MDB database described in the introduction to this chapter.
Start Crystal Reports, and start a new report by clicking the New Report toolbar button or selecting File New from the main menu. Click on Mail Label when the Create New Report Gallery appears. The Create Report Expert dialog box. Click Preview Report to see the basic report. After reviewing the labels, switch to design view by clicking the Design tab. Select the three fields on the report by Ctrl-clicking on each field in turn. Right-click and use the Change Font dialog box to increase the font size to 12 points.
You can also change font attributes by using the font toolbar at the bottom of the Crystal Reports designer window. Drag the three fields down the label to just below the middle on the left side. Add agraphic element in the upper-left corner. Select Insert Picture from the main menu, or click the Insert Picture button on the toolbar. When the Choose Graphic File box appears, select a graphics file from any of the supported formats: Windows bitmap (BMP), CompuServe (GIF), PC Paintbrush (PCX), TIFF (TIF), or TARGA (TGA). EARTH.GIF, courtesy of NASA and the Galileo spacecraft program, is included on the CD-ROM that accompanies this book. After you select the file, click OK and the image appears on the Crystal Reports design screen.
Position it so that the upper-left corner of the image is at or near the upper-left corner of the label, inside the left vertical and top horizontal gray lines. Choose Format Picture from the main menu, and format the picture 2 inches wide and 1.5 inches tall, as shown in Figure 9.29.
You'll need to play with the aspect ratio (the ratio of height to width) to get it to look right. Because of different screen and printer aspect ratios, what looks right on the screen might not look right on your printer. The Graphic format dialog box. Enter three text fields, and enter the text Global Research Network, One Uranus Place, and Houston, Texas 04107. Place these text fields in the upper-right quarter of the label next to the picture. Format the fields for a 12-point bold italic font.
Insert a horizontal dividing line to split the two address areas of the label, by either selecting Insert Line from the main menu or clicking on the Insert Line button on the toolbar. Place the point of the line drawing tool directly on the label's left border, with the gray vertical line near the left edge of the label. Click and hold down the left mouse button as you drag the tool to the right edge of the label. Release the mouse button. If you need to, adjust the position or length of the line just as you would with any other field. Format the line to your preferred thickness.
Some versions of Crystal Reports enlarge the label size on the design window when you increase the font size on a line of the report. This causes you to end up with only one or two rows of labels on the page, rather than three rows. To restore the proper label size, scroll down to the bottom of the label. Notice that the bottom section border (the line that extends into the gray area to the left of the design area) is one-half to one-fourth of an inch below the rectangle of the label. Drag the bottom section edge as far up as it will go, adjacent to the dashed gray line.
In other words, the bottom edge of the label, the bottom edge of the section, and the dashed line should all be very close to one another. Remember to save the file, calling it SHIPLBL.RPT.
The steps in this How-To show in detail how to create a multi-page form letter. To open and run a report in Crystal Reports, select File Open from the Crystal main menu, and select the FORMLTR.RPT report file, as shown in Figure 9.30. To print the report, click the Print button on the toolbar, or select File Print from the main menu. To preview the report on-screen, click the Print Preview button on the toolbar, or select File Print Preview from the Crystal Reports main menu. This How-To uses the MAIL.MDB database described in the introduction to this chapter.
Start Crystal Reports, and begin a new report by clicking the New Report toolbar button or selecting File New from the main menu. Click the Custom button when the Create New Report Gallery appears. Click on Data File in the lower-right corner after the Create Report Expert expands. Use the dialog box to find the MAIL.MDB Access database file. Click Done to close the dialog box. A screen similar to that shown in Figure 9.31 appears.
As mentioned in the preceding set of steps, the text for the letter can be put into fields, it can come from the database in memo fields, or it can all be put into one large text field. It really depends on a few factors:. If every letter will be the same, with only one or two small customizations, use the technique in this How-To, in which a text field is used for each paragraph. This technique gives you the best combination of formatting flexibility and also allows the customization. If all the text of the letters is the same, you could use one large text field to hold it all. This means all the text must be formatted the same, and you must use trial and error to get the page headers and footers to work right.
Also, Crystal Reports has a problem with General Protection Faults and some video drivers, particularly when editing text fields that have some trailing space configurations. If the text in each letter changes substantially, and if much of the text is in the table you use for the report, you can use database fields in combination with text fields and formulas to control (to a low level of detail) how each letter is formatted and what information it contains. The steps in this How-To show in detail how to create a customer directory in Crystal Reports. Select File Open from the Crystal main menu, and select the CUSTDIR.RPT report file, as shown in Figure 9.32. To print the report, click the Print button on the toolbar, or select File Print from the main menu. To preview the report on-screen, click the Print Preview button on the toolbar, or select File Print Preview from the Crystal Reports main menu. This How-To uses the CRYSTAL.MDB database described in the introduction to this chapter.
Start Crystal Reports, and begin a new report by clicking the New Report toolbar button or selecting File New from the main menu. Click the Custom button when the Create New Report Gallery appears. Make sure that Detail field names are automatically inserted into the page header. Select File Options from the main menu. On the Layout tab, check the Insert Detail Field Titles option. Click on Data File in the lower-right corner after the Create Report Expert expands.
Use the dialog box to find the CRYSTAL.MDB Access database file. Click Done to close the dialog box. Here are a couple of final notes about strings in Crystal Reports:. When you trim and concatenate strings, remember to add spaces where you need them. For example, you needed to include a space within the quotation marks when you concatenated the FormAddress, ContactFN, and ContactLN fields.
Otherwise, there would have been no space between the names. In some databases, such as Paradox, it isn't necessary to use the TrimRight function to eliminate trailing spaces.
Paradox includes a null character at the end of the string, effectively making the length of the string the number of characters without trailing spaces. It doesn't hurt to include the TrimRight function, because the format could change in the future or you might need to adapt the report to be used with another database. The steps in this How-To show in detail how to create a fairly complex report from multiple tables and allow for missing information.
To open and run a report in Crystal Reports, select File Open from the Crystal main menu, and select the TITLES.RPT report file, as shown in Figure 9.33. To print the report, click the Print button on the toolbar, or select File Print from the main menu. To preview the report onscreen, click the Print Preview button on the toolbar, or select File Print Preview from the Crystal Reports main menu. Two interesting features were used to create this report. The first is the Suppress Blank Lines section formatting option. Using this option means that all you have to do is make sure that no extraneous text appears on a line that otherwise would be blank so that the line is not printed at all.
The phone and fax fields were put into formulas rather than separate text fields to make some of the text fields the full (or nearly full) width of the report. This way, if any field is blank, the entire line won't print. If two or more fields could be blank on the same line, all the fields would have to be blank for the line not to print, resulting in a checkerboard effect if some fields contain data and others do not. The steps in this How-To show in detail how to create a cross-tab report. To open and run a report in Crystal Reports, select File Open from the Crystal main menu, and select the MAILANAL.RPT report file, shown in Figure 9.35. To print the report, click the Print button on the toolbar, or select File Print from the main menu. To preview the report onscreen, click the Print Preview button on the toolbar, or select File Print Preview from the Crystal Reports main menu.
This How-To uses the MAIL.MDB database described in the introduction to this chapter. Start Crystal Reports, and start a new report by clicking the New Report toolbar button or selecting File New from the main menu. Click on Cross-Tab when the Create New Report Gallery appears. Click on Data File when the Create Report Expert appears. Use the dialog box to find the MAIL.MDB Access database file. Click Done to close the dialog box. The Crystal Reports design window for marketing analysis report.
From the Fields tab, add the Contact, City, State, and Zip fields from the MailingList table. On the Style tab, enter Bulk Mail Calculation Report as the title. After you select the database file to use for the report, the CrossTab window appears, as shown in Figure 9.36. The layout of this window makes it easy to visualize the final report.
You will need to enter the field or formula used for the rows and columns, and then enter a field or formula for the data that is contained in the body of the report. Crystal Reports will then handle all the calculations to produce the report. Creating a cross-tab report in Crystal Reports is an almost trivial task after you understand how to present data using this type of report.
The table used in this How-To consists of names, addresses, dates, and week days. By using a cross-tab report, you transform this data into a breakdown of clients by week, day, and geographic location. In fact, the hardest part of producing the report is to size and position the report elements so that all the fields show their data and everything fits on the page. Load and run the Visual Basic application MAILLIST.VBP.
From the Print Sorted Mailing List window (see Figure 9.38), select a report type to print (City, State, or Zip) and either enter a particular value to use in selecting records or leave the field blank to include all records. Select a page number format to be used, and then click the Print Report button to preview the report. Start by creating a simple report that you can modify when printing through Visual Basic (see Figure 9.39). Start Crystal Reports, and start a new report by clicking the New Report toolbar button or selecting File New from the main menu. Click on Standard when the Create New Report Gallery appears. This How-To takes advantage of properties of the Crystal Reports custom control to modify the design of the report at runtime. These changes stay in effect only for the lifetime of the Crystal Reports custom control in the form, and they are not saved in the report file itself.
This means that if the form with the control is unloaded, the options revert to the settings in the report until you explicitly set them again. This 'stickiness' trait of the control makes it critical to set any unused formulas or fields to a null string if they aren't required for the current operation. Three formula and field properties of the custom control were used in this How-To:.
SelectionFormula: There is one selection formula in each report. But because it is a Crystal Reports formula, it can be as complex as you care to make it.
In this How-To, the City, State, and Zip fields of the report are set to the particular values entered; if no values are entered, they are set to empty strings so that all records print. Formulas: Any formula contained in the report file can be changed at runtime through the Crystal Reports control.
In this How-To, the @ReportTitle and @PageFooter formulas were changed. SortFields: This property resets the record sort order of the report. A similar GroupSortFields property wasn't used in this program, but it can be used to reset the sorting order of any groups in the report. Both of these properties are implemented as arrays so that fields entered into the array are sorted in the order of the 0th element, 1st element, and so on.