Asp.Net

Wednesday 30 April 2014

Adding parameter fields to the crystal reports


aspx page:

<%@ Register Assembly="CrystalDecisions.Web, Version=10.5.3700.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"
Namespace="CrystalDecisions.Web" TagPrefix="CR" %>

<CR:CrystalReportViewer ID="ClientRevenue" runat="server" AutoDataBind="true"
 DisplayGroupTree="False" />

aspx.cs page:

using CrystalDecisions.Shared;
using CrystalDecisions.CrystalReports.Engine;

protected void Page_Load(object sender, EventArgs e)
{

 Bindqueryresults();
}

 private void Bindqueryresults()
    {
        try
        {
            SqlConnection con =
 new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["con"]);
            SqlCommand myCommand = new SqlCommand("usp_client_revenue", con);
            con.Open();

       myCommand.CommandType = CommandType.StoredProcedure;

      string cltid = Session["ClientLoginstatus"].ToString();
      string endcltid = Session["Endcltid"].ToString();
      string startdate = Session["fromdate"].ToString();
      string enddate = Session["todate"].ToString();

      startdate = utildate.FormatStringwithDB(startdate.ToString());
      enddate = utildate.FormatStringwithDB(enddate.ToString());

      myCommand.Parameters.AddWithValue("@Cltid", cltid);
      myCommand.Parameters.AddWithValue("@Endcltid", endcltid);
      myCommand.Parameters.AddWithValue("@Startdate", startdate);
      myCommand.Parameters.AddWithValue("@Enddate", enddate);

      SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
      DataSet ds = new DataSet();
      myAdapter.Fill(ds);
      ReportDocument sa = new ReportDocument();
      string reportPath = Server.MapPath("../Reports/ClientRevenue.rpt");
     ParameterFields paramFields = new ParameterFields();

         ParameterField pclientid = new ParameterField();
         ParameterField pendcltid = new ParameterField();
         ParameterField pstartdate = new ParameterField();
         ParameterField penddate = new ParameterField();
         pclientid.ParameterFieldName = "@Cltid";
         pendcltid.ParameterFieldName = "@Endcltid";
         pstartdate.ParameterFieldName = "@Startdate";
         penddate.ParameterFieldName = "@Enddate";

         ParameterDiscreteValue cltidvalue = new ParameterDiscreteValue();
         ParameterDiscreteValue endcltidvalue = new ParameterDiscreteValue();
         ParameterDiscreteValue startdatevalue = new ParameterDiscreteValue();
         ParameterDiscreteValue enddatevalue = new ParameterDiscreteValue();

        cltidvalue.Value = cltid;
        endcltidvalue.Value = endcltid;
        startdatevalue.Value = startdate;
        enddatevalue.Value = enddate;

        pclientid.CurrentValues.Add(cltidvalue);
        pendcltid.CurrentValues.Add(endcltidvalue);
        pstartdate.CurrentValues.Add(startdatevalue);
        penddate.CurrentValues.Add(enddatevalue);

        paramFields.Add(pclientid);
        paramFields.Add(pendcltid);
        paramFields.Add(pstartdate);
        paramFields.Add(penddate);
  
        ClientRevenue.ParameterFieldInfo = paramFields;
        sa.Load(reportPath);
        dt = ds.Tables[0];
         sa.SetDataSource(dt);
        ClientRevenue.ReportSource = sa;
        ClientRevenue.RefreshReport();
        con.Close();
    }
  catch (Exception ex)
   {
         throw ex;
   }
}