Reply to comment

Crystal Reports and ASP.NET

Submitted by Ben Hodson on Thu, 09/18/2008 - 16:52

There are TONS of examples out there of using Crystal Reports in ASP.NET in VB.NET or C# AS LONG as you want to use the CrystalReportViewer control. But what if you just want to load a report from a file path and then export it as a PDF or some other action on the report without using the CrystalReportViewer?

Let me throw another wrench in this. Also, let's say your report has Parameters, Formulas, and SubReports. First, let me get a few gotchas out of the way.



First, create your reference to a report to load:

string sPathToReport = "C:\\myreport.rpt";
// create a report object to hold the report
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
// load the report from the path specified
report.Load(sPathToReport);

Now, if your report is a "pull" model, then it does it's own connection to the database. You will need to set the connection information. It is kind of clunky but the way the ReportDocument object works, you have to set the connection information for each table in the report object. Here is the code to set the connection information for the report tables:

// set up login to database so report can use it to access the database tables.
CrystalDecisions.Shared.ConnectionInfo conInfo = new CrystalDecisions.Shared.ConnectionInfo();
conInfo.Password = "mypassword";
conInfo.ServerName = "myservername";
conInfo.UserID = "myusername";
conInfo.DatabaseName = "mydatabasename";
 
// loop through all tables in report and set connection information.
for (int i = 0; i < report.Database.Tables.Count; i++)
{
    CrystalDecisions.CrystalReports.Engine.Table tblCurrent = report.Database.Tables[i];
    CrystalDecisions.Shared.TableLogOnInfo logonInfo = tblCurrent.LogOnInfo;
    logonInfo.ConnectionInfo = conInfo;
    tblCurrent.ApplyLogOnInfo(logonInfo);
    tblCurrent.Location = logonInfo.TableName;
}

Now all tables in the report are setup and ready to connect to the database. Next we can edit values for any of the formulas in the report like this:

// set forumula fields if necessary
CrystalDecisions.CrystalReports.Engine.FormulaFieldDefinitions formulaList = report.DataDefinition.FormulaFields;
for (int i = 0; i < formulaList.Count; i++)
{
    // DON'T access the field this way.  If "MyFormulaName" does not exist in the collection,
    // instead of just returning null, it actually throws an Index out of Range expection! 
    // Don't ask me why they did it this way but this method of accessing the formula is not recommended.
    // In order to use the method, you would have to wrap this in a Try/Catch and code for an 
    // expected exception but Exceptions are slow so you never want to code by counting on them happening.
    formulaList["MyFormulaName"].Text = "\"" + "the formula text to set" + "\"";
 
    // Instead, just loop the formulas and test by name to be safe.
    if (formulaList[i].Name == "MyFormulaName")
    {
        // if the formula is a string type, then you MUST wrap it in quotes.
        formulaList[i].Text = "\"" + "the formula text to set" + "\"";
 
        // if the formula is an int type, then DO NOT wrap it in quotes.
        formulaList[i].Text = "1";
    }
}

Here is how you set values for the parameters for this report:

// set parameter fields values
CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinitions parameterList = report.DataDefinition.ParameterFields;
for (int i = 0; i < parameterList.Count; i++)
{
    // Here is how you can tell if this parameter is a linked parameter or not.
    if (parameterList[i].IsLinked())
    {
        // do something with the linked parameter.
    }
 
    // This let's you check if a parameter already has a value or not.
    if (!parameterList[i].HasCurrentValue)
    {
        // you can test the parameter 
        if (parameterList[i].ValueType == CrystalDecisions.Shared.FieldValueType.DateTimeField || parameterList[i].ValueType == CrystalDecisions.Shared.FieldValueType.DateField)
        {
            // If the parameter is a range field, add a date time range.
            CrystalDecisions.Shared.ParameterRangeValue rangeValue = new CrystalDecisions.Shared.ParameterRangeValue();
            rangeValue.LowerBoundType = CrystalDecisions.Shared.RangeBoundType.BoundInclusive;
            rangeValue.UpperBoundType = CrystalDecisions.Shared.RangeBoundType.BoundInclusive;
            rangeValue.StartValue = DateTime.Parse("1/1/1899");
            rangeValue.EndValue = DateTime.Parse("1/1/1899");
            report.SetParameterValue(parameterList[i].ParameterFieldName, rangeValue);
        }
 
        // Again, DON'T access a paramater by it's name unless you are sure it is there because if
        // this name does not exist in the list, it will throw an exception.  Just like the formulas 
        // shown previously, it is better to test by name so we don't throw the exception.
        string sParameterName = parameterList["MyParameterName"].Name;
        
        // Test the parameter by field name and add a value if you want.
        if (parameterList[i].ParameterFieldName == "MyParameterName")
        {
            // NOW, here is the deal.  If you want to set a parameter, practically EVERY example I found online, 
            // says to do it this way:
            CrystalDecisions.Shared.ParameterDiscreteValue discreteValue = new CrystalDecisions.Shared.ParameterDiscreteValue();
            discreteValue.Value = "Some value you want to set here";
            parameterList[i].CurrentValues.Add(discreteValue);
            // But, this will NOT work.  This doesn't actually set the parameter's value.  It just adds a value to the CurrentValues list.
            // If you try to run with this code, your report will fail to load with the exception "Missing Paramater Values".
 
            // Instead, set the paramater's value this way.
            report.SetParameterValue(parameterList[i].ParameterFieldName, "Some value you want to set here");
        }
        else
        {
            // If you're parameter doesn't have a value but does have a default value, here
            // is an easy way to set the parameter's value to it's default value (Should do this automatically
            // for you in my opinion but it doesn't unfortunately.)
            if (parameterList[i].DefaultValues.Count > 0)
            {
                // just set parameter to it's default value
                report.SetParameterValue(parameterList[i].ParameterFieldName, parameterList[i].DefaultValues[0]);
            }
        }
    }
}

There are a few other things I see in a lot of posts online that need to be remembered:

// don't EVER call this unless you know what you are doing.  It will delete all your paramaters you set up.
report.VerifyDatabase();
 
// Don't EVER call this unless you know what you are doing.  It will reset all your parameters and delete everything you have edited.
report.Refresh();

If you want to export this report to PDF, you can do it with this simple line of code:

report.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, "C:\\MyNewPdfName.pdf");

The only other thing I would recommend doing is closing the report when you are done using it like this:

// Close the report after you have done something with it.  This is good for clean up and
// also I found a couple of times, that the report became locked on disk if I didn't do this.
report.Close();

Reply