Changing datasource from ODBC into XML file

[Former Member]

I try to change the datasoruce of a report from a ODBC databse into a XML file.

I found the thread about ReplaceConnection_Click (see below) but I am afraid that will do not help me.

Our situation: we do have a lot of reports created with a SQL Anywhere 10 database (ODBC). In the reports there are a dozens of tables with each a dozen of columns. These reports are printed by our PowerBuilder RDC program.

Now we like to print these reports with .NET outside our old PowerBuilder program.

Our PowerBuilder can export a XML file containing all the data needed for the report. Alle data is exported in one table "Table", the column names are in the format table_column.

For example; a orderconfirmation XML looks like:

<Table>

<Customers_number>10</Customers_number>

<Orders_number>123</Orders_number>

<Orders_date>01-05-2011</Orders_date>

<Orderlines_description>Coca cola bottle</Orderlines_desription>

</Table>

(the original report contains 3 tables, Customers, Orders and Orderlines)

In .Net wWe import this XML into a dataset. Now we have to replace the datasource and map the fields to the corresponding values of the dataset. For example: if the report uses the field "description" from table "orderlines" it should map to the dataset Table and column "orderlindes_description" .

How do we change the datasource from the ODBC database with a XML datasource and remap the fields ?

Tia

Hans

-

---

private void ReplaceConnection_Click(object sender, EventArgs e)

{

CrystalDecisions.CrystalReports.Engine.ReportDocument rpt = new CrystalDecisions.CrystalReports.Engine.ReportDocument();

ISCDReportClientDocument rcd;

rcd = rptClientDoc;

rptClientDoc.DatabaseController.LogonEx("dwcb12003", "xtreme", "sb", "pw");

//Create the logon propertybag for the connection we wish to use

CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag logonDetails = new CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag();

logonDetails.Add("Auto Translate", -1);

logonDetails.Add("Connect Timeout", 15);

logonDetails.Add("Data Source", "dwcb12003");

logonDetails.Add("General Timeout", 0);

logonDetails.Add("Initial Catalog", "Orders");

logonDetails.Add("Integrated Security", "True");

logonDetails.Add("Locale Identifier", 1033);

logonDetails.Add("OLE DB Services", -5);

logonDetails.Add("Provider", "SQLOLEDB");

logonDetails.Add("Use Encryption for Data", 0);

logonDetails.Add("Owner", "dbo"); // schema

//Create the QE (query engine) propertybag with the provider details and logon property bag.

CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag QE_Details = new CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag();

QE_Details.Add("Database DLL", "crdb_ado.dll");

QE_Details.Add("QE_DatabaseName", "Orders");

QE_Details.Add("QE_DatabaseType", "OLE DB (ADO)");

QE_Details.Add("QE_LogonProperties", logonDetails);

QE_Details.Add("QE_ServerDescription", "dwcb12003");

QE_Details.Add("QE_SQLDB", "True");

QE_Details.Add("SSO Enabled", "False");

QE_Details.Add("Owner", "dbo");

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo newConnInfo = new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo oldConnInfo;

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfos oldConnInfos;

oldConnInfos = rcd.DatabaseController.GetConnectionInfos(null);

for (int I = 0; I < oldConnInfos.Count; I++)

{

oldConnInfo = oldConnInfos;

newConnInfo.Attributes = QE_Details;

newConnInfo.Kind = CrystalDecisions.ReportAppServer.DataDefModel.CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;

rcd.DatabaseController.ReplaceConnection(oldConnInfo, newConnInfo, null, CrystalDecisions.ReportAppServer.DataDefModel.CrDBOptionsEnum.crDBOptionDoNotVerifyDB);

}

}

Thanks

Don

[former_member11696 · Accepted solution]

Hi Hans,

What version of CR are you using? And which XML driver are you using?

First thing to try is to set location to your XML file in CR Designer and see if it works without the Mapping UI popping up. If it does then you may be able to use CR for VS 2010 and Visual Studio 2010 which now has a Field Mapping event. If that is not an option for you then unfortunately you will need to update the reports manually or you may have to recreate them. There are no supported SDK's that handle filed mapping, all default to auto which simply deletes fields that CR can't map.

Another option is the RDC Viewer also had a field Mapping Event so you could do this your RDC app.

To get the proper XML format create a data set in your app and then save the DS to XML/XSD format

In .NET this is the code to save the xml in the correct format:

