Programmatically Pass Parameters into Crystal Reports from ASP.NET (C#) - Part 1

Introduction

This article will guide you through how to programmatically pass parameters to a Crystal Reports report and show the report in an .aspx page. It will also show you how to create a non-embedded cached report. This article is also intended to help you out with dynamically retrieving parameters from a Crystal Reports file and placing custom controls based on their types on your web page.

Background

In this article, I presume that you are familiar with creating Crystal Reports files and you have already created some .rpt files which have some parameters as well. The page that we will create here will dynamically gather the parameters from the rpt file based on the report's parameters count, then a Submit button will be placed on the page which will pass all the parameters to the report and show it.

Using the Code

The project consists of two ASPX pages:

1. Default.aspx - This page has the list of reports where from they will be launched.
2. ReportView.aspx - This page will do three main things:
   • Create the non-embedded cached report.
   • Retrieve the parameters from the cached report and place the custom controls instead of the Crystal Reports built-in parameters.
   • Submit and show the report itself (this includes passing the parameters to Crystal Reports, of course).

Default.aspx Code-behind

Basically, there are two methods to show and launch the selected report:

public void ShowReportList(int menu_id)
{
  // Show reports in a GridView webcontrol
  // The source comes from an SQL2005 database

  DataSet DS;

  string connString = 
    System.Configuration.ConfigurationSettings.
    AppSettings["userreader"].ToString();
  DS = SqlHelper.ExecuteDataset(connString, CommandType.StoredProcedure, 
       "usp_get_submenuitems", 
       new SqlParameter("@menu_id", menu_id));
  GridView1.DataSource = DS.Tables[0].DefaultView;
  GridView1.DataBind();
}

protected void GridView1_RowCommand(object sender, 
                         GridViewCommandEventArgs e)
{
  // A session will be set to identity the selected report
  // and open the ReportView.aspx in a new window

  int index = Convert.ToInt32(e.CommandArgument);
  GridViewRow selectedRow = ((GridView)e.CommandSource).Rows[index];
  Session.Clear();
  Session["name"] = selectedRow.Cells[0].Text;
  if (e.CommandName == "Preview")
  {
    Response.Write("<script>");
    Response.Write("window.open('../ReportView.aspx?time=" + 
                   DateTime.Now.ToBinary() + "','_blank')");
    Response.Write("</script>");
  }
}

ReportView.aspx

Here is a quick outline of the ReportView class:

public partial class ReportView : System.Web.UI.Page
{
  protected void Page_Load(object sender, EventArgs e)[...]
  private void Page_Init(object sender, EventArgs e)[...]
  private void InitCrystalReport()[...]
  private void InitCrystalReportParameters()[...]
  private void AddDateTimeFilter(CrystalDecisions.Shared.ParameterField param)[...]
  private void AddMultiSelectFilter(CrystalDecisions.Shared.ParameterField param)[...]
  private void AddStringFilter(CrystalDecisions.Shared.ParameterField param)[...]
  private void AddSubmitButton()[...]
  protected void Submit_Click(object sender, EventArgs e)[...]
}

The page starts with three init steps:

public partial class ReportView : System.Web.UI.Page
{
    private CachedReport cachedReportSource;
    private CrystalDecisions.Web.CrystalReportViewer myViewer;
    private string report_file = "";
    private ParamType filters = new ParamType();

    private void Page_Init(object sender, EventArgs e)
    {
      InitCrystalReport();
      if (myViewer.ParameterFieldInfo.Count > 0)
      {
        InitCrystalReportParameters();
        AddSubmitButton();
      }
    }

    private void InitCrystalReport()
    {
      SqlDataReader rdr = null;
      try
      {
       // Create cached report
       myViewer = null;
       cachedReportSource = null;
       myViewer = new CrystalDecisions.Web.CrystalReportViewer();
       myViewer.EnableParameterPrompt = false;
       myViewer.HasCrystalLogo = false;
       myViewer.Visible = false;
       string connString = System.Configuration.
              ConfigurationSettings.
              AppSettings["userreader"].ToString();
       rdr = SqlHelper.ExecuteReader(connString, 
             CommandType.StoredProcedure, 
             "usp_get_report_details", 
             new SqlParameter("@name", 
                 Session["name"].ToString()));
       while (rdr.Read())
       {
          report_file = (string)rdr["crystal_report_file"];
       }
       report_file = System.Configuration.ConfigurationSettings.
           AppSettings["root"].ToString() + report_file;
       cachedReportSource = new CachedReport(report_file);
       myViewer.ReportSource = cachedReportSource;
       contentMain.Controls.Add(myViewer);
      }
      catch
      {
        throw;
      }
    }

    private void InitCrystalReportParameters()
    {
      // Prepare parameters
      for (int i = 0; i < myViewer.ParameterFieldInfo.Count; i++)
      {
        switch (myViewer.ParameterFieldInfo[i].ParameterValueType.ToString())
        {
          case "DateTimeParameter":
            AddDateTimeFilter(myViewer.ParameterFieldInfo[i]);
            break;
          case "StringParameter":
            if (myViewer.ParameterFieldInfo[i].DefaultValues.Count > 0)
            {
              AddMultiSelectFilter(myViewer.ParameterFieldInfo[i]);
            }
            else
            {
              AddStringFilter(myViewer.ParameterFieldInfo[i]);
            }
            break;
        }
      }
    }
}

The cachedReportSource variable was created from a CachedReport class which looks like this:

public class CachedReport : ICachedReport
{
  private string reportFileName;
  private ReportDocument nonEmbeddedReportDocument;
  public CachedReport(string reportFileName)
  {
    this.reportFileName = reportFileName;
  }
  public virtual ReportDocument CreateReport()
  {
    if (nonEmbeddedReportDocument == null)
    {
      nonEmbeddedReportDocument = new ReportDocument();
      nonEmbeddedReportDocument.Load(reportFileName);
    }
    return nonEmbeddedReportDocument;
  }
  public virtual String 
         GetCustomizedCacheKey(RequestContext request)
  {return null;}
  public virtual Boolean IsCacheable
  {
     get {return true;}
     set {}
  }
  public virtual Boolean ShareDBLogonInfo
  {
    get {return false;}
    set {}
  }
  public virtual TimeSpan CacheTimeOut
  {
    get {return CachedReportConstants.DEFAULT_TIMEOUT;}
    set {}
  }
}

Rest of the code is as follows:

private void AddDateTimeFilter(CrystalDecisions.Shared.ParameterField param)
{
  try
  {
    Label myLabel = new Label();
    TextBox myTextBox = new TextBox();
    TableCell tc1 = new TableCell();
    TableCell tc2 = new TableCell();
    myLabel.Text = param.PromptText + ": ";
    myTextBox.ID = "text" + param.PromptText;
    myTextBox.ToolTip = param.PromptText;
    string calendarSetup = System.Configuration.
      ConfigurationSettings.AppSettings["calendarsetup"].
      ToString().Replace("mytext", myTextBox.ID);
    myTextBox.Attributes.Add("onclick", calendarSetup);

    tc1.Controls.Add(myLabel);
    tc2.Controls.Add(myTextBox);
    contentFilter1.Controls.Add(tc1);
    contentFilter2.Controls.Add(tc2);
    filters.AddDateTimeFilter(myTextBox);
  }
  catch
  {
    throw;
  }
}

private void AddMultiSelectFilter(CrystalDecisions.Shared.ParameterField param)
{
  const int width = 150;
  try
  {
     Label myLabel = new Label();
     xMilk.DropCheck myDropCheck = new xMilk.DropCheck();
     TableCell tc1 = new TableCell();
     TableCell tc2 = new TableCell();
     myLabel.Text = param.PromptText + ": ";
     myDropCheck.Width = width;
     for (int i = 0; i < param.DefaultValues.Count; i++)
     {
       ParameterDiscreteValue paramDV = new ParameterDiscreteValue();
       paramDV = (ParameterDiscreteValue)param.DefaultValues[i];
       ListItem myItem = new ListItem(paramDV.Value.ToString());
       myDropCheck.Items.Add(myItem);
     }

     tc1.Controls.Add(myLabel);
     tc2.Controls.Add(myDropCheck);
     contentFilter1.Controls.Add(tc1);
     contentFilter2.Controls.Add(tc2);
     filters.AddMultiSelectFilter(myDropCheck);
   }
   catch
   {
     throw;
   }
}

private void AddStringFilter(CrystalDecisions.Shared.ParameterField param)
{
  const int width = 150;
  try
  {
    Label myLabel = new Label();
    TextBox myTextBox = new TextBox();
    TableCell tc1 = new TableCell();
    TableCell tc2 = new TableCell();
    myLabel.Text = param.PromptText + ": ";
    myTextBox.Width = width;

    tc1.Controls.Add(myLabel);
    tc2.Controls.Add(myTextBox);
    contentFilter1.Controls.Add(tc1);
    contentFilter2.Controls.Add(tc2);
    filters.AddStringFilter(myTextBox);
  }
  catch
  {
    throw;
  }
}

private void AddSubmitButton()
{
  try
  {
    Button submit = new Button();
    TableCell tc1 = new TableCell();
    submit.Text = "Submit";
    tc1.Controls.Add(submit);
    contentFilter1.Controls.Add(tc1);
    submit.Click += new System.EventHandler(this.Submit_Click);
  }
  catch
  {
    throw;
  }
}

protected void Submit_Click(object sender, EventArgs e)
{
  for (int i = 0; i < myViewer.ParameterFieldInfo.Count; i++)
  {
    ParameterDiscreteValue objDiscreteValue = 
                               new ParameterDiscreteValue();
    ParameterField objParameterField = new ParameterField();
    string value = filters.GetParamTextByIndex(i);
    string filterType = filters.GetParamTypeByIndex(i);
    if (value != "")
    {
      switch (filterType)
      {
        case FilterType.DateTime:
          value += " 12:00";
          objDiscreteValue.Value = DateTime.ParseExact(value, 
                     "yyyy-MM-dd HH:mm", null);
          break;
        case FilterType.MultiSelect:
          objDiscreteValue.Value = value;
          break;
        case FilterType.String:
          objDiscreteValue.Value = value;
          break;
      }
    }
    else
    {
      objDiscreteValue.Value = null;
    }
    objParameterField = myViewer.ParameterFieldInfo[i];
    objParameterField.CurrentValues.Add(objDiscreteValue);
    myViewer.ParameterFieldInfo.RemoveAt(i);
    myViewer.ParameterFieldInfo.Insert(i, objParameterField);
  }
  myViewer.Visible = true;
}

The second part of this article can be found here.