Need a simple CRM and Project Management system?
Check out JobNimbus - CRM for Contractors and Service Professionals.

Crystal Reports and ASP.NET

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();

re "But, this will NOT work."

The code you have above is fine if you have a single value or single range going into the parameter but fails if you have a list of values (eg 3 or 4 customer codes)

I found that the following code worked just fine (I guess it is the ApplyCurrentValues command that sets the parameters value)

Dim crParameterFieldDefs As ParameterFieldDefinitions
Dim crParameterFieldDef As ParameterFieldDefinition
Dim crParameterValues As New ParameterValues
Dim crParameterDiscreteValue As New ParameterDiscreteValue

crParameterFieldDefs = report.DataDefinition.ParameterFields

For Each ip As IncomingPrompt In Prompts
crParameterFieldDef = crParameterFieldDefs.Item(ip.PromptName)
crParameterValues = crParameterFieldDef.CurrentValues

crParameterValues.Clear()

For x As Integer = 0 To ip.AnswerList.Count - 1
crParameterDiscreteValue = New ParameterDiscreteValue
crParameterDiscreteValue.Value = ip.AnswerList.Item(x).Answer
crParameterValues.Add(crParameterDiscreteValue)
Next

crParameterFieldDef.ApplyCurrentValues(crParameterValues)

Next

thanks

thanks for the infos,
but 2 things from my side:

1) i had to add

report.SetDatabaseLogon( db_user_name, db_pwd );

after report.Load() in order to add values to parameters, else i got exceptions ("cant connect" and such)

2) report.SetParameterValue(parameterList[i].ParameterFieldName, parameterList[i].DefaultValues[0]); // doesnt assign THE default value, but the first stored value in the default values array, actually there can be 10 stored "default" values, but the actual single default value that can be set in the report-designer might be at array position 5 or whatever.
anyone any idea how to get the real default value ?

linked parameter

Hi,
what is supposed to do where you say:
// do something with the linked parameter.
???

You have made a great job, very simple and saying what we should not do :-)
Thanks!

Thanks

This is very helpful to understand