Crystal Reports help

E

edmund

Member
Joined
Jul 27, 2003
Messages
10
I have a report designed in crystal reports (CR for .NET) using a MySQL database. The report is based on a query, which is set in the report. My headache is that I cant figure out how to change the query at runtime using vb.net. Any help would be truly appreciated.

Thanks
 
ok here is the scenario. The report/form is already designed to the specs (it has to look close to exact as a CA state form. There are 4 rows to a page that could be multiple pages. These rows have contain 14 data items (fields) to populate psuedoo checkboxes(an "X" inside a box), text and currency types. To get the data required to populate the form it requires multiple joins and nested recordsets.

Now, its a client server app, my solution was to grab the data and store it in a table emulating a row in the form based on the system users id and indexed by an identity so that I can create new instances of the report populate it, preview it, print it then dump the data. In the crystal report itself I get the data by storing a sql command:

"SELECT * FROM tablename WHERE useid = 1 order by id"

The report populates beautifully.

I just want to change the 1 (ie user id) in the query, depending on the user. How does one change the query at runtime? Theres got to be a solution to this.

Scenario number two basing a report on a date range. Another just change the query situation. hmm

Any help would certainly be appreciated.
 
Parameters. I know t-sql supports it. "SELECT * FROM tablename WHERE useid = @useid order by id". Look at the dataadapter parameters method to learn how to fill the parameter at run time. The syntax is something like this:

daMyAdapter.Parameters("@useid").value = "1"
 
Thanks, Ill look into it and let you know what happens. Would you know how to bypass the prompt for a parameter field?
 
How Crystal Reprot Works with ADO.NET

How Crystal Reprot Works with ADO.NET

ADO.NET data access model i entirely diffierent from other data access methedologies.

if you are going to use ADO.NET to fill crystal Reports you need to do things in theis sequence.

1- Design an XML Schma Defination file (xsd) which will be used while designing Crystal Report. this contains nothing just tables which would be contained on Report.

2- Design a Crystal Report.

3- add a Crystal Report Viewer to some form and bind it to the Report which we designed and fill the report with ADO.NET data

here s a little example. in C# it would be easier for VB Developers to understand

C#: 
//Here i have made a function which Loads an Already Designed Report 

//in a Crystal Report Viewer and fills it with data

//this is same like you fill some DataGrid

// mysql is a Connection which has been already intialized so dont need to mention the Query String

 

using CrystalDecisions.CrystalReports; <OR in VB> imports CrystalDecisions.CrystalReports

//This is a simple SUB which takes an input parameter Query

// and use that Query string to get data.

public void LoadCarData(string strQuery)

{

     try

      {

            //rptCars is a Crystal Report which was designed to View Cars Sales

            rptCars rptc = new rptCars();

            //Here Defining a SQL Data Adapter and giving some Qurey (select * from cars)

            SqlDataAdapter myadp = new SqlDataAdapter(strQuery,mysql);

            //Here Defining a new DataSet myds

            DataSet myds = new DataSet();

 

            //Now Filling the DataTable Cars in the Dataset myds 

            // Dont be confused with Cars table we give this name to the result

            // Table for our Reference if you dont specify the name of Table

            // by Default DataAdapter gives it name (Table1)

            myadp.Fill(myds,"Cars");

            //Next I am doing seting the CarReport Object to get data from this tables

            rptc.SetDataSource(myds.Tables["Cars"]);

            //once my report is filled with records i am setting Crystal Report Viewers 

            //Report Source Property to the Report Object which we instentiated from 

            //and already designed report.

            reportviewer.ReportSource = rptc;

            reportviewer.Zoom(2);

      }

      catch (Exception ex)

      {

            //If some error occures it will show it in a messageBox

            MessageBox.Show(ex.Message,"Error");

      }

      finally

      {

            if (mysql.State==ConnectionState.Open)

            {

                  mysql.Close();

            }

      } 

}
 
Back
Top