Dynamic Crystal Reports

There are two ways to create Crystal reports dynamically: using preset columns and using the Crystal SDK. Both have their advantages and disadvantages, which you learn in the sections that follow.

Preset Columns

Using preset columns is the easier way to create a data-driven Crystal report. It's also cruder. By creating a report file and binding it to a typed DataSet with, say, ten columns, you can bind up to ten selected data elements chosen by the user. You need to create ten parameter fields as well to serve as column headers. Both the DataSet columns and the parameters fields are given consistent names such as DColumn1, DColumn2..DColumnn. Then, using the code in Listing 7-17, you can use SQL virtual column names to return a result set where the columns are named DColumn1, DColumn2..DColumnn.

Listing 7-17. Dynamic Crystal Reports Using Preset Columns private string DynamicCrystalReports() {

DynamicCrystalReport oDynamicCrystalReport; ParameterFields oParameterFields; ParameterField oParameterField; ParameterDiscreteValue oParameterDiscreteValue;

StringBuilder oSQL = new StringBuilder("SELECT ");

DataSet1 oDS = new DataSet1();

SqlConnection oSqlConnection;

SqlDataAdapter oSqlDataAdapter;

SqlCommand oSqlCommand;

oDynamicCrystalReport = new DynamicCrystalReport(); oParameterFields = new ParameterFields();

//Assign the description text to the parameter objects for the selected //fields. The iColumn variable assigns the parameters in sequence based on name.

foreach (object oItem in lstColumns.CheckedItems) {

oParameterField = new ParameterField(); oParameterField.Name = "DataColumn" + iColumn.ToString(); oParameterDiscreteValue = new ParameterDiscreteValue(); oParameterDiscreteValue.Value = ((ListItem)oItem).Text; oParameterField.CurrentValues.Add(oParameterDiscreteValue); oParameterFields.Add(oParameterField);

oSQL.Append(((ListItem)oItem).Value + " AS DColumn" + iColumn.ToString()); oSQL.Append(", ");

iColumn++;

//There are 10 hard-coded columns in this report. Place an empty string

//in the header fields for the unused columns iColCnt = oDynamicCrystalReport.ParameterFields.Count;

oParameterField = new ParameterField(); oParameterField.Name = "DataColumn" + iColumn.ToString(); oParameterDiscreteValue = new ParameterDiscreteValue(); oParameterDiscreteValue.Value = string.Empty; oParameterField.CurrentValues.Add(oParameterDiscreteValue); oParameterFields.Add(oParameterField);

iColumn++;

//Pass in the collection of 10 named parameter objects crystalReportViewer1.ParameterFieldInfo = oParameterFields;

//Whack the trailing comma oSQL.Remove(oSQL.Length - 2, 2);

oSQL.Append(" FROM Employees");

//Execute the SQL oSqlConnection = new

SqlConnection("Data Source=SETON-NOTEBOOK; "+ "Initial catalog=Northwind;"+

"Integrated security=SSPI;Persist security info=False"); oSqlConnection.Open();

oSqlDataAdapter = new SqlDataAdapter(); oSqlCommand = new SqlCommand();

oSqlCommand.CommandText = oSQL.ToString(); oSqlCommand.Connection = oSqlConnection; oSqlDataAdapter.SelectCommand = oSqlCommand;

//Make sure the table name matches the one used in DataSet1.Designer.cs. //DataTable1 is the default name. If you fail to name it properly, the //headers will appear but the report data will not. oSqlDataAdapter.Fill(oDS, "DataTable1");

//Assign the DataTable to the report oDynamicCrystalReport.SetDataSource(oDS); crystalReportViewer1.ReportSource = oDynamicCrystalReport;

return oSQL.ToString();

The results can be seen in Figure 7-6.

Viewer Crystall Report 2008 Net
Figure 7-6. Crystal reports output

Using the Crystal SDK

If you have Crystal Reports 2008, you can also create a report using its SDK's object model. Just as in Word or Excel VBA, for example, anything you can do with Crystal Reports from the designer interface you can also accomplish programmatically via the SDK. Using the same interface shown in the previous section, you can build a report using the code shown in Listing 7-18.

Listing 7-18. Dynamic Crystal Report Using SDK

CrystalDecisions.CrystalReports.Engine.ReportDocument oReportDocument;

ISCDReportClientDocument oReportClientDocument;

Section oSection;

FontColor oDetailHeaderTextFont;

FontColor oDetailHeaderFieldFont;

System.Data.DataSet oDS;

string szSQL;

int iWidth = 2000;

int iHeight = 200; //Define font objects oDetailHeaderTextFont = CreateFont("Arial", 10, false, true, false, false, System.Drawing.Color.Black); oDetailHeaderFieldFont = CreateFont("Arial", 10, false, false, false, false, System.Drawing.Color.Black);

//Create a new ReportDocument oReportDocument = new CrystalDecisions.CrystalReports.Engine.ReportDocument();

//Access the ReportClientDocument in the ReportDocument oReportClientDocument = oReportDocument.ReportClientDocument;

//create new report document oReportClientDocument.New();

//Iterate through the selected columns to build an SQL string szSQL = BuildSQL();

//Add the data source to the report. This is needed so the data //structure is available when the field objects are added. oDS = AddTable(oReportClientDocument, szConnectString, szSQL);

//Iterate through the selected columns foreach (object oItem in lstColumns.CheckedItems) {

//Obtain a reference to the page header section oSection = oReportClientDocument.ReportDefinition.PageHeaderArea.Sections[0]; //Add a text object as a column header

AddTextField(oReportClientDocument, oSection, oDetailHeaderTextFont, CrAlignmentEnum.crAlignmentLeft, ((ListItem)oItem).Text, iLeft, iTop, iWidth, iHeight);

//Obtain a reference to the report details section oSection = oReportClientDocument.ReportDefinition.DetailArea.Sections[0];

//Add the data field bound to a data source column AddField(oReportClientDocument, oSection, oDetailHeaderFieldFont,

CrAlignmentEnum.crAlignmentLeft, "Table", ((ListItem)oItem).Value, iLeft, iTop, iWidth, iHeight);

oReportDocument.SetDataSource(oDS);

crystalReportViewerl.ReportSource = oReportDocument;

This code iterates through the user-selected columns, creates a SQL statement pulling only that data, instantiates a report object, adds the column headers and data columns to it, sets the data source, and displays it in the Crystal viewer. This report doesn't need to persist to disk. If you wish to do this, you can use the code shown in Listing 7-19.

Listing 7-19. Writing a Dynamically Created Report to a Disk File object oTarget = @"c:\temp";

oReportClientDocument.SaveAs(@"myreport.rpt", ref oTarget, ((int)CdReportClientDocumentSaveAsOptionsEnum. cdReportClientDocumentSaveAsOverwriteExisting));

oReportDocument.Load(@"c:\temp\myreport.rpt");

Because the SQL is generated at runtime, you can bind it to the report object using the code shown in Listing 7-20. Doing so at the beginning of the process is necessary so the data structures are available to the reports as data columns are added.

Listing 7-20. AddTable() Method private System.Data.DataSet AddTable(ISCDReportClientDocument oReportClientDocument, string szConnectString, string szSOL)

SqlDatabase oSqlDatabase = new SqlDatabase(szConnectString); ISCRDataSet olSCRDataSet; System.Data.DataSet oDS;

using (DbCommand oDbCommand = oSqlDatabase.GetSqlStringCommand(szSOL)) {

oDS = oSqlDatabase.ExecuteDataSet(oDbCommand);

oISCRDataSet = CrystalDecisions.ReportAppServer.

DataSetConversion.DataSetConverter.Convert(oDS); oReportClientDocument.DatabaseController.AddDataSource(oISCRDataSet);

return oDS;

You add data fields one by one using the AddField() method shown in Listing 7-21.

Listing 7-21. AddField() method private void AddField(ISCDReportClientDocument oReportClientDocument,

Section oSection, FontColor oFontColor, CrAlignmentEnum sAlignmentEnum, string szTableName, string szFieldName, int iLeft, int iTop, int iWidth, int iHeight)

ISCRTable oISCRTable; ISCRField oField;

ISCRReportObject oISCRReportObject; FieldObject oFieldObject;

CrystalDecisions.ReportAppServer.DataDefModel.Table oTable;

oISCRTable = oReportClientDocument.Database. Tables.FindTableByAlias(szTableName);

//Extract the table or stored procedure and cast it to a Table object oTable = ((CrystalDecisions.ReportAppServer.DataDefModel.Table)oISCRTable);

//Cast this field reference to a Field object oField = ((Field)oTable.DataFields.FindField(szFieldName, CrFieldDisplayNameTypeEnum.crFieldDisplayNameName, CrystalDecisions.ReportAppServer.DataDefModel. CeLocale.ceLocaleUserDefault));

////Instantiate a FieldObject and set the properties ////and display the data and position on the page. oFieldObject = new FieldObject();

oFieldObject.Kind = CrReportObjectKindEnum.crReportObjectKindField;

oFieldObject.DataSource = oField.FormulaForm;

oFieldObject.Left = iLeft;

oFieldObject.Top = iTop;

oFieldObject.Width = iWidth;

oFieldObject.Height = iHeight;

oFieldObject.FieldValueType = oField.Type;

oFieldObject.FontColor = oFontColor;

oFieldObject.Format.HorizontalAlignment = sAlignmentEnum;

oISCRReportObject = ((ISCRReportObject)oFieldObject); ////Add the field to the report oReportClientDocument.ReportDefController.ReportObjectController. Add(oISCRReportObject, oSection, 0);

You can add column headers in the form of TextObjects. Working with TextObjects is one of the less intuitive parts of the Crystal Reports object model. Adding a text field (shown in Listing 7-22) is a rather convoluted process involving a number of objects—five to be exact—just to display a piece of text on the report. First you need a ParagraphTextElement object that contains the text you wish to display, which you then need to add to a ParagraphElements collection object. This collection is then added to the ParagraphElements property of a Paragraph object, which in turn is added to a Paragraphs collection. Finally, the Paragraphs collection object is added to the Paragraphs property of a TextObject. The TextObject is the visual element that holds settings for size and position.

Listing 7-22. AddTextField() method private void AddTextField(ISCDReportClientDocument oReportClientDocument, Section oSection, FontColor oFontColor, CrAlignmentEnum sAlignmentEnum, string szText, int iLeft, int iTop, int iWidth, int iHeight)

TextObject oTextObject; ISCRReportObject oISCRReportObject; Paragraphs oParagraphs; Paragraph oParagraph; ParagraphElements oParagraphElements; ParagraphTextElement oParagraphTextElement;

//Instantiate the necessary objects oTextObject = new TextObject(); oParagraphs = new Paragraphs(); oParagraph = new Paragraph(); oParagraphElements = new ParagraphElements(); oParagraphTextElement = new ParagraphTextElement();

//Set the displayed text to the ParagraphTextElement object oParagraphTextElement.Text = szText;

oParagraphTextElement.Kind = CrParagraphElementKindEnum.

crParagraphElementKindText;

//Add the ParagraphTextElement to the ParagraphElements collection oParagraphElements.Add(oParagraphTextElement);

//Set the ParagraphElements collection to the ParagraphElements //property of the Paragraph object and set the text alignment oParagraph.ParagraphElements = oParagraphElements; oParagraph.Alignment = sAlignmentEnum;

//Add the Paragraph object to the Paragraphs collection oParagraphs.Add(oParagraph);

//Set up the TextObject by assigning the Paragraphs collection object to //its Paragraphs property. Also, set the size and position. oTextObject.Kind = CrReportObjectKindEnum.crReportObjectKindText; oTextObject.Paragraphs = oParagraphs; oTextObject.Left = iLeft; oTextObject.Top = iTop; oTextObject.Width = iWidth; oTextObject.Height = iHeight; oTextObject.FontColor = oFontColor;

oISCRReportObject = ((ISCRReportObject) oTextObject);

//Finally, add the TextObject to the report oReportClientDocument.ReportDefController.ReportObjectController. Add(oISCRReportObject, oSection, -1);

You can take this process much further by adding sections, groups, formulas, and parameters all at runtime to produce a data-driven report completely customized to your needs.

Was this article helpful?

0 0

Responses

  • Anil.MR
    Showing error ISCDReportClientDocument type not found<br />
    8 years ago
  • Habaccuc
    How to add new fields to a crystal report programmatically FieldObject .ReportObjectController.Add?
    8 years ago
  • Birikti
    How to add column dynamically in crystal report?
    7 years ago
  • chiara
    How to dynamically create header and field data in crystal report?
    7 years ago
  • daniela
    How to create dynamic column in crystal report?
    7 years ago
  • Aaron Stauffer
    How to add datafield definition dynmically in crystal report?
    7 years ago
  • PHILLIPP COLE
    Can assign header name dynamically in crystal report?
    7 years ago
  • mungo gaukrogers
    How to bind dynamic textobject in crystal report?
    7 years ago
  • Amanda Button
    How to make parameter driven crystal reports 2008?
    7 years ago
  • fantino
    How to create report with list collection visual basic.net?
    3 years ago
  • SANDRA KUNZE
    How to insert picture dynamically in crystal report vb?
    1 year ago

Post a comment