locked
crystal report based on filtering criteria

    質問

  • i m working on airline reservation project(a dummy project just to learn c#.net)

    i need to show the report as per the filtering criteria.

    i have created a blank crystal report with the table named "reservation" attached to it.the columns to be displayed are also clear from the image below.

    here is the meaning of filtering criteria:1) class(economic/business) 2) trip(oneway trip/round trip) 3) deptDate(departure date) 

    rest of the filtering criteria are self explainatory.

    (Note:i have just placed all the criteria as textboxes ,just for demonstration.actually i will use combo boxes for source,destination,class trip nad datetimepicker for deptdate)

    now i want to filer the report as per the filtering criteria added by user.consider the example below:

    1) if at user enters source as "mumbai" , destination as "delhi" and the class as "business" (and rest all the fields are left blank)..then the reporting should be like select * from reservation where source="mumbai" and destination="delhi" and class="business"(i m new to crystal reports. i just know the basics of attaching a report with table and table with the report viewer..and that is why i have tried to explain my requirements in terms of sql statement)also in the above query the source ,destination and class are decided at rum time

    here is the pictorial representation of my requirement:

    can any one write the code for my requirement or help me in any way.i m completely new to crystal reports.

    any help is appreciated from the core of my heart.


    2012年3月13日 5:40

回答

  • thanks hari.but i was a bit difficult to understand your code.the main problem of mine was how to decide the query of the dataAdapter(since the number of where conditions would vary as per the input at rum time).i finally solved it.here i post my code of solution:

     private void button1_Click(object sender, EventArgs e)
            {
                string a="", b="";
                if (src.Text != "")
                { a = a + src.Text + ","; b = b + "Source" + ","; }
                if (dest.Text != "")
                {   a = a + dest.Text + ","; b = b + "Dest" + ",";}
                if (clas.Text != "")
                {   a = a + clas.Text + ","; b = b + "Class" + ",";}
                if (trip.Text != "")
                {   a = a + trip.Text + ","; b = b + "TOJ" + ",";}
                if (ddate.Text != "")
                {   a = a + ddate.Text + ","; b = b + "DeptDate" + ",";}
                if (cname.Text != "")
                {   a = a + cname.Text + ","; b = b + "CustName" + ",";}
                if (fid.Text != "")
                {   a = a + fid.Text + ","; b = b + "FlightCode" + ",";}


                string[] aa = a.Split(','); string[] bb = b.Split(',');
                string query = "select * from Reservation where "; string sample1 = ""; string sample2 = "";
                int i=0;
               // foreach (string str in aa)
                //{
                    for (int j = 0; j < aa.Length - 1; j++)
                    {
                        //   MessageBox.Show(str);
                        //sample1 = sample1 + str;
                       // sample2 = sample2 + bb[i];
                        //if(str!=" ")
                        query = query + bb[i] + "='" + aa[i]+"'";
                        if (i < aa.Length - 2)
                            query = query + " and ";
                        i++;
                    }
               // }
               // MessageBox.Show(i.ToString()+"lentgh= "+aa.Length.ToString());
                MessageBox.Show(query);
               // MessageBox.Show("begin"+sample1+"end");
                //MessageBox.Show("begin"+sample2+"end");
                SqlConnection con = new SqlConnection("data source=abcde-PC\\sqlexpress;initial catalog=Test;integrated security=true;pooling=false");
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter(query,con);
                DataSet ds = new DataSet();
                da.Fill(ds, "Reservation");
                cr1.SetDataSource(ds.Tables["Reservation"]);
                crystalReportViewer1.ReportSource=cr1;
                crystalReportViewer1.Refresh();


            }

    • 回答としてマーク Neel6344 2012年3月13日 21:42
    2012年3月13日 21:42

すべての返信

  • using System;
    using System.Windows.Forms;
    using CrystalDecisions.CrystalReports.Engine;
    using CrystalDecisions.Shared;
    using System.Data;
    using System.Data.SqlClient ;
    
    namespace WindowsApplication1
    {
        public partial class Form1 : Form
        {
            CrystalReport1 objRpt = new CrystalReport1(); 
    
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                SqlConnection cnn ;
                string connectionString = null;
                string sql = null;
                connectionString = "data source=SERVER NAME;initial catalog=crystaldb;user id=USER NAME;password=PASSWORD;";
                cnn = new SqlConnection(connectionString);
                cnn.Open();
                sql = procesSQL();
                SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
                DataSet1 ds = new DataSet1();
                dscmd.Fill(ds, "Product");
                objRpt.SetDataSource(ds.Tables[1]);
                crystalReportViewer1.ReportSource = objRpt;
                crystalReportViewer1.Refresh();
            }
    
            public string procesSQL()
            {
                string sql = null;
                string inSql = null;
                string firstPart = null;
                string lastPart = null;
                int selectStart = 0;
                int fromStart = 0;
                string[] fields = null;
                string[] sep = { "," };
                int i = 0;
                TextObject MyText ;
    
                inSql = textBox1.Text;
                inSql = inSql.ToUpper();
    
                selectStart = inSql.IndexOf("SELECT");
                fromStart = inSql.IndexOf("FROM");
                selectStart = selectStart + 6;
                firstPart = inSql.Substring(selectStart, (fromStart - selectStart));
                lastPart = inSql.Substring(fromStart, inSql.Length - fromStart);
    
                fields = firstPart.Split(',');
                firstPart = "";
                for (i = 0; i <= fields.Length - 1; i++)
                {
                    if (i > 0)
                    {
                        firstPart = firstPart + ", " + fields[i].ToString() + " AS COLUMN" + (i + 1);
                        firstPart.Trim();
    
                        MyText = (TextObject) objRpt.ReportDefinition.ReportObjects[i+1];
                        MyText.Text = fields[i].ToString();
                    }
                    else
                    {
                        firstPart = firstPart + fields[i].ToString() + " AS COLUMN" + (i + 1);
                        firstPart.Trim();
    
    //--- Find the textfield that you 've placed in the report form
                        MyText = (TextObject)objRpt.ReportDefinition.ReportObjects[i+1];
                        MyText.Text = fields[i].ToString();
                    }
                }
                sql = "SELECT " + firstPart + " " + lastPart;
                return sql;
            } 
        }
    }

    For more references to build a basic knowledge of reporting please review the following article.

    Code Project

    Have a nice day! :)


    )-(aree

    2012年3月13日 9:30
  • thanks hari.but i was a bit difficult to understand your code.the main problem of mine was how to decide the query of the dataAdapter(since the number of where conditions would vary as per the input at rum time).i finally solved it.here i post my code of solution:

     private void button1_Click(object sender, EventArgs e)
            {
                string a="", b="";
                if (src.Text != "")
                { a = a + src.Text + ","; b = b + "Source" + ","; }
                if (dest.Text != "")
                {   a = a + dest.Text + ","; b = b + "Dest" + ",";}
                if (clas.Text != "")
                {   a = a + clas.Text + ","; b = b + "Class" + ",";}
                if (trip.Text != "")
                {   a = a + trip.Text + ","; b = b + "TOJ" + ",";}
                if (ddate.Text != "")
                {   a = a + ddate.Text + ","; b = b + "DeptDate" + ",";}
                if (cname.Text != "")
                {   a = a + cname.Text + ","; b = b + "CustName" + ",";}
                if (fid.Text != "")
                {   a = a + fid.Text + ","; b = b + "FlightCode" + ",";}


                string[] aa = a.Split(','); string[] bb = b.Split(',');
                string query = "select * from Reservation where "; string sample1 = ""; string sample2 = "";
                int i=0;
               // foreach (string str in aa)
                //{
                    for (int j = 0; j < aa.Length - 1; j++)
                    {
                        //   MessageBox.Show(str);
                        //sample1 = sample1 + str;
                       // sample2 = sample2 + bb[i];
                        //if(str!=" ")
                        query = query + bb[i] + "='" + aa[i]+"'";
                        if (i < aa.Length - 2)
                            query = query + " and ";
                        i++;
                    }
               // }
               // MessageBox.Show(i.ToString()+"lentgh= "+aa.Length.ToString());
                MessageBox.Show(query);
               // MessageBox.Show("begin"+sample1+"end");
                //MessageBox.Show("begin"+sample2+"end");
                SqlConnection con = new SqlConnection("data source=abcde-PC\\sqlexpress;initial catalog=Test;integrated security=true;pooling=false");
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter(query,con);
                DataSet ds = new DataSet();
                da.Fill(ds, "Reservation");
                cr1.SetDataSource(ds.Tables["Reservation"]);
                crystalReportViewer1.ReportSource=cr1;
                crystalReportViewer1.Refresh();


            }

    • 回答としてマーク Neel6344 2012年3月13日 21:42
    2012年3月13日 21:42