How to set ReportDocument.Database.Tables[idx].Location?

[Former Member]

Hi,

I am using crystal report inside visual studio 2010.

I have a DataSet that contains 5 tables. I serialize the dataset into a xml file, and I use the XML file as the crstal report database file in report design time.

Because Each time my application give the xml file a random name, so I need assign the file to crystal table. I tried the way as followed,

                for (int idx = 0; idx < rptDoc.Database.Tables.Count; idx++)

                {

                       rptDoc.Database.Tables[idx].Location = "c:\invoice1.xml"; // rptDoc  is ReportDocument

                }

This doesn't generate an error. But when it executes

rptDoc.ExportToStream(ExportFormatType.CrystalReport);

it has error message:

"Cannot determine the queries necessary to get data for this report. Failed to retrieve data from the database."

If the xml file only contains one table then the report is OK.

How can I solve the problem?

Thanks.

[former_member183750 · Accepted solution]

Hello Peter

The string 'table location crystal net xml' using the search box in the top right corner of this web page returns the following KB:

1203980 - How to change the location of the .XSD and .XML files at runtime in a .NET application

Please see if this KB helps.

- Ludek

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member · Accepted solution]

Hi Ludek,

Thank you for your help.

I convert the VB code into C# code as followed,

                CrystalDecisions.CrystalReports.Engine.Database crDatabase;
                CrystalDecisions.CrystalReports.Engine.Tables crTables;
                TableLogOnInfo crTableLogOnInfo;
                CrystalDecisions.Shared.ConnectionInfo crConnectionInfo;

                crConnectionInfo = new CrystalDecisions.Shared.ConnectionInfo();
                crDatabase = rptDoc.Database;
                crTables = crDatabase.Tables;//'Loop through all tables in the report and apply the connection information for each table.
                foreach (CrystalDecisions.CrystalReports.Engine.Table crTable in crTables)
                {
                    crTableLogOnInfo = crTable.LogOnInfo; 
                    crTableLogOnInfo.ConnectionInfo = crConnectionInfo;
               
                    crConnectionInfo.ServerName = @"D:\MyFolder\invoice.xml";
                    crConnectionInfo.DatabaseName =  @"D:\MyFolder\invoice.xsd";
               
                    crTableLogOnInfo = crTable.LogOnInfo;
                    crTableLogOnInfo.ConnectionInfo = crConnectionInfo;
                    crTable.ApplyLogOnInfo(crTableLogOnInfo);
                }

But I still get an error message:

The error message is
Cannot determine the queries necessary to get data for this report.
Failed to retrieve data from the database.
Error in File invoice {CC261C77-CA8B-4AA6-9A02-63BE45B167D9}.rpt:
Failed to retrieve data from the database.

The source is
CrystalDecisions.ReportAppServer.DataSetConversion

The stack is
   at CrystalDecisions.ReportAppServer.ConvertDotNetToErom.ThrowDotNetException(Exception e)
   at CrystalDecisions.ReportSource.EromReportSourceBase.ExportToStream(ExportRequestContext reqContext)
   at CrystalDecisions.CrystalReports.Engine.FormatEngine.ExportToStream(ExportRequestContext reqContext)
   at CrystalDecisions.CrystalReports.Engine.ReportDocument.ExportToStream(ExportOptions options)
   at CrystalDecisions.CrystalReports.Engine.ReportDocument.ExportToStream(ExportFormatType formatType)
   at ClinicalComputers.CCCrystalReport.CrReportServer.GetReportStream()

Does the crystal report support the xml files generated from a dataset with relationalship between tables?

peter

[former_member188030 · Accepted solution]

Hi Peter,

If the XML file is directly being supplied to the report as a datasource instead of the dataset in this case th report uses crdb_xml.dll.

I am not sure of what kind of 'relationship between tables' you are referring to but hen a report is connecting to XML, it does not support hierarchical data structure.

I would suggest you to use datasets to pass the data to the report.

1511438 - How to use datasets to pass data to Crystal Reports.

- Bhushan

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member · Accepted solution]

Hi Bhushan,

Thank you for help.

I have 5 tables, for easy to explain, say these five tables are customer, account, order, orderline, product.

I generated xml file (containing xsd section in the file) from dataset. And use the xml file do the crystal report design. In the Database expert / links table, There are all five tables with links.

one customer could have more account, one account may have many order, one order may have more than one orderline, each orderline has one product Id, through the product Id, we can find product name in product table. So it like customer -> account -> order -> orderline <- product.

In the Database expert / links table, There are all five tables with links. The link directions are same as I said above. Also I changed all links' join type as left outer join.

Here orderline is different from customer, account and order, you can see the arrow does not point to product instead it is from product pointing to orderline.

I found that if my report uses fields in detail section from customer, account, order and orderline,  or any combinations of these 4 tables, then the report seems fine. Once add product field, then I got the error message as I mentioned before.

Do you think the link direction in Database expert / link are correct? or crystal report doesn' support these kind of relationship between dataset tables?

Thanks

[former_member183750 · Accepted solution]

See if the KB 1509133 - Error: Cannot determine the queries necessary to get data for this report will help. The KB is written for CR 2008, but should apply to CRVS2010 equally.

One note for you re. connecting to XML files via the crdb_XML.dll driver. This driver requires the Java framework, so it will be up to you to ensure that this is installed and configured correctly on any runtime computer. E.g.; the CR MSM / MSI files do not install / configure the Java runtime. For more info, see the blog How to deploy Crystal Reports 2008 runtime for .NET when the report is connecting to XML files. Again, the blog was written for CR 2008, but applies to CRVS2010 as well.

- Ludek

[Former Member · Accepted solution]

Hi Ludek,

Thank you for your help.

KB 1509133 asks Changing the setting in CRConfig.xml located in C:\Program Files\Business Objects\Common\4.0\java.

I could not find the C:\Program Files\Business Objects\Common\4.0\java in my computer.

I only find C:\Program Files\SAP BusinessObjects, under this fold there is Crystal Reports for .NET Framework 4.0 folder and MapInfo MapX folder. I searched CRConfig.xml under folder of C:\Program Files\SAP BusinessObjects, there is no this file.

Could you tell me where is this file?

Thanks.

[Former Member · Accepted solution]

Hi Ludek,

I just tried to create dataset at design time, It works. I am wondering if it is possible to use xml file or xsd to design report as I did before?

Thanks.

[former_member183750 · Accepted solution]

Yes, that is actually the recommended way of designing reports that will be reporting off of a dataset. But I find that often you can create a report off of OLE DB or ODBC datsource, pass a dataset to it and it will work. There are instances where you have to use replaceConnection method. See this blog for more details.

- Ludek

[Former Member · Accepted solution]

Hi Ludek,

The report works when I drag tables into dataset at design time. Then I realise the actual tables for the report are very different from the tables in the database. I tried to add two datatable into the existing dataset at design time, then I got error said database logon failed. Is this caused by different type of data tables(some directly from database, some are manually added)? Anyway to solve the problem?

We have more than 260 different reports to do. Do you think what is proper way for the data source?

We previously used paradox database as data sources for cr. But it doesn't work well in Win 7. So we want to choose a stable way for the data source. I thought data set is basically a xml file, it should be suitable way, but now I am not sure about it, would you give some suggestions?

Thank you very much.

[former_member183750 · Accepted solution]

Once a report is designed on a particular data (it could be an actual database, or XML / XSD), it "remembers" the structure of the data (the table names, field names, field types, etc., etc.). It then expects that data supplied to it will match what it expects.

So, as I understand it, we have report originally created off of paradox tables. Now we want to use datasets rather than paradox. When using datasets, it is of no consequence to the report engine where the data came from, as long as it matches what it expects. I addition to using datasets, it appears that you now want to eithe add new tables to the report or change tables. Or both(?). Doing this in code (using InProc RAS SDK), could be done - maybe, but with an awful lot of work. Even with 260 reports, doing these conversions in the designer may be quicker than creating an app that will do it for you. This is beacause not only do you have to convert the report, you more than likely will have to contend with filed mapping issues. So, the solution;

1) Download CR 2011 trial from here:

http://www.sap.com/solutions/sapbusinessobjects/sme/freetrials/index.epx

2) Create an XSD file off of your paradox tables that matches what the report(s) expect

3) Open a report in the CR Designer (after you've made a back up)

4) Go to the Database | set Database connection menu

5) Select ADO .NET as your new datasource, point at the XML

6) Click the <Upate> button

7) If you get a Field mapping dialog, the XSD does nto match what the report is expecting and you will have to manually map the fields or modify your XSD file

😎 Save the report

9) In your app, you should now be able to pass to the report a dataset that matches the XSD

You should also be able to add new tables using the XSD in the CR designer. Like I said, all of the above could be done in code, but I would not want to do it. Way too many places where this could go wrong, way too much development time.

- Ludek

[Former Member · Accepted solution]

Hi Ludek,

Thank you very very much for your help.

I didn't explain clearly previously. Our previous application was written in Delphi and used paradox table as Cr data source. Now we re-write the application in C#.

What I tested today is that I created a new sql server database, and created 5 tables with relationship between each other. Using the 5 tables I created a dataset, and use the dataset to design report. In the application code, I created 5 datatables with same table name, column name and data type, but I didn't add relations. This way the report works fine. Then I deleted the database, and excluded the designed dataset from the project. The report is still working.

The thing I don't like is that I have 260 reports and may have to create around 1,000 tables for the reports. That is big job. With paradox, we can create local tables at runtime. It is much easier. Now I have to create tables in database and create datasets, once structure changed, then have to modify the database tables and datasets.

I am still wondering is there any way I can create only dataset at runtime and don't have to create database tables and using that to create a dataset?

Thank you agian.

[former_member183750 · Accepted solution]

Sorry Peter, I suspected as I was writing my last post that I was going sideways...

I had another tech look at this with me and we decided that perhaps a different approach will work better;

In a nutshell, we'll need to do the following

1) replace the database driver the report is using now - we can do this in code (more below)

2) Then we'll need a bit of InProc RAS code (more below)

3) And, we will probably need to set the tables by name, not index as we suspect that one of the issues is that the table index will not match the order of the table names in the report.

To replace the database driver see the blog When to use the 'Replace Connection' method using the Crystal Reports or InProc RAS SDK for .NET (sorry about the formatting in that blog - I'll fix that in a bit)

The InProc RAS code will be written out for you is you use the utility attached to this KB. Now, what you want to do, is use one of the reports you have already converted in the CR designer - assuming the report is using crdb_adoplus.dll (ADO .NET), not crdb_xml.dll. Open that report in the utility and see the db connection code for it. Use that once you have replaced the connection.

The third part is just a concern I have; If we have 5 tables and we set the location by index, we are assuming that the order of the tables in all reports is always A, B, C, D, E. If the order is different, say A, C, E, D, B the report engine will not automatically adjust for that resulting in the error:

"Cannot determine the queries necessary to get data for this report. Failed to retrieve data from the database."

E.g.; in the above example A will match to A (thus one table works), but the engine wil try to match B to C and will fail.

If we follow the above process, we will at the end be able to use either an XML file or an in memory dataset - as long as it's not too big. E.g.; the crdb_adoplus.dll will use an XML file or an DO .NET dataset.

Hope I did not over-complicate this...

- Ludek

[Former Member · Accepted solution]

Hi Ludek,

You said If we follow the above process, we will at the end be able to use either an XML file or an in memory dataset - as long as it's not too big. What is too big? I run a audit report over a year period, and the report had over 2,500 pages and I saved it into a xml format file, the file has about 350 MB in size. I don't think anyone will run the report over one year period, but it is possible to run it for two months, therfore it may have over 400 pages, 60 MB. Is this too big?

Thank you very much.

[former_member183750 · Accepted solution]

Sorry Peter, I should have explained that better. Too big would be somewhere around 2,000 to 3,000 records. A 400 page report will in all likelihood be too big, never mind 2,500 pages. There are a lot of good things about datasets, but the draw back is that they are memory hogs. And, due to MS imposed security restrictions, we can not use the dataset you actually point the report at. We must create an in memory copy, so that much more of a hit... So connecting to XML may be an option - if you ensure that you are not using crdb_xml.dll. Use crdb_adoplus.dll to connect to the XML file. But unfortunately, all of this may come down to a bit of experimentation.

- Ludek

[Former Member · Accepted solution]

Hi ludek,

Thank you for your help.

I just back to work from holiday. I will be away about a month from early next  month.

Use data set seems a big risk for me. Do you mean using XML file will not have the size problem?

Also how about use XML server tables as data source? Do I have to create tables at run time and these tables must have random unique names ( so different users won't have problem if they run same report at same time)?

Thank you again for your time.

Peter

[former_member183750 · Accepted solution]

I've seen xml files of hundreds of MEGs and no memory issues, but there are performance issues. If yo have to be creating data on the fly, creating an actual temp table(s) and pointing the report at that will give you good performance and certainly no mem issues.

- Ludek

[Former Member · Accepted solution]

Hi Ludex,

Thank you very much!

Peter