Crystal Report Coding Problem

[retkehing]

Anyone can help me to check the following underlined code? The VB.NET 2005 can't recognize the underlined object. I will really appreciate if you can provide me some good Crystal Report web tutorials or information. Thank you.

Dim CrystalReportViewer1 As CrystalDecisions.Windows.Forms.CrystalReportViewer = New CrystalDecisions.Windows.Forms.CrystalReportViewer

Dim Report As CrystalDecisions.CrystalReports.Engine.ReportDocument = New CrystalDecisions.CrystalReports.Engine.ReportDocument
CrystalReportViewer1.ActiveViewIndex = 0

CrystalReportViewer1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
CrystalReportViewer1.DisplayGroupTree = False
CrystalReportViewer1.Dock = System.Windows.Forms.DockStyle.Fill
CrystalReportViewer1.Location = New System.Drawing.Point(0, 0)
CrystalReportViewer1.Name = "CrystalReportViewer1"

 

[CygNuS]

What is the exact error?
Did you Add all the necessary References to the project?
- CrystalDecisions.Windows.Forms
- CrystalDecisions.CrystalReports.Engine
etc...

 

[cjard]

Funny.. when I added a new crystal reports document to my project, VS inserted the reference for me automatically..

 

[retkehing]

The following is the complete code i got from internet and tried to execute in order to gain understanding on crystal report. Now i have solved the previous problem but i did the line by line debug and when the pointer reached this statement "Report.Load(Application.StartupPath & "/ReportName.rpt")", it returned an error. So may i know what is the parameter that i should replace with ReportName.rpt? Thank you.

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] CrystalReportViewer1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] CrystalDecisions.Windows.Forms.CrystalReportViewer = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] CrystalDecisions.Windows.Forms.CrystalReportViewer[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Report [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] CrystalDecisions.CrystalReports.Engine.ReportDocument = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] CrystalDecisions.CrystalReports.Engine.ReportDocument[/SIZE]
 
[SIZE=2]CrystalReportViewer1.ActiveViewIndex = 0[/SIZE]
[SIZE=2]CrystalReportViewer1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle[/SIZE]
[SIZE=2]CrystalReportViewer1.DisplayGroupTree = [/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE]
[SIZE=2]CrystalReportViewer1.Dock = System.Windows.Forms.DockStyle.Fill[/SIZE]
[SIZE=2]CrystalReportViewer1.Location = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Drawing.Point(0, 0)[/SIZE]
[SIZE=2]CrystalReportViewer1.Name = [/SIZE][SIZE=2][COLOR=#800000]"CrystalReportViewer1"[/COLOR][/SIZE]
 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] QueryString [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#800000]"select * from Emp_Profile"[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'Your Query here[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Con [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#800000]"Provider=SQLOLEDB; Data Source= USER\SQLEXPRESS; Database=CLDatabase; UID=sa ; PWD=sa;"[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Connection [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbConnection(Con) [/SIZE][SIZE=2][COLOR=#008000]'Your Database Connection Here[/COLOR][/SIZE]
 
[SIZE=2]Connection.Open()[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Adapter [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDbDataAdapter = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbDataAdapter(QueryString, Connection) [/SIZE][SIZE=2][COLOR=#008000]'Passing the query in the connection[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] DataSet [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataSet = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataSet() [/SIZE][SIZE=2][COLOR=#008000]'DataSet[/COLOR][/SIZE]
[SIZE=2]Adapter.Fill(DataSet)[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] DataTable [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataTable = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataTable [/SIZE][SIZE=2][COLOR=#008000]'DataTable[/COLOR][/SIZE]
 
[SIZE=2]DataTable = DataSet.Tables(0) [/SIZE][SIZE=2][COLOR=#008000]'filling the datatable here[/COLOR][/SIZE]
[SIZE=2]Report.Load(Application.StartupPath & [/SIZE][SIZE=2][COLOR=#800000]"/ReportName.rpt"[/COLOR][/SIZE][SIZE=2]) [/SIZE][SIZE=2][COLOR=#008000]'Report Name Here[/COLOR][/SIZE]
[SIZE=2]Report.SetDataSource(DataTable)[/SIZE]
[SIZE=2]CrystalReportViewer1.ReportSource = Report[/SIZE]

 

[cjard]

Gee.. I dunno.. How about the name of your crystal report file?


Thats like saying "i created some text file on my hard disk, and i'm trying to open it with notepad xxx where xxx is the name of the file. what should i put for xxx?"

 

[retkehing]

I understand but how to create a crystal report and save in a specified location. I want to do something like this, when the user select a row of the datagridview and press print, it will generate report and allow user to print the report of that selected employee. Now what i have done is only add the crystal report in my solution explorer and setup the OLEDB connection for the crystal report. May i know how to call out the crystal report by clicking the print button, do i need a viewer for that and how to pass the SQL statement to the report so it can retrieve the data properly?

I have already extracted the data that i want to display in report using textboxes and datagridview. May i know is there any way for me to pass all these values to the text objects in crystal report? Thank you.

 

[cjard]

I take it then, that it never occurred to you that the files you see in Solution Explorer are actually on disk somewhere?

Just give the database connection to crystal and it will get the data itself..

 

[TechGnome]

Typing "Crystal Reports" into the MSDN search, results in this link:
http://msdn2.microsoft.com/en-us/library/ms227881(VS.80).aspx

-tg

 

[retkehing]

I have already set the datasource for the crystal report but i dont know how to extract the emp_no from the database and display it using the crystal report text object called Employee_No. Thank you.

CrystalReportViewer1.ActiveViewIndex = 0
CrystalReportViewer1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
CrystalReportViewer1.DisplayGroupTree = [SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE]
[SIZE=2]CrystalReportViewer1.Dock = System.Windows.Forms.DockStyle.Fill[/SIZE]
[SIZE=2]CrystalReportViewer1.Location = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Drawing.Point(0, 0)[/SIZE]
[SIZE=2]CrystalReportViewer1.Name = [/SIZE][SIZE=2][COLOR=#800000]"CrystalReportViewer1"[/COLOR][/SIZE]
 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] QueryString [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#800000]"select * from Emp_Profile WHERE emp_no='E00001'"[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'Your Query here[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Con [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#800000]"Provider=SQLOLEDB; Data Source= USER\SQLEXPRESS; Database=UserDatabase; UID=sa ; PWD=sa;"[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Connection [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbConnection(Con) [/SIZE][SIZE=2][COLOR=#008000]'Your Database Connection Here[/COLOR][/SIZE]
[SIZE=2]Connection.Open()[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Adapter [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDbDataAdapter = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbDataAdapter(QueryString, Connection) [/SIZE][SIZE=2][COLOR=#008000]'Passing the query in the connection[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] DataSet1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataSet = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataSet() [/SIZE][SIZE=2][COLOR=#008000]'DataSet[/COLOR][/SIZE]
[SIZE=2]Adapter.Fill(DataSet1)[/SIZE]
 
[SIZE=2][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Report [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] CrystalReport1[/SIZE]
[SIZE=2]Report.SetDataSource(DataSet1)[/SIZE]
[SIZE=2]CrystalReportViewer1.ReportSource = Report[/SIZE]
[/SIZE]

 

[retkehing]

Now i am able to display the data from the dataset. First, i select which table i am going to make use under database expert and then you can drag any data field of that table you want to display from Field Explorer. Apart from that, i coded the SQL query which retrieves the appropriate data and fill this into dataset with Adapter. Set this dataset as DataSource of CrystalReport and previous dragged data field will only retrieve the result according to the query.

However, the datafields i can drag into crystal report content must belong to the table that i have set previously. What if i want to sum two data and display this data with a text object of CrystalReport. Even we could add this data in dataset, what about the field that used to display that since there is no data field i could find for that? Thank you.

 

[retkehing]

I tried to generate the report with dataset. I have already established the connection between crystal report and database and drag relevant fields of some tables. When i come to join on tables, why some of the fields of the table are missing and actually they are the fields needed for join?

Currently, i made use of the wizard and simply drag and drop the field but what if i want to display some of the information like total no. of the employees, which is not a field stored in the database and hence i can't drag it onto the the report?

Based on the internet information, it said that i shouldn't use wizard. I must create a dataset schema from IDE and populate dataset from the database. I can't fully understand what i am supposed to do now since i have filled the data into dataset with adapter and my concern is always without using the wizard, how could i form a field which used to display the data value in report? I have been working on this solution for quite a while and i will really appreciate if you can help on this. Thank you.

 

[DavidT_macktool]

Re: help...

I was just browsing and thought I could help.
Summing report fields is accomplished by right clicking on the report and selecting "INSERT" and selecting "subtotal", "grand total", or "summary". Subtotals usually go in the group footer, grand totals in the report footer.

You will also want to look up the use of "Formula Fields" and "Parameter Fields". Formula fields are a good way to do calculations on report fields - eg. calculate total cost. (price * quantity). Parameters are a good way to pass values to the report from VB. eg. pass employee number and use that parameter in the record selection formula to print out a report about a specific employee. BTW. Right click on the report...select "REPORT" - "EDIT SELECTION FORMULA", and choose "GROUP" or "RECORDS". Brush up on your formula editor skills and search this forum for more specific requests especially "Crystal Report Parameters".

I'll try to help when I can...

 

[retkehing]

Now i use the following way of generating the report, there is a relationship between employee and requisition tables.

May i know how to form the table structure in the report and display the data? Apart from that, may i know how to generate a group of data belong to another data? For example, one employee with employee ID E00001 may have more than one requisition no.. I dont want the requisition no. to display more than one E00001 in report but just one E00001 and all of relevant requisition no. next to it. Is there anyway i can do it? Thank you.

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] VoucherViewer [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] CrystalDecisions.Windows.Forms.CrystalReportViewer = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] CrystalDecisions.Windows.Forms.CrystalReportViewer[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] DataSet1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataSet = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataSet()[/SIZE]
[SIZE=2]DataSet1.Tables.Clear()[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] DataTable_Emp_Profile [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataSet1.Emp_ProfileDataTable[/SIZE]
[SIZE=2]DataSet1.Tables.Add(DataTable_Emp_Profile)[/SIZE]
[SIZE=2]Local_Global_SQLSTRING = [/SIZE][SIZE=2][COLOR=#800000]"SELECT * FROM Emp_Profile WHERE emp_no='"[/COLOR][/SIZE][SIZE=2] & DataGridView1.Item(0, DataGridView1.CurrentRow.Index).Value.ToString & [/SIZE][SIZE=2][COLOR=#800000]"'"[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] DAdapter1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDb.OleDbDataAdapter = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbDataAdapter(Local_Global_SQLSTRING, OleDbConnection1)[/SIZE]
[SIZE=2]DAdapter1.Fill(DataTable_Emp_Profile)[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] DataTable_Requisition [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataSet1.RequisitionDataTable[/SIZE]
[SIZE=2]DataSet1.Tables.Add(DataTable_Requisition)[/SIZE]
[SIZE=2]Local_Global_SQLSTRING = [/SIZE][SIZE=2][COLOR=#800000]"SELECT * FROM Requisition WHERE batch_no='"[/COLOR][/SIZE][SIZE=2] & DataGridView1.Item(1, DataGridView1.CurrentRow.Index).Value.ToString & [/SIZE][SIZE=2][COLOR=#800000]"'"[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] DAdapter2 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDb.OleDbDataAdapter = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbDataAdapter(Local_Global_SQLSTRING, OleDbConnection1)[/SIZE]
[SIZE=2]DAdapter2.Fill(DataTable_Requisition)[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] VoucherReport [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Voucher[/SIZE]
[SIZE=2]VoucherReport.SetDataSource(DataSet1)[/SIZE]
[SIZE=2]VoucherViewer.ReportSource = VoucherReport[/SIZE]
[SIZE=2]Voucher_Report_Form.Controls.Add(VoucherViewer)[/SIZE]
[SIZE=2]Voucher_Report_Form.WindowState = FormWindowState.Maximized[/SIZE]
 
[SIZE=2][COLOR=#008000]'Set the Crystal Report Viewer properties[/COLOR][/SIZE]
[SIZE=2]VoucherViewer.ActiveViewIndex = 0[/SIZE]
[SIZE=2]VoucherViewer.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle[/SIZE]
[SIZE=2]VoucherViewer.DisplayGroupTree = [/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE]
[SIZE=2]VoucherViewer.Dock = System.Windows.Forms.DockStyle.Fill[/SIZE]
[SIZE=2]VoucherViewer.Location = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Drawing.Point(0, 0)[/SIZE]
[SIZE=2]VoucherViewer.Name = [/SIZE][SIZE=2][COLOR=#800000]"CrystalReportViewer1"[/COLOR][/SIZE]
 
[SIZE=2][COLOR=#008000]'Show the Voucher_Report_Form[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] DisplayForm [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Voucher_Report_Form[/SIZE]
[SIZE=2]Voucher_Report_Form.ShowInTaskbar = [/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE]
[SIZE=2]Voucher_Report_Form.ShowDialog([/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2])[/SIZE]