ds.WriteXml("c:
sc.xml", XmlWriteMode.WriteSchema);

Thank you

Don

[Former Member · Accepted solution]

Hi Don,

We use Crystal Reports XI and ADO.NET XML driver from Visual Studio 2008.

When we change the Datasource within Crystal reports Designer from a SQL database into a XML file the Field mapping pops up. So, if I understand well, we have to upgrade to Visual Studio 2010 and Crystal Reports 2010... Right..?

If so, we like to see an example how to map fields in that event. Before we invest time an money in these upgrades.

For example:

Map field

SQL database Dataset

"Orders.Number" -> "Table.Orders_Number":

"Orderlines.Description" - > "Table.Orderlines_Description"

Thanks

Hans

[former_member11696 · Accepted solution]

Hi Hans,

Thanks for testing, what this means is CR can't map the field types likely due to XML types can't use what ever the originating DB Server has them defined as.

You may be able to alter the types on the server to get a better map to XML but XML i s limited.

We have not done much testing with this new API yet but our Developer sent this for use to start testing with and Trevor did verify it works when the DB driver changes which is exactly what you are doing:

using System;
using System.Collections.Generic;
using System.Text;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.ReportAppServer.ClientDoc;
using CrystalDecisions.ReportAppServer.DataDefModel;
using CrystalDecisions.Shared;
using System.Threading;

namespace ConsoleApplication1
{
    class Program
    {
        static string aa = "";
        static ReportDocument report = new ReportDocument();
           
        static void Main(string[] args)
        {
            MM();         
        }

        private static void MM()
        {
            Program pp = new Program();
            
            try
            {
                //Use a report with no data for NoData event. 
               report.Load(@"..\\..\\xtreme_1table_oracle.rpt");
                //for (int t = 0; t < report.Database.Tables.Count; t++)
                //{
                //    Console.WriteLine("Table " + t.ToString() + ": " + report.Database.Tables[t].Name);
                //    for (int i = 0; i < report.Database.Tables[t].Fields.Count; i++)
                //    {
                //        Console.WriteLine(report.Database.Tables[t].Fields<i>.UseCount.ToString());
                //    }
                //}
                report.AfterFormatPage += new FormatPageEventHandler(pp.report_AfterFormatPage);
                report.BeforeFormatPage += new FormatPageEventHandler(pp.report_BeforeFormatPage);
                report.NoData += new FormatPageEventHandler(pp.report_NoData);
          
                report.FieldMapping += new FieldMappingEventHandler(pp.report_FieldMapping);
                report.FormatSection += new FormatSectionEventHandler(pp.report_FormatSection);

                EventEnabledArgs enableArgs = new EventEnabledArgs();
                enableArgs.FieldMappingEnabled = true;
                enableArgs.FormattingEnabled = true;
                report.EnableEvent(enableArgs);

                //EROM API
               // EnableEventData enableEventData = new EnableEventDataClass();
               // enableEventData.EnableFieldMapping = true;
               // enableEventData.EnableFormatting = true;
               // report.ReportClientDocument.EnableEvent(enableEventData);

                report.ExportToDisk(ExportFormatType.PortableDocFormat, "abc.pdf");

               //Below function for Field Mapping event.
                testSetSQLCommandTable_CRPE(report);

            }
            finally
            {
                report.AfterFormatPage -= new FormatPageEventHandler(pp.report_AfterFormatPage);
                report.BeforeFormatPage -= new FormatPageEventHandler(pp.report_BeforeFormatPage);
                report.NoData -= new FormatPageEventHandler(pp.report_NoData);
                report.NoData -= new FormatPageEventHandler(report_NoData2);
                report.FieldMapping -= new FieldMappingEventHandler(pp.report_FieldMapping);
               report.FormatSection -= new FormatSectionEventHandler(pp.report_FormatSection);

                report.Close();             
            }
        }

[former_member11696 · Accepted solution]

Code part 2 of 2:

        static void testSetSQLCommandTable_CRPE(ReportDocument rptDoc)
        {
            //DatabaseController dbCtrl = rptDoc.DatabaseController;
            String newTableName = "Command_jli";
            CrystalDecisions.Shared.ConnectionInfo connectionInfo = new CrystalDecisions.Shared.ConnectionInfo();
            DbConnectionAttributes dbconn = new DbConnectionAttributes();
            NameValuePairs2 propertyBag = new NameValuePairs2();

            propertyBag.Set("QE_DatabaseDLL", "crdb_odbc.dll");
            propertyBag.Set("QE_Servertype", "ODBC (RDO)");
            propertyBag.Set("QE_ConnectionString", "DSN=Xtreme Sample Database 11.5;;UseDSNProperties=0");
            propertyBag.Set("QE_Servername", "Xtreme Sample Database 11.5");

            dbconn.Collection = propertyBag;
            connectionInfo.Attributes = dbconn;
            connectionInfo.UserID = "guest";
            connectionInfo.Password = "password";
            connectionInfo.ServerName = "Xtreme Sample Database 11.5";
            String sqlQueryString = " SELECT * FROM Customer";
            //            XTREME"."Orders Detail" "Orders_Detail" INNER JOIN "XTREME"."Orders" "Orders" ON "Orders_Detail"."Order ID"="Orders"."Order ID"
            rptDoc.SetSQLCommandTable(connectionInfo, newTableName, sqlQueryString);

        }
        static void report_NoData2(FormatPageEventArgs e)
        {
            Console.WriteLine("report_NoData2," + e.PageNumber);
        }
        static void report_FieldMapping2(FieldMappingEventArgs fieldMappingargs)
        {
            Console.WriteLine("FieldMappingEvent");
        }
        

         void report_FormatSection(FormatSectionEventArgs e)
        {
            Console.WriteLine("report_FormatSection");
            bool isEndOfGroup = e.IsEndOfGroup;
            bool isRepeatGroupHeader = e.IsRepeatGroupHeader;
             bool isStartOfGroup = e.IsStartOfGroup;
             Console.WriteLine("isEndOfGroup:" + isEndOfGroup);
             Console.WriteLine("isRepeatGroupHeader:" + isRepeatGroupHeader);
             Console.WriteLine("isStartOfGroup:" + isStartOfGroup);
             Console.WriteLine("section number:" + report.ReportDefinition.Sections[0].Name);
          //   report.ReportDefinition.Sections[0].SectionFormat.BackgroundColor = System.Drawing.Color.Green;
            /// aa = "formatsection";           

        }

         void report_FieldMapping(FieldMappingEventArgs fieldMappingargs)
        {
            Console.WriteLine("report_FieldMapping");
            aa = fieldMappingargs.ReportFieldsMappings[0].FieldName;
            fieldMappingargs.ReportFieldsMappings[0].FieldName = "Customer ID";
            fieldMappingargs.ReportFieldsMappings[0].MappingToFieldIndex = 1;
            fieldMappingargs.ReportFieldsMappings[0].TableName = "Customer";
            ////FieldMappingEventData a = new FieldMappingEventDataClass();
            //a.ReportFieldsMappingInfoList[0].DatabaseFieldName = "Customer ID";                     
        }

        void report_NoData(FormatPageEventArgs e)
        {
            Console.WriteLine("report_NoData," + e.PageNumber);
        }

        void report_BeforeFormatPage(FormatPageEventArgs e)
        {
         Console.WriteLine("report_BeforeFormatPage," + e.PageNumber);
            report.ReportDefinition.ReportObjects[0].Left = 10;
            Console.WriteLine("report Height:" + report.ReportDefinition.ReportObjects[0].Height);
           }

        void report_AfterFormatPage(FormatPageEventArgs e)
        {
            Console.WriteLine("report_AfterFormatPage," + e.PageNumber);
            Console.WriteLine("table name:" + report.Database.Tables[0].Name);
        }
    }
}

It doesn't require the Section event code, something we have not been able to make work yet but we have not had time to test a lot. Once more samples become available we'll be sure to include all Mapping and Event triggers.

Thanks again

Don

[former_member11696 · Accepted solution]

One other option I should mention too is to use RAS which is included with CR for VS 2010 or CR 2008. You can get the report objects and then create a new report using RCAPI in RAS. A little more work and may be able to copy all object info but does give you the ability to create your own Report Designer:

See samples here:

http://wiki.sdn.sap.com/wiki/display/BOBJ/NETRASSDK+Samples#NETRASSDKSamples-Exporting%2FPrinting

Thanks again

Don

[Former Member · Accepted solution]

Hi Don,

We have upgraded our Visual Studio 2008 to 2010 and installed Crystal Reports 2010 for Visual Studio 2010.

We have used your example but unfortunately the Field Mapping event did not fire up. Instead the VerifyDatabase() results into an Logon error.

We tried every possible Datasource and Connection modification but nothing did work.

We have a zip file ready for you containing a test solution with a report with an ODBC connection and a XML file that should replace the ODBC connection. How can I upload that zip file?

Tia

Hans

[Former Member · Accepted solution]

Hi Don,

Here an example of our code. We are able to change the connection from ODBC into XML, however there is no data shown. (event no data is triggered). The trigger FieldMapping is NOT triggered.

Please can you help us?

Thanks

Hans

Crystal Reports version 13.0.2000.0

.NET 4.0

.
.
using System.Collections;

namespace CrystalReportsApplication1
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            ReportDocument report = new ReportDocument();
            try
            {
                //Load dreport with ODBC driver
                report.Load(@"D:\Test\CR\afroep_db.rpt");
                report.AfterFormatPage += new FormatPageEventHandler(report_AfterFormatPage);
                report.BeforeFormatPage += new FormatPageEventHandler(report_BeforeFormatPage);
                report.NoData += new FormatPageEventHandler(report_NoData);
                report.FieldMapping += new FieldMappingEventHandler(report_FieldMapping);
                report.FormatSection += new FormatSectionEventHandler(report_FormatSection);

                EventEnabledArgs enableArgs = new EventEnabledArgs();
                enableArgs.FieldMappingEnabled = true;
                enableArgs.FormattingEnabled = true;
                report.EnableEvent(enableArgs);

                //change ODBC driver into XML
                report = ChangeConnectionInfo(report);

                //Verify failed; logon error
                report = changeReportFields(report);
                report.VerifyDatabase();
                crystalReportViewer1.ReportSource = report;
                //FieldMapping fm = new FieldMapping();

            }
            catch (Exception ex)
            {
                String s = ex.Message;
                MessageBox.Show(s);
            }
        }

        void report_FieldMapping(FieldMappingEventArgs fieldMappingargs)
        {
            Console.WriteLine("report_FieldMapping"); <--- Never fires !!
        }

        void report_NoData(FormatPageEventArgs e)
        {
            Console.WriteLine("report_NoData," + e.PageNumber);  <-- Does fire
        }

        private ReportDocument ChangeConnectionInfo(ReportDocument boReportDocument)
        {
            CrystalDecisions.ReportAppServer.DataDefModel.Table boTable =
            new CrystalDecisions.ReportAppServer.DataDefModel.Table();

            PropertyBag boMainPropertyBag = new PropertyBag();
            PropertyBag boInnerPropertyBag = new PropertyBag();

            boInnerPropertyBag.Add("File Path ", @"D:\Test\CR\afroep_db.xml");

            boMainPropertyBag.Add("Database DLL", "crdb_adoplus.dll");
            boMainPropertyBag.Add("QE_DatabaseName", "");
            boMainPropertyBag.Add("QE_DatabaseType", "ADO.NET (XML)");
            boMainPropertyBag.Add("QE_LogonProperties", boInnerPropertyBag);
            boMainPropertyBag.Add("QE_ServerDescription", "data");
            boMainPropertyBag.Add("QE_SQLDB", "False");
            boMainPropertyBag.Add("SSO Enabled", "False");

            CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo boConnectionInfo =
            new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();
            boConnectionInfo.Attributes = boMainPropertyBag;
            boConnectionInfo.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;
            boTable.ConnectionInfo = boConnectionInfo;

            CrystalDecisions.ReportAppServer.DataDefModel.Tables boTables;
            boTables = boReportDocument.ReportClientDocument.DatabaseController.Database.Tables;

            for (int t = 0; t < boTables.Count; t++)
            {
                boTable.Name = "data_row";
                boTable.QualifiedName = "data_row";
                boTable.Alias = "data_row";
                boReportDocument.ReportClientDocument.DatabaseController.SetTableLocation(boTables[t], boTable);
            }
            return boReportDocument;
        }

    }
}

Edited by: Hans Groeneveld on May 20, 2011 4:06 PM

[Former Member · Accepted solution]

Hi Don

We are able to get the FieldMapping event fired. We change our ODBC table reports into a SQLCommand table, as you described. Immediately after the SetSQLCommandTable() the event FieldMapping triggers.

However, we are not able to map the reports fields to the database fields correctly.In our test report we do have two tables called 'afroep' and 'afroepregels'. We only use the field 'plantnaam' from 'afroepregels'. In our script we added a Command table with 'SELECT plantnaam FROM afroep INNER JOIN afroepregels', named 'Command'.

When I debug the FieldMapping event I see the following values (one example):

DatabasFieldsMappings[5].FieldName = "afroepregels_plantnaam"

DatabasFieldsMappings[5].MappingToFieldIndex = -1

DatabasFieldsMappings[5].TableName= "Command"

ReportFieldsMappings[1].FieldName = "plantnaam"

ReportFieldsMappings[1].TableName = "afroepregels"

ReportFieldsMappings[1].MappingToFieldIndex = -1

In the event we change the value of these mappings to get a right link (see bold text)::

DatabasFieldsMappings[5].FieldName = "afroepregels_plantnaam"

DatabasFieldsMappings[5].MappingToFieldIndex = 1

DatabasFieldsMappings[5].TableName= "Command"

ReportFieldsMappings[1].FieldName = "afroepregels_plantnaam"

ReportFieldsMappings[1].TableName = "Command"

ReportFieldsMappings[1].MappingToFieldIndex = 5

This did not work. We saved the modified report. All the report fields in the report are gone, only the title (a expression with text) is shown. In the report the database fields are shown well, the Command table is available.

What do we do wrong ? We think we are very close....

Please help.

Tia

Hans