Solved: How to create a report based on a DataSet programa...

Former Member · ‎08-26-2008

I'm working on a CR 2008 Add-in.

Usage of this add-in is: Let the user choose from a list of predefined datasets, and create a totally empty report with this dataset attached to is. So the user can create a report based on this dataset.

I have a dataset in memory, and want to create a new report in cr2008.

The new report is a blank report (with no connection information).

If I set the ReportDocument.SetDataSource(Dataset dataSet) property, I get the error:

The report has no tables.

So I must programmatically define the table definition in my blank report.

I found the following article: https://boc.sdn.sap.com/node/869, and came up with something like this:


internal class NewReportWorker : Worker
   {
      public NewReportWorker(string reportFileName)
         : base(reportFileName)
      {
      }

public override void Process()
      {
         DatabaseController databaseController = ClientDoc.DatabaseController;

         Table table = new Table();
         string tabelName = "Table140";
         table.Name = tabelName;
         table.Alias = tabelName;
         table.QualifiedName = tabelName;
         table.Description = tabelName;

         var fields = new Fields();

         var dbField = new DBField();
         var fieldName = "ID";
         dbField.Description = fieldName;
         dbField.HeadingText = fieldName;
         dbField.Name = fieldName;
         dbField.Type = CrFieldValueTypeEnum.crFieldValueTypeInt64sField;
         fields.Add(dbField);

         dbField = new DBField();
         fieldName = "IDLEGITIMATIEBEWIJS";
         dbField.Description = fieldName;
         dbField.HeadingText = fieldName;
         dbField.Name = fieldName;
         dbField.Type = CrFieldValueTypeEnum.crFieldValueTypeInt64sField;
         fields.Add(dbField);

         // More code for more tables to add.

         table.DataFields = fields;

         //CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo info = 
         //   new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();
         //info.Attributes.Add("Databse DLL", "xxx.dll");
         //table.ConnectionInfo = info;

         // Here an error occurs.
         databaseController.AddTable(table, null);

         ReportDoc.SetDataSource( [MyFilledDataSet] );

         //object path = @"d:\logfiles\";
         //ClientDoc.SaveAs("test.rpt", ref path, 0);
      }
   }
}

The object ClientDoc referes to a ISCDReportClientDocument in a base class:


   internal abstract class Worker
   {
      private ReportDocument _ReportDoc;
      private ISCDReportClientDocument _ClientDoc;
      private string _ReportFileName;

      public Worker(string reportFileName)
      {
         _ReportFileName = reportFileName;
         _ReportDoc = new ReportDocument();
         // Load the report from file path passed by the designer.
         _ReportDoc.Load(reportFileName);
         // Create a RAS Document through In-Proc RAS through the RPTDoc.
         _ClientDoc = _ReportDoc.ReportClientDocument;
      }

      public string ReportFileName
      {
         get
         {
            return _ReportFileName;
         }
      }

      public ReportDocument ReportDoc
      {
         get
         {
            return _ReportDoc;
         }
      }

      public ISCDReportClientDocument ClientDoc
      {
         get
         {
            return _ClientDoc;
         }
      }

But I get an "Unspecified error" on the line databaseController.AddTable(table, null);

What am i doing wrong? Or is there another way to create a new report based on a DataSet in C# code?

Former Member · ‎08-27-2008

/*****************************************************************
		 * the following two string values can be modified to reflect your system
		************************************************************************************************/
string mdb_path = "C:\\program files\\crystal decisions\\crystal reports 9\\samples\\en\\databases\\xtreme.mdb";    // path to xtreme.mdb file
string xsd_path = "C:\\Crystal\\rasnet\\ras9_csharp_win_datasetreport\\customer.xsd";  



		

		OleDbConnection m_connection;					
		OleDbDataAdapter m_adapter;						
		System.Data.DataSet m_dataset;					/
    
		
		ReportClientDocument m_crReportDocument;				     Field m_crFieldCustomer;
		Field m_crFieldCountry;

		void CreateData()
		{
			
	m_connection = new OleDbConnection();
m_connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdb_path;

m_adapter = new OleDbDataAdapter("select * from Customer where Country='Canada'", m_connection);

m_dataset = new System.Data.DataSet();
			m_adapter.Fill(m_dataset, "Customer");

			// create a schema file
			m_dataset.WriteXmlSchema(xsd_path);
		}

		
void AddDataSourceUsingDataSet( ReportClientDocument rcDoc,			System.Data.DataSet data)		
		{
		DataSetConverter.AddDataSource(rcDoc, data);
		}

		
void AddDataSourceUsingSchemaFile( ReportClientDocument rcDoc, string schema_file_name, string table_name, System.Data.DataSet data)		
{
	PropertyBag crLogonInfo; PropertyBag crAttributes;						ConnectionInfo crConnectionInfo;				CrystalDecisions.ReportAppServer.DataDefModel.Table crTable;
			
			// create logon property
			crLogonInfo = new PropertyBag();
			crLogonInfo["XML File Path"] = schema_file_name;

			// create logon attributes
			crAttributes = new PropertyBag();
			crAttributes["Database DLL"] = "crdb_adoplus.dll";
			crAttributes["QE_DatabaseType"] = "ADO.NET (XML)";
			crAttributes["QE_ServerDescription"] = "NewDataSet";
			crAttributes["QE_SQLDB"] = true;
			crAttributes["QE_LogonProperties"] = crLogonInfo;

			// create connection info
			crConnectionInfo = new ConnectionInfo();
			crConnectionInfo.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;
			crConnectionInfo.Attributes = crAttributes;

			// create a table
			crTable = new CrystalDecisions.ReportAppServer.DataDefModel.Table();
			crTable.ConnectionInfo = crConnectionInfo;
			crTable.Name = table_name;
			crTable.Alias = table_name;

			// add a table
			rcDoc.DatabaseController.AddTable(crTable, null);

			// pass dataset
			rcDoc.DatabaseController.SetDataSource(DataSetConverter.Convert(data), table_name, table_name);
		}

	void CreateReport()
	{
			int iField;

			// create ado.net dataset
			CreateData();

			// create report client document
			m_crReportDocument = new ReportClientDocument();

			m_crReportDocument.ReportAppServer = "127.0.0.1";

			// new report document
			m_crReportDocument.New();

	AddDataSourceUsingSchemaFile(m_crReportDocument, xsd_path, "Customer",
m_dataset);
						
	iField = m_crReportDocument.Database.Tables[0].DataFields.Find("Customer Name", CrFieldDisplayNameTypeEnum.crFieldDisplayNameName, CeLocale.ceLocaleUserDefault);
m_crFieldCustomer =
(Field)m_crReportDocument.Database.Tables[0].DataFields[iField];
			iField = m_crReportDocument.Database.Tables[0].DataFields.Find("Country", CrFieldDisplayNameTypeEnum.crFieldDisplayNameName, CeLocale.ceLocaleUserDefault);
m_crFieldCountry = (Field)m_crReportDocument.Database.Tables[0].DataFields[iField];

						m_crReportDocument.DataDefController.ResultFieldController.Add(-1, m_crFieldCustomer);
			m_crReportDocument.DataDefController.ResultFieldController.Add(-1, m_crFieldCountry);

			// view report
			crystalReportViewer1.ReportSource = m_crReportDocument;
		}

		public Form1()
		{
			//
			// Required for Windows Form Designer support
			//
			InitializeComponent();

			// Create Report
			CreateReport();

			//
			// TODO: Add any constructor code after InitializeComponent call
			//
		}

Former Member · ‎08-29-2008

Hai Dennis Dam ,

I also doing the same task what you done.

I try your code using Visual Studio 2005,Asp.Net2.0.

I created those Two classes but i want to know how can we

use these files from .aspx code behind file because i cant

access the file worker.cs and NewReportWorker.cs

from .aspx.cs file. Could you please post the code how to

use this code from .aspx file

Former Member · ‎08-28-2008

Finally here the code that works for me....

   internal class NewReportWorker : Worker
   {
      public override void Process()
      {
         var reportDataSet = [MYDATASET];

         // Save the dataset with schema to a temporary file.
         var tempFileName = Path.GetTempFileName() + ".xml";
         reportDataSet.WriteXml(tempFileName, XmlWriteMode.WriteSchema);

         PropertyBag logonInfo = new PropertyBag();
         logonInfo.EnsureCapacity(1);
         logonInfo.Add("XML File Path", tempFileName);

         PropertyBag attributes = new PropertyBag();
         attributes.EnsureCapacity(5);
         attributes.Add("Database DLL", "crdb_adoplus.dll");
         attributes.Add("QE_DatabaseType", "ADO.NET (XML)");
         attributes.Add("QE_ServerDescription", "NewDataSet");
         attributes.Add("QE_SQLDB", true);
         attributes.Add("QE_LogonProperties", logonInfo);

         ConnectionInfo connectionInfo = new ConnectionInfo();
         connectionInfo.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;
         connectionInfo.Attributes = attributes;

         // Convert 'my table' to a crystal report table.
         ISCRTable table = DataSetConverter.Convert(reportDataSet).Tables[0];
         table.ConnectionInfo = connectionInfo;
         ClientDoc.DatabaseController.AddTable(table, null);
         SaveAndClose();
      }
   }

   internal abstract class Worker : IDisposable
   {
      private ReportDocument _ReportDoc;
      private ISCDReportClientDocument _ClientDoc;
      private string _ReportFileName;

      public Worker(string reportFileName)
      {
         _ReportFileName = reportFileName;
         _ReportDoc = new ReportDocument();
         // Load the report from file path passed by the designer.
         _ReportDoc.Load(reportFileName);
         // Create a RAS Document through In-Proc RAS through the RPTDoc.
         _ClientDoc = _ReportDoc.ReportClientDocument;
      }

      public string ReportFileName
      {
         get { return _ReportFileName; }
      }

      public ReportDocument ReportDoc
      {
         get { return _ReportDoc; }
      }

      public ISCDReportClientDocument ClientDoc
      {
         get { return _ClientDoc; }
      }

      public abstract void Process();

      public void SaveAndClose()
      {
         // The RAS SDK needs to be used to save the temporary report.
         _ClientDoc.Save();
         // Finally close the report document.
         _ClientDoc.Close();
      }

      public void Dispose()
      {
         if (_ReportDoc.IsLoaded)
         {
            _ReportDoc.Close();
         }
         GC.SuppressFinalize(this);
      }
   }

Problem solved. Thanx anyone who replied on this thread.

Former Member · ‎08-28-2008

To Ted Ueda, TristaL and Alphonse Kouassi.

Where did you all get this knowledge. I searched google, the forum and crystal reports website, but cannot find any information about all of this...

Former Member · ‎08-28-2008

Hi,

In your code I cannot see any implementation of the PropertyBags as Logon Attributes for the ConnectionInfo. This might be at the origin of the issue. I will advise you to review the code in considering the implementation of the PropertyBags. Here is again another version of the code I previously sent to you.

//======================================================================

// Dataset
		OleDbConnection m_connection;	// ado.net connection
		OleDbDataAdapter m_adapter;		// ado.net data adapter
		System.Data.DataSet m_dataset;	// ado.net dataset
    
		//		*** NOTE ***
		//		ASPNET_wp user needs read permission to mdb_path, and both read and write permission to xsd_path
		//Set the path to your database
		string mdb_path =@"D:\Program Files\Business Objects\Crystal Reports 11\Samples\en\Databases\xtreme.mdb";
		//Set the path to where the customer schema file will be created
		string xsd_path = "D:\\WINNT\\Temp\\customer.xsd";
		// CR variables
		ReportClientDocument m_crReportDocument;						// report client document
		PropertyBag m_crLogonInfo;										// logon info
		PropertyBag m_crAttributes;										// logon attributes
		ConnectionInfo m_crConnectionInfo;								// connection info
		CrystalDecisions.ReportAppServer.DataDefModel.Table m_crTable;	// table

		int iField;
		Field m_crFieldCustomer;
		protected CrystalDecisions.Web.CrystalReportViewer CrystalReportViewer1;										// customer field
		Field m_crFieldCountry;											// country field
	
		private void Page_Load(object sender, System.EventArgs e)
		{
			CreateReport();
		}
		void CreateData()
		{
			// Create OLEDB connection
			m_connection = new OleDbConnection();
			m_connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdb_path;

			// Create Data Adapter
			m_adapter = new OleDbDataAdapter("select * from Customer where Country='Canada'", m_connection);

			// create dataset and fill
			m_dataset = new System.Data.DataSet();
			m_adapter.Fill(m_dataset, "Customer");

			// create a schema file
			m_dataset.WriteXmlSchema(xsd_path);
		}
		void CreateReport()
		{
			// create ado.net dataset
			CreateData();

			// create report client document
			m_crReportDocument = new ReportClientDocument();

			// set RAS server location
			m_crReportDocument.ReportAppServer = "localhost";

			// new report document
			m_crReportDocument.New();

			// create logon property
			m_crLogonInfo = new PropertyBag();
			m_crLogonInfo["XML File Path"] = xsd_path;

			// create logon attributes
			m_crAttributes = new PropertyBag();
			m_crAttributes["Database DLL"] = "crdb_adoplus.dll";
			m_crAttributes["QE_DatabaseType"] = "ADO.NET (XML)";
			m_crAttributes["QE_ServerDescription"] = "NewDataSet";
			m_crAttributes["QE_SQLDB"] = true;
			m_crAttributes["QE_LogonProperties"] = m_crLogonInfo;

			// create connection info
			m_crConnectionInfo = new ConnectionInfo();
			m_crConnectionInfo.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;
			m_crConnectionInfo.Attributes = m_crAttributes;

			// create a table
			m_crTable = new CrystalDecisions.ReportAppServer.DataDefModel.Table();
			m_crTable.ConnectionInfo = m_crConnectionInfo;
			m_crTable.Name = "Customer";
			m_crTable.Alias = "Customer";

			// add a table
			m_crReportDocument.DatabaseController.AddTable(m_crTable, null);

			// grab customer name and country field
			iField = m_crReportDocument.Database.Tables[0].DataFields.Find("Customer Name", CrFieldDisplayNameTypeEnum.crFieldDisplayNameName, CeLocale.ceLocaleUserDefault);
			m_crFieldCustomer = (Field)m_crReportDocument.Database.Tables[0].DataFields[iField];
			iField = m_crReportDocument.Database.Tables[0].DataFields.Find("Country", CrFieldDisplayNameTypeEnum.crFieldDisplayNameName, CeLocale.ceLocaleUserDefault);
			m_crFieldCountry = (Field)m_crReportDocument.Database.Tables[0].DataFields[iField];

			// add customer name and country field
			m_crReportDocument.DataDefController.ResultFieldController.Add(-1, m_crFieldCustomer);
			m_crReportDocument.DataDefController.ResultFieldController.Add(-1, m_crFieldCountry);

			// pass dataset to report
			m_crReportDocument.DatabaseController.SetDataSource(DataSetConverter.Convert(m_dataset), "Customer", "Customer");

			// view report
			CrystalReportViewer1.ReportSource = m_crReportDocument;
		}

//=======================================================================

Try and make it work first before using in your project.

Cheers

Former Member · ‎08-27-2008

I have nothing to add to Ted's answer.

Cheers

Former Member · ‎08-27-2008

Hi,

Have a look at the snippet code below written for version 9 that you might accommodate to CR 2008, it demonstrates how to create a report based on a DataSet programmatically.

//=========================================================================

/************************************************************************************************

+ * the following two string values can be modified to reflect your system+

+ ************************************************************************************************/+

+ string mdb_path = "C:
program files
crystal decisions
crystal reports 9
samples
en
databases
xtreme.mdb"; // path to xtreme.mdb file+

+ string xsd_path = "C:
Crystal
rasnet
ras9_csharp_win_datasetreport
customer.xsd"; // path to customer schema file+

+ // Dataset+

+ OleDbConnection m_connection; // ado.net connection+

+ OleDbDataAdapter m_adapter; // ado.net adapter+

+ System.Data.DataSet m_dataset; // ado.net dataset+

+ // CR variables+

+ ReportClientDocument m_crReportDocument; // report client document+

+ Field m_crFieldCustomer;+

+ Field m_crFieldCountry;+

+ void CreateData()+

+ {+

+ // Create OLEDB connection+

+ m_connection = new OleDbConnection();+

+ m_connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdb_path;+

+ // Create Data Adapter+

+ m_adapter = new OleDbDataAdapter("select * from Customer where Country='Canada'", m_connection);+

+ // create dataset and fill+

+ m_dataset = new System.Data.DataSet();+

+ m_adapter.Fill(m_dataset, "Customer");+

+ // create a schema file+

+ m_dataset.WriteXmlSchema(xsd_path);+

+ }+

+ // Adds a DataSource using dataset. Since this does not require intermediate schema file, this method+

+ // will work in a distributed environment where you have IIS box on server A and RAS Server on server B.+

+ void AddDataSourceUsingDataSet(+

+ ReportClientDocument rcDoc, // report client document+

+ System.Data.DataSet data) // dataset+

+ {+

+ // add a datasource+

+ DataSetConverter.AddDataSource(rcDoc, data);+

+ }+

+ // Adds a DataSource using a physical schema file. This method require you to have schema file in RAS Server+

+ // box (NOT ON SDK BOX). In distributed environment where you have IIS on server A and RAS on server B,+

+ // and you execute CreateData above, schema file is created in IIS box, and this method will fail, because+

+ // RAS server cannot see that schema file on its local machine. In such environment, you must use method+

+ // above.+

+ void AddDataSourceUsingSchemaFile(+

+ ReportClientDocument rcDoc, // report client document+

+ string schema_file_name, // xml schema file location+

+ string table_name, // table to be added+

+ System.Data.DataSet data) // dataset+

+ {+

+ PropertyBag crLogonInfo; // logon info+

+ PropertyBag crAttributes; // logon attributes+

+ ConnectionInfo crConnectionInfo; // connection info+

+ CrystalDecisions.ReportAppServer.DataDefModel.Table crTable;+

+ // database table+

+ // create logon property+

+ crLogonInfo = new PropertyBag();+

+ crLogonInfo["XML File Path"] = schema_file_name;+

+ // create logon attributes+

+ crAttributes = new PropertyBag();+

+ crAttributes["Database DLL"] = "crdb_adoplus.dll";+

+ crAttributes["QE_DatabaseType"] = "ADO.NET (XML)";+

+ crAttributes["QE_ServerDescription"] = "NewDataSet";+

+ crAttributes["QE_SQLDB"] = true;+

+ crAttributes["QE_LogonProperties"] = crLogonInfo;+

+ // create connection info+

+ crConnectionInfo = new ConnectionInfo();+

+ crConnectionInfo.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;+

+ crConnectionInfo.Attributes = crAttributes;+

+ // create a table+

+ crTable = new CrystalDecisions.ReportAppServer.DataDefModel.Table();+

+ crTable.ConnectionInfo = crConnectionInfo;+

+ crTable.Name = table_name;+

+ crTable.Alias = table_name;+

+ // add a table+

+ rcDoc.DatabaseController.AddTable(crTable, null);+

+ // pass dataset+

+ rcDoc.DatabaseController.SetDataSource(DataSetConverter.Convert(data), table_name, table_name);+

+ }+

+ void CreateReport()+

+ {+

+ int iField;+

+ // create ado.net dataset+

+ CreateData();+

+ // create report client document+

+ m_crReportDocument = new ReportClientDocument();+

+ m_crReportDocument.ReportAppServer = "127.0.0.1";+

+ // new report document+

+ m_crReportDocument.New();+

+ // add a datasource using a schema file+

+ // note that if you have distributed environment, you should use AddDataSourceUsingDataSet method instead.+

+ // for more information, refer to comments on these methods.+

+ AddDataSourceUsingSchemaFile(m_crReportDocument, xsd_path, "Customer", m_dataset);+

+ +

+ // get Customer Name and Country fields+

+ iField = m_crReportDocument.Database.Tables[0].DataFields.Find("Customer Name", CrFieldDisplayNameTypeEnum.crFieldDisplayNameName, CeLocale.ceLocaleUserDefault);+

+ m_crFieldCustomer = (Field)m_crReportDocument.Database.Tables[0].DataFields[iField];+

+ iField = m_crReportDocument.Database.Tables[0].DataFields.Find("Country", CrFieldDisplayNameTypeEnum.crFieldDisplayNameName, CeLocale.ceLocaleUserDefault);+

+ m_crFieldCountry = (Field)m_crReportDocument.Database.Tables[0].DataFields[iField];+

+ // add Customer Name and Country fields+

+ m_crReportDocument.DataDefController.ResultFieldController.Add(-1, m_crFieldCustomer);+

+ m_crReportDocument.DataDefController.ResultFieldController.Add(-1, m_crFieldCountry);+

+ // view report+

+ crystalReportViewer1.ReportSource = m_crReportDocument;+

+ }+

+ public Form1()+

+ {+

+ //+

+ // Required for Windows Form Designer support+

+ //+

+ InitializeComponent();+

+ // Create Report+

+ CreateReport();+

+ //+

+ // TODO: Add any constructor code after InitializeComponent call+

+ //+

+ }+//=========================================================================

Former Member · ‎08-26-2008

In my experience that oh-so-descriptive "Unspecified error" usually means the report engine is missing a piece of information it needs to connect to the data source - usually a connection attribute of some sort.

Looking at your code, at least part of the issue might be with the lines you've commented out which set the database DLL. A blank report won't automatically know which database driver you want to use for a newly-added table. The DLL for connecting to a .NET dataset is crdb_adoplus.dll.

Side note: Is there just one table in your dataset? If not, I would recommend sticking with a single table, preferably as a permanent solution but at least until you've got the code working, anyway.

How to create a report based on a DataSet programatically

Accepted Solutions (1)

Accepted Solutions (1)

Answers (7)

Answers (7)

Re: "Failed to update setup engine executables. Pr...

Re: Timer showing while sending the mails from SAP

Re: Best practice to connect to multiple databases...

SAP Hana Calculation view input parameter from JPA...

Re: Adaptation error in sap mdg ui screen customer