Web Development

Ask something
Discussion Locked

VB6 & Crystal Reports 8.0

0 helpful
11 replies

by paulfr2

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Ok first off, I'm a newbie to programming so please bear with me. My company has asked me to design a time tracking application for all of their technicians. Ok, that's fine I can do that. ADO is easy, but the problem is creating a dynamic reportusing Crystal Reports 8.0. We want to be able to select a specific technician from a list and a date range. I've been using SQL statements to generate the query from a Combo Box and 2 Text field boxes.

The code that I use in the SQL statement is as follows:

sSql = "SELECT tblLabor.Technician, tblLabor.Date, tblLabor.[Billable Hours], tblLabor.[Non Billable Hours], tblLabor.Vacation, tblLabor.[Sick Time], tblLabor.[Project Time], tblLabor.[Total Time], tblLabor.TicketNumber, tblLabor.TimeTaken "
sSql = sSql & "FROM tblLabor "
sSql = sSql & "WHERE tblLabor.Technician = '" & cmbTechname.Text & "' AND tblLabor.Date >= '" & txtDateStart.Text & "' AND tblLabor.Date <= '" & txtDateEnd.Text & " ' "
sSql = sSql & "ORDER BY tblLabor.Technician"

the sSql statment is piped into a text file for right now, but the company wants to use Crystal Reports to generate a more professional looking report. I'm somewhat familiar with CR, but not that familiar and can't seem to find any resources on how to use it with VB. Any assistance would be greatly appreciated.

Thanks,

Paul Friedrichsmeyer

11 replies

by Former member

I'm not very experienced with creating Crystal reports with VB either but the basics are that you create a report with Crystal Reports and then use it as the source for the VB form. Here are a few sites that may help

www.vbexplorer.com/vb_feature/august2000/using_crystal_reports.asp

www.apostate.com/programming/vb-crpe.html

support.microsoft.com/support/KB/ARTICLES/Q193/3/36.asp

www.crystaldecisions.net/products/dev_zone check out the Sample Application on this site

by jimbb

In Crystal Reports, which should be installed together with your VB, open your database, and open your table or tables. Drag and drop the fields to where you'd like them, and how you'd like them organised. It's roughly the same as creating a report in Access.
The same goes for "grouping", as in using your technician as a "header", and then giving all the details for that technician, and (for example) thus creating a report with one page per technician.
Save your report(s).

In VB, put the Crystal Reports object on your form. It's an invisible one, and you can have several on one form if you like. You can choose whether to put all the parameters in the object, via its properties, or from within the code "behind" a button or a menu option. You can choose for example the report name you wish to use (don't forget to distribute them, or store them somewhere centrally!), the filters, how it looks (print preview/print immediately/..., and so on. The easiest way to get all the parameters is F1 when toe object is selected. There are some samples there too.

Jim

by sl-campbell

There are several ways in doing this and it depends on how much control over CR via VB you need now and in the future and where the data comes from.
Assuming the data is stored in a database and assuming that you do not need more control, then using the CR OCX control could be what you need (if you nee?d more later then you'll need to change to the Crystal Report Engine Object Library or the Crystal Data Object).

Add the component. ( Project | Components | Crystal Report Control )

Add itfrom the toolbox to a form - maybe the main form.

Build the report in CR based on the table where the data comes from, to return all records. (Use the report generator - File|New - and start with a Standard report and then save it)

In VB set the following properties for the CR control:
Crpt1.ReportFileName = sMyReportPathAndName

Crpt1.DataFiles(0) = sMyDatabankPathAndName
(this needs to be done for each table listed in CR under "Database | Set Paths" increase the Crpt1.DataFiles(X) index (X) by one for each table)

Crpt1.SelectionFormula = sMyCriteria (CR has a little bit different syntax such as: {sMyTable.sMyField} = '123' )

Crpt1.SortFields(0) = +{sMyTable.sMyField}

You can also pass formulas:
Crpt1.Formulas(gc0) = "CRFormula1= '" & sMyFormula & "'"

Then there are many other settings like:
Crpt1.WindowShowPrintBtn = true
Crpt1.WindowShowExportBtn = true

You will have best results using one OCX copy on the main form, set a object variable to it and use aModule, or better yet, a Class, to handle everything that is passed to it.

You could also use the Crystal Reports Smart Viewer.

Continued.............

by sl-campbell

Again I must say, use the OCX control only when your demands on controlling CR via VB are not too high, or for a start to get the fell of things and then change later to the Report Engine. Also, the OCX is not being developed anymore according to what they told me. They will still have it, and MAY correct some bugs, but will not add any more functionality.

by sl-campbell

forgot to mention:
You need to either create a blank form in order to show the report on and set
Crpt1.WindowParentHandle = Form1.hwnd
so that CR knows where to show it's window and then call the report by setting:

Crpt1.Destination = crptToWindow (or crptToPrinter)
Crpt1.PrintReport

or use the Crystal Reports Smart Viewer.
as I mentioned above.

By the way, if you want to pass a recordset object straight to CR then you can do this by making a definition file (CR has prg to simplify it) and then the report uses the definition file as fields, which then can be place on the rpt as you would with real database fields. Once this is done you only need to pass the recordset. This will keep you from having to build temporary tables, filling and emptying them, (bloating an MDB as well). Then open the report as described above.

For this, check out "Using the Active Data Driver" in the same help file or go on-line to the Crystal Care Technical Support Knowledge Base Articles: c2002281,c2002848,c2003548,c2002748.

Also,check out the CR "Help for developer's" help file (installed with the CR program) under "Using the Crystal Data Object", and "When to use the Crystal Data Source Type Library"!.

by miss kitty

Hi Paul,
First you have to write the report and make sure that it is working. Now, in VB you will have to create a work table. If you are using Access, this makes everything easier. Just write an Access select query and have it make a work table for the select data to go into. If you are using SQL then you will have to go thru VB to make a table to hold the info from your select statement. Now when you call in the Crystal report, point it at the work table and you will get only the info that you need.
To access Crystal 8.0 from VB, click on Projects in the VB project you are writing. Click on components and select Crystal Report Control and Crystal Report Viewer Control.
To call in the report and view it, below is the coding:
Dim crxDatabaseTable As CRAXDRT.DatabaseTable
Dim crxParameterField As CRAXDRT.ParameterFieldDefinition
Dim crxFormulaField As CRAXDRT.FormulaFieldDefinition
Set Report = crxApplication.OpenReport _
("D:\Program Files\MIcrosoft Visual Studio\VB98\Contract Haulers Report.rpt", 1)
frmViewer.Show vbModal
To print it, change the Set Report = to crxApplication.PrintReport (and then the rest of the info on where your report is at).
If you need more help, email me and I'll help.
Cathy
covcat@riverstonegrp.com

This discussion is closed to new replies.