Wednesday, April 15, 2009

Connecting Crystal Report and VB using TTX

I'd like to share how to make a report using Crystal Report, and connect it from VB here, since there has been several friends asking about this.
There are actually several ways to connect VB and Crystal Report, ones of which that I've ever used are using TTX and ODBC.

I'd like to share the usage of TTX here. I use Crystal Report 8.5, but it's more or less the same as the other versions.

Like it's data source type - Field Definition Only - implies, it makes a .ttx file to define the fields used in the report. Thus, when creating a report, we have to make a .ttx file that contains all the fields and type of the fields used in the report.

The advantages of this method are:
1. Flexible, we don't have to change the report if there is any changes on the logic while retrieving the data.

2. We only have to define the data type, that's all, and do the code in the application.

These are the steps need to be done for the report:
1. Run Crystal Report, choose Create a New Crystal Report Document [As a Blank Report].

2. Choose the type of the data source: Field Definition Only.
Data Source: Field Definition Only

3. It will ask to browse for a text file or make a new file, I'll make a new one.
Just insert the Field Name and the Field Type to be used in the report. Make sure the field name and the field type is correct. Add all fields used. When finished, click the close icon of the form. It will ask to save the file, then click OK.

4. Click Add to add the .ttx file to the report, then click Close.

Add TTX to report

5. Start designing the report by inserting the fields used in the report then save it.

I use the table [Customers] from the Northwind database.
I'll try to print Customers data by Country.
PS: I use ADODB for the database connection.

These are the steps in the VB:
1. Add Crystal Report component to the application by pressing ctrl+T or go to menu Projects -> Components. On the Controls tab, check [Crystal Report Control], then press ok. A crystal report component will be added to the toolbox window.

2. Drag the component to the form.

Crystal Report Component

3. The small programs will look like this.

Application View

4. Here's the code on the print button event:
The name of the Controls:
combo : cmbCountry
command button : cmdPrint

Private Sub cmdPrint_Click()
With CrystalReport1
Dim sql As String
sql = "select CustomerID, CompanyName, ContactName, ContactTitle, " & _
"Address from Customers where Country = '" & cmbCountry.Text & "'"
Set rs = con.Execute(sql)

.ReportFileName = App.Path & "/RptTest.rpt"
.Destination = crptToWindow
.SetTablePrivateData 0, 3, rs
.WindowState = crptMaximized
.WindowShowPrintBtn = True
.Action = 1
End With
End Sub

5. And the report looks like this.

Report View

That's all folks.



  1. thx buat tutorialnya,,,
    kalo buat subreport bisa g pake metode ky gitu??
    Gmn caranya??

  2. Ups, sori, ga sadar kalo ada yang comment.. hehe, kalo buat subreport, bisa juga, sama kek pake ODBC ato yang lainnya ^^

  3. aku dah coba panggil ttx buat ditampilin sebagai subreport,,,tapi hasilnya g kluar...klo aku panggil langsung sebagai main report kluar,,,,

    ada contoh codingnya g?!


  4. Alo, ada post baru tentang cara buat sub report dengan TTX, bisa dilihat di ^^ Good Luck :)

  5. thanks buat postingannya.
    tapi saya buat di kok ga keluar ya? msh ada tampil pesan errornya. kira2 salah dmn?
    thanks banget.

  6. Hmm, pesan errornya apa yah? Kalo ga dijelaskan, ga ketahuan.. hehe :)

  7. ada yang tahu mas programer....

    ada tugas kuliah yang harus disiapkan...
    bagaimana cara menampilkan report pada CR8
    pada kasus seperti dibawah ini :

    NIP : 12345
    Nama : anto
    jabatan : supervisor

    Data Tanggungan :
    | Hubungan | Nama tanggungan | tgl lahir | ket |
    | Istri | siti | 01-01-85 | IRT |
    | Anak | adi | 01-01-08 | - |
    | Anak | | | - |
    | Anak | | | - |

    Data Keluarga :
    | Hubungan | Nama Keluarg | Alamat |
    | Bpk Kndg | rahman | medan |
    | Ibu Kndg | maryam | medan |
    | Bpk Mrta | jhony | medan |
    | Ibu Mrta | rohani | medan |
    | Sdr Kndg | ahmad | medan |

    mohon bantuan pencerahannya... saya menggunakan koneksi DAO

    terima kasih
    masry... medan

  8. tambahan mas...databasenya MDB
    hasil tampilan dalam 1 report..
    kita hanya pilih NIP yang bersangkutan..

    terima kasih sebelumnya

  9. Dalam post di atas udah ada kan mas, tutorialnya, cara buat report dan hubungkan VB dengan Crystal Report. Kalau Anda pakai DAO Connection di VB, sama saja. Untuk kasus Anda hanya perlu ubah sedikit di reportnya dan query SQL untuk mengambil datanya.

    Pengambilan datanya diambil semua saja. Hasil dari querynya seharusnya terdiri dari field2 berikut: NIP, Nama, Jabatan, HubunganTanggungan, NamaTanggungan, TglLhrTanggungan, KetTanggungan, HubunganKeluarga, NamaKeluarga, dan AlamatKeluarga.

    Setelah didapat datanya, di reportnya, NIP, Nama, dan Jabatan diletakkan pada Page Header / Report Header. Sedangkan field2 lainnya diletakkan pada section Detail.

  10. udah saya coba mas...
    untuk data tanggungan bisa tampil...
    data keluarga nggak mau tampil....

    tabel saya pisah... yang jadi query hanya NIPnya..


    semua dipanggil berdasarkan NIP

    terima kasih sebelumnya..

  11. Ohh, oh yah, maaf Mas, salah tebak.. hehehe, untuk kasus Anda sebaiknya menggunakan SubReport.
    Ada 2 alternatif:
    1. Seperti yang telah Anda buat, Data tanggungan diretrieve dari report utama. Sekarang Anda perlu buat 1 sub report untuk data keluarganya. Untuk cara buat subreport, silakan liat di post sebelumnya, sudah ada prosedurnya step-by-step. Penghubungnya adalah NIP.

    2. Data tanggungan dibuat dalam 1 sub report. Data keluarga juga dibuat dalam 1 sub report sendiri. Tujuannya supaya lebih rapi, dan dapat digunakan lagi reportnya kalau memang sewaktu² diperlukan

  12. Mas, bagi dong tutorial ttx buat nya. thx banget nih mas steven...

  13. Hmm, untuk VB.NET kayaknya dulu pernah buat seh, cuma sekarang ga ada waktu untuk buat sample atau tutorialnya neh.. hehehe, seharusnya ga jauh beda, silakan dicoba dulu yah, Tala ^^ Atau coba cari² di internet kalo ada contoh code-nya.. hehehe :P

  14. this is Awsome thank you very much, you are great



Related Posts

Dual Axis not Working in Tableau

Problem : When creating chart with Dual Axis, Synchronize Axis is disabled and cannot be clicked. Cause : Tableau identified this as ...

About Me

My photo
Is an ordinary unNoticed man, with a little knowledge to share and high dreams to achieve. I'd be glad if I can help others, 'coz the only thing for the triumph of evil is for a good man to do nothing. I like English and Chinese language, and know a little about them. Hence, for me, most things can best be expressed in Chinese, some in English, and the rest in others ^^

About Blog

You can find a lot of debugging and deploying problems while developing applications in .NET and Visual Basic here. There are also some querying tips in SQL and typical source codes which might be useful shared here.

Popular Posts



Follow by Email