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.

Share:

14 comments:

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

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

    ReplyDelete
  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?!

    thx

    ReplyDelete
  4. Alo, ada post baru tentang cara buat sub report dengan TTX, bisa dilihat di http://programmerslab.blogspot.com/2009/10/creating-sub-report-using-ttx-in-vb.html ^^ Good Luck :)

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

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

    ReplyDelete
  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

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

    terima kasih sebelumnya
    masrycom@gmail.com

    ReplyDelete
  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.

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

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

    tebel_data_pegawai
    tabel_data_tanggungan
    tabel_data_keluarga

    semua dipanggil berdasarkan NIP

    terima kasih sebelumnya..
    masrycom

    ReplyDelete
  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

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

    ReplyDelete
  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

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

    ReplyDelete

You may be intersted in

Related Posts

Updating Table Containing Xml Column via LinkedServer

If you are trying to update a table containing XML column via Linked Server in SQL Server, and you are not able to, you are not alone. There...

About Me

My photo
Is an ordinary 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.

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

Blogroll

Followers

Leave a Message