Setting the sql statement

[Former Member]

http://www.egbolig.dk/drift_bo_syd.rpt (HERE IS THE REPORT)

We are using SAP Crystal Report .NET Runtime files (http://scn.sap.com/docs/DOC-7824 newest version 13.0.9.1312) in our web asp.net application. Everything have been working fine, but we have can into problems when showing the certain kinda reports.

In our code get the reports sql statement using getSQLStatement.

Dim gp As New GroupPath() Dim sql As String = report.ReportClientDocument.RowsetController.GetSQLStatement(gp)

This will get the sql, and we use this sql (getSqlStatement) and attach a sql WHERE clause from the code to only get a certain number of records.

The report Drift_bo_syd.rpt has 5 tables, and the following sql statement. 

Database/Show Sql Statement

SELECT "rekvstam"."Sel", "rekvstam"."Afd", "rekvstam"."Levadresse", "rekvstam"."Rekvisition", "rekvstam"."Lejemaal", "rekvstam"."Lejer", "rekvstam"."Udfoertaf", "rekvstam"."Udfoertdato", "rekvstam"."Krit", "Selskab"."Adresse", "Selskab"."Postby", "Selskab"."Tlf", "Selskab"."Fax", "rekvstam"."Kontor", "rekvstam"."Hjemme", "rekvstam"."Rekvdato", "rekvstam"."Aftale", "rekvstam"."InitialRet", "rekvstam"."Arbejde", "kreditor"."Att", "rekvstam"."Konto", "Selskab"."Navn", "Selskab"."Email", "Interessentadresse"."Navn", "Interessentadresse"."Adresse", "Interessentadresse"."Postby", "Interessentadresse"."Email1", "Interessentadresse"."Telefon_Fax", "Interessentadresse"."Type", "Interessentadresse"."Tlf1", "rekvstam"."tlfLejer", "kreditor"."Kred", "Interessentadresse"."Interessentnr" FROM  (("Rekvstam" "rekvstam" INNER JOIN "Selskab" "Selskab" ON "rekvstam"."Sel"="Selskab"."Sel") LEFT OUTER JOIN "Kreditor" "kreditor" ON "rekvstam"."Kred"="kreditor"."Kred") INNER JOIN "Interessentadresse" "Interessentadresse" ON "kreditor"."Interessentnr"="Interessentadresse"."Interessentnr" WHERE  "Interessentadresse"."Type"='K' 

But if we run the report from our asp.net code, we get the following error: 

Cannot determine the queries necessary to get data for this report. Failed to retrieve data from the database. Error in File Drift_Bo_Syd {97FED382-1BAC-4DB1-970F-9E098ECE28F1}.rpt: Failed to retrieve data from the database.

After a long time searching for a solution, we found out that if we place the column kred from the kreditor table on the report, the report will work. (field explorer / database fields / kreditor (table) / kred (column)

Very important is that the field "kreditor.kred" is a primary key of the table kreditor, and also used in the linking.!

(We can get the report to work if we call the sql statement with the "kreditor"."kred" in the SELECT statement.

SELECT "kreditor"."kred", "rekvstam"."Sel", "rekvstam"."Afd", "rekvstam"."Levadresse", "rekvstam"."Rekvisition", "rekvstam"."Lejemaal", "rekvstam"."Lejer", "rekvstam"."Udfoertaf", "rekvstam"."Udfoertdato", "rekvstam"."Krit", "Selskab"."Adresse", "Selskab"."Postby", "Selskab"."Tlf", "Selskab"."Fax", "rekvstam"."Kontor", "rekvstam"."Hjemme", "rekvstam"."Rekvdato", "rekvstam"."Aftale", "rekvstam"."InitialRet", "rekvstam"."Arbejde", "kreditor"."Att", "rekvstam"."Konto", "Selskab"."Navn", "Selskab"."Email", "Interessentadresse"."Navn", "Interessentadresse"."Adresse", "Interessentadresse"."Postby", "Interessentadresse"."Email1", "Interessentadresse"."Telefon_Fax", "Interessentadresse"."Type", "Interessentadresse"."Tlf1", "rekvstam"."tlfLejer", "kreditor"."Kred", "Interessentadresse"."Interessentnr" FROM  (("Rekvstam" "rekvstam" INNER JOIN "Selskab" "Selskab" ON "rekvstam"."Sel"="Selskab"."Sel") LEFT OUTER JOIN "Kreditor" "kreditor" ON "rekvstam"."Kred"="kreditor"."Kred") INNER JOIN "Interessentadresse" "Interessentadresse" ON "kreditor"."Interessentnr"="Interessentadresse"."Interessentnr" WHERE  "Interessentadresse"."Type"='K'

But it should not be necessary to include this field (which is the primary key and used in linking the report) in the sql statement,
BECAUSE it is not used in the report. So maybe this is a bug?

It has not been necessary in RDC Crystal Report or RAS Crystal Report in a classic asp envoriment.

Here is the code we use to set the reports SQL Statement)

Try
Dim conn As New SqlConnection(connString)
conn.Open()
Dim sd As New SqlDataAdapter(New SqlCommand(nySQL, conn))
Dim ds As New Data.DataSet()

Dim navn As String = report.Database.Tables.Item(0).Name
sd.Fill(ds, navn)
report.SetDataSource(ds)
conn.Close()
Catch ex As Exception
Throw
End Try

[former_member11696 · Accepted solution]

Hi Jan,

Setting the SQL Statement is no longer available in SDK. You can change it using the Record Selection formula now or if you need specific detail changes then change your report to use a Command, you can edit that SQL all you want.

No API's to convert a report based on tables to Command, well you can up to 2 tables but that's it.

So to convert to Command open 2 CR Designers and copy objects from one to to the other, after adding the SQL into the Command first.

So it could be the ODBC driver you are using. If you want to do more logging see this KBA on how to enable crlogger.dll

1470978 - How to enable/disable crpe logging for the Crystal Reports .NET SDK

I may show you more info on why it's failing. Likely due to original report is missing a field reference it needed to link on or it could be what you are doing is causing the SQL to be alter within CR SDK that no longer matches what is in the report.

It could also be there is a missing field from the DS now, CR auto-maps fields in .NET so if a field is missing from the SQL CR deletes the field references from the report and of course this can cause all sorts of problems...

Check your DS, make sure all the fields exist in the DS as they exist in the Report.

You can also try doing this manually in CR Designer to help debug the cause. In your code save the DS to XML:

ds.WriteXml("c:\\reports\\sc2.xml", XmlWriteMode.WriteSchema);

Open the Designer and set location to the XML file and see what it does, if it messages you the report has changed then do so and compare before and after. If the field mapping UI pop's up you know there are differences in the field info, may be the field Type has no match for Data Sets.

You need to do more debugging to find the root cause.

Don

[Former Member · Accepted solution]

can you give a code example how to set the sql using the record selection formula?

we cannot change reports to command. That would make to much Work from us and our customers, because we have hundreds of reports.

[former_member183750 · Accepted solution]

From the SAP Crystal Reports .NET API Guide:

Private Sub SetSelectionFormula(ByVal mySalesAmount As String, ByVal myCustomerName As String)

 Dim mySelectionFormula As String = "{Customer.Last Year's Sales} > " & mySalesAmount _
  & " AND Mid({Customer.Customer Name}, 1) > """ & myCustomerName & """"
  CrystalReportViewer.SelectionFormula = mySelectionFormula

End Sub

Also using the search string 'crystal selectionformula net' in the search box at the top right corner comes up with a number of hits. E.g.; have a look at KB 1544447 - How to filter the data of a Crystal report at runtime using a selection formula with the C...

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow us on Twitter

[former_member11696 · Accepted solution]

Also, how to generate a Record Selection formula so it results in the same thing you had done when editing the SQL was allowed could be quite tedious to code in the rules for the logic and syntax.

Command was the work around for complex changes, no other option. We've asked R&D and they said no... Commands are your only direct replacement.

Don

[Former Member · Accepted solution]

hi

you are right, converting our sql statement (that we send from the code) to a completely different language (record selection formula) is a major change. Also as i understand record selection formula is that it filters the recordset, which mean that if we load 100.000 records, and the record selection formula filters it to 10 records, we are actually loading 100.000 records in the first place.

Changing our reports to command is also a major drawback. This Means convertering 1000+ reports.

why is setting the sql taken out of the sdk? (it is a major problem for us, compared to RDC)

We also tried setting the sql statement for each table, but this gives a completely wrong result:

there must be some way? We know that the problem never exists with reports that have command (database/database Expert/add command), because we have a few reports that are build that way, and when using command, it is like having just one table recordset.

Try

     Dim conn As New SqlConnection(connString)

     conn.Open()

     Dim sd As New SqlDataAdapter(New SqlCommand(nySQL, conn))

     Dim ds As New Data.DataSet()

     For Each mnTable In report.Database.Tables

            sd.Fill(ds, mnTable.name)

            report.Database.Tables.Item(mnTable.name).SetDataSource(ds)

     Next

     conn.Close()

Catch ex As Exception Throw End Try

[former_member11696 · Accepted solution]

Well if the Record Selection is structured properly then the filter is still done server side. But it depend on the formula to add, if any conversions are require then oyua re correct, all 100k of data is filter client side, but if configured properly then it is transferred Server Side and then you knoly get 10 records returned.

Problem is making sure the formula does push the WHERE to the server.

Short story as to why we changed it is in CR 9 we completely re-wrote the DB drivers and moved the SQL parser into a separate dll so we could manage it easier. We also removed all "custom" fixed we had in the DB drivers and when to Standards, if it did not work then the makers of the client had to "fix' their driver, it was becoming imppossible to code around their bugs.

With that change and so we could manage the SQL properly we removed the ability to edit it.

Command was the work around. If you had converted when CR9 was first released your reports may have been fewer to "fix up"....

Nothing else we can for you....

Don

[Former Member · Accepted solution]

hi

Will "setting the sql statement" be available in a future of the SDK? (perhabs very soon)

It is extremely important for us, with hundreds of customer running 100s of reports, we are in big trouble with no solution.

Not every report is a simple "where field=1" sql.  For example some of our reports have "where (select Count(*) from table where exists bla bla..)", Does record selection formula support "select exists sql" and advanced sql?

We need to be able to set our sql statement to whatever SQL like we were able to do before.

If possible, do you have some kinda implementation / function that converts any sql to a record selection formula?

Converting ALL our reports to "command" as suggested earlier is a very big job and can take many months.

If not possible, do you somehow know a recommended reporting product,

where we are able to convert our existing crystal report.

Hoping for a quick answer.

[former_member11696 · Accepted solution]

Hi Jan

I understand your problem completely but we have asked  R&D to be able to edit the SQL and they explained due to how the report Engine works it's simply not possible anymore. It would take a complete rewrite in all of our DB drivers and query dll's to be able to allow it. They did that in CR 9 when they rewrote the DB drivers and removed the SQL part of the code into separate dll's and that is when the ability was removed, it's been that way ever since.

One possibility is to get the SQL from the Report and then connect to your DB using a .NET and if the results from the SQL is less than 5K rows and not too many columns you could then set the Report data source to a Dataset.

As long as all of the field names are the same CR will run the report using this work flow.

For data larger than 5K rows, limit is due to memory resources, you could save the DS to an XML file and then set the reports data source to the XML file. We've seen 100meg XML's used so the amount of data should not be a problem, but of course there are limits to everything so test...

This should not affect performance much.

So the work flow would be:

Load the report

Get the SQL Statement

Paste it into a Dataset Query

Something like this should get you started:

//string connString = "Provider=SQLOLEDB;Data Source=MySQLServer;Database=xtreme;User ID=sa;Password=pw";

string connString = "Provider=SQLNCLI10;Server=MySQLServer;Database=xtreme;User ID=sa;Password=pw";

Detail"".""Quantity"" FROM   ""xtreme"".""dbo"".""Orders Detail"" ""Orders Detail""";

string sqlString = @"SELECT top 10*  FROM  ""xtreme"".""dbo"".""Financials"" ""Financials""";

System.Data.OleDb.OleDbConnection oleConn = new System.Data.OleDb.OleDbConnection(connString);
System.Data.OleDb.OleDbCommand cmd = oleConn.CreateCommand();
cmd.CommandText = sqlString;

System.Data.DataSet ds = new System.Data.DataSet();

OleDbDataAdapter oleAdapter = new OleDbDataAdapter(sqlString, oleConn);
//OleDbDataAdapter oleAdapter2 = new OleDbDataAdapter(sqlString2, oleConn);
DataTable dt1 = new DataTable("Financials");
//DataTable dt2 = new DataTable("Orders Detail");

oleAdapter.Fill(dt1);
//oleAdapter2.Fill(dt2);

ds.Tables.Add(dt1);
//ds.Tables.Add(dt2);
ds.WriteXml("c:\\reports\\sc2.xml", XmlWriteMode.WriteSchema);

// as long as the field names match exactly Cr has no problems setting report to a DS.
try
{
    rpt.SetDataSource(ds.Tables[0]); // incremtent [0] for more than 1 table.
    rpt.SetDataSource(ds);
}
catch (Exception ex)
{
    MessageBox.Show("ERROR: Schema Mismatch. Error reported by CR: " + ex.Message);
}

//Now check for subreport and set to same DS
foreach (CrystalDecisions.CrystalReports.Engine.Section section in rpt.ReportDefinition.Sections)
{
    foreach (CrystalDecisions.CrystalReports.Engine.ReportObject reportObject in section.ReportObjects)
    {
        if (reportObject.Kind == ReportObjectKind.SubreportObject)
        {
            CrystalDecisions.CrystalReports.Engine.SubreportObject subReport = (CrystalDecisions.CrystalReports.Engine.SubreportObject)reportObject;

            CrystalDecisions.CrystalReports.Engine.ReportDocument subDocument = subReport.OpenSubreport(subReport.SubreportName);

            subDocument.SetDataSource(ds);
        }
    }
}

And for XML it would use this part, not above I am saving the data and structure in the XML file so it should match what is in the report:

foreach (CrystalDecisions.CrystalReports.Engine.Table rptTable in rpt.Database.Tables)
{
    try
    {
        rptTable.Location = btrDataFile.ToString(); // @"D:\Atest\" + rptTable.Location + ".xml";
    }
    catch (Exception ex)
    {
        MessageBox.Show("ERROR: " + ex.Message);
    }

}

Only issue you may run into is sometimes XML does not have a direct field type and your original data source but you'll get an error if that happens which could be trapped and handled.

If you have a report that only uses 2 tables I do have code that will convert it to use a Command Object. That is the limit the engine is capable of, there is a lot of logic to be able to do this in CRD and the SDK so that is all we could get for now.

I hope that helps

Don

[Former Member · Accepted solution]

it what you are saying a code like the one below? Some reports are build by defining a sql in the report through the database Expert / add command. If done this way there is always just one kinda "table" in the report, namely the command. Setting the dataset with report.setDatasource will in this case always work.

But about 70% of our customers reports are created by adding each table in the "database Expert" using the add table to report and then linking the report together. I DONT think we have any reports that is only showing data from just 1 table. If we use the report.setDatasource on a report that has more that 1 table, and is not created using the command object, then in some cases it will fail (just stalling, and use all the power of the IIS).

Try Dim conn As New SqlConnection(connString)

Dim commandReport As Boolean = False

conn.Open()

Dim sd As New SqlDataAdapter(New SqlCommand(nySQL, conn))

Dim ds As New Data.DataSet()

For Each mnTable In report.Database.Tables

     sd.Fill(ds, mnTable.name)

     If mnTable.Name.ToUpper() = "COMMAND" Then

          commandReport = True

     End If

Next

If commandReport = True Then

     report.SetDataSource(ds.Tables(0))

Else

     report.SetDataSource(ds.Tables(1))

End If

report.SetDataSource(ds)

conn.Close()

Catch ex As Exception Throw End Try

[former_member11696 · Accepted solution]

Hi Jan,

If the report is based on a Command then all you need to do is change the SQL and update the report, no need to convert it to a DS or XML:

newTableName = "Command";

// change your SQL now - WARNING must include all fields that exist in RPT.

sqlQueryString = @"SELECT 'Checks'.'CheckID', 'Checks'.'LoopType', 'Checks'.'RunType' FROM 'astellastest'.'dbo'.'Checks' 'Checks' WHERE 'Checks'.'CheckID'<100";

rpt.SetSQLCommandTable(connectionInfo, newTableName, sqlQueryString.ToString());

So for these reports you don't need to change their source to DS or XML, all that is required is you update the Command SQL Statement in your code.

To figure out if the report is using a Command this will do it:

int dbConCount = rptClientDoc.DatabaseController.GetConnectionInfos().Count;
String DBDriver = "";

for (int x = 0; x < dbConCount; x++)
{
    try
    {
        DBDriver = rptClientDoc.DatabaseController.GetConnectionInfos()[x].Attributes.get_StringValue("Database DLL").ToString();
        btnDBDriver.Text += DBDriver + " :";
        if (((dynamic)rptClientDoc.Database.Tables[0].Name) == "Command")
        {
            CrystalDecisions.ReportAppServer.Controllers.DatabaseController databaseController = rpt.ReportClientDocument.DatabaseController;
            ISCRTable oldTable = (ISCRTable)databaseController.Database.Tables[0];

            btnSQLStatement.Text = "Report is using Command Object: \n" + ((dynamic)oldTable).CommandText.ToString();
            btnSQLStatement.Text += "\n";

            IsLoggedOn = false;
        }
    }
    catch (Exception ex)
    {
        btnDBDriver.Text = "ERROR: " + ex.Message;
        return;
    }
}

if (dbConCount == 0)
    btnDBDriver.Text = "NO Datasource in report";

Don

[Former Member · Accepted solution]

there are 2 ways to create a crystal report.

1. Command approach

2. Linked table approach

Convertering all our reports from the tabled approach to command approach could take very long. We have a lot of customers who has their own report.

Since it has been over a year now. Is there an tool or a easy way to convert crystal reports from table approach to command approach (most of our tables uses several tables) ?

If we have a crystal report that uses the "linked table approach". Then convertering the recordset to xml and setting it with rpt.SetDataSource(xml) file. Will that work with a linked table approach, where this report has many tables... (more than 2) ?

[former_member11696 · Accepted solution]

No update to remap data sources in CR Designer ot the SDK, this is not going to happen, way too much work for DEV to be able to do this.

Yes, using/setting your reports datasource to XML should work, search, lots of sample code here.

Don

[Former Member · Accepted solution]

Hi!

I cant seem to get your example to work with setting the XML as datasource.

Normally a get a where clause from the code and combine it with the reports sql.

For example i read that it should be possible to just set the path of the xml fil as setDataSource parameter.

Your example, should i create multiple xml files for each table the i use i the sql statement?

from example in the below : interessentadresse and initial table.

And then set the xml datasource for each of these tables?

Can you provide us with another example? saving the xml file(s) from one sql statement, and setting the datasource as the xml file?

SQL = "SELECT Initial.Navn, Initial.Passw, Interessentadresse.Interessentnr, Interessentadresse.Type FROM initial Initial INNER JOIN Interessentadresse Interessentadresse ON Initial.InteressentNr=Interessentadresse.Interessentnr"

  Dim conn As New SqlConnection(connString)

  conn.Open()

  Dim sd As New SqlDataAdapter(New SqlCommand(nySQL, conn))

  Dim ds As New Data.DataSet()

  Dim navn As String = report.Database.Tables.Item(0).Name

  If navn.ToUpper = "COMMAND" Then

       sd.Fill(ds, navn)

       report.Database.Tables.Item(navn).SetDataSource(ds)

  Else

       sd.Fill(ds, navn)

       Dim filnavn As String = Path.GetTempPath() & "Initial.xml"

       ds.WriteXml(filnavn, System.Data.XmlWriteMode.WriteSchema)

       report.SetDataSource(filnavn)

  End If

[former_member11696 · Accepted solution]

Can you create a new report off of the new XML file:

ds.WriteXml(filnavn, System.Data.XmlWriteMode.WriteSchema)

Does the XML have multiple tables in it? if not then don't add the Table name property.

Not needed by the way, since the data is in memory.

[Former Member · Accepted solution]

Well...Maybe i have to explain a little more clearly.

The code is working fine when the report has been created using "command". Setting the "command" datasource using the setDataSource works every time.

But the problem arises, when we use reports that are not created using "command", but by adding several tables (maybe 6 or 7) og linking them to the report (database / database expert , links). We have a 2500+ of these reports. So convertering them one by one is an incredible task.

The method for us is to grap the sql statement from the crystal report. This is actually possible with "command" and "linked tables" reports (see below). After that we combine the sql statement with a "WHERE" clause from the code, and we have the sql statement with the records we want to show.

Dim gp As New GroupPath()

Dim sql As String = report.ReportClientDocument.RowsetController.GetSQLStatement(gp)

In the good old days we just used the setSqlStatement, and the report was running with the result requested.

Your suggest that we export the dataset to xml and use "table.location" to point to the exported xml file(s). In the code below i have tried implementing your suggestion. This is a hardcoded example using only 2 tables to make it simple. But normally it is 5+ tables. The "sql" variable is what we want to show as a result.

This result will only produce 1 xml file. I can then give it the name of the first table "initial" (report.Database.Tables.Item(0).Name) , and set the table location of the "initial" table in the report to the xml file. But what about the other tables? should these be set?

Setting only the first table to the xml file, will give me the same problem as if i used SetDataSource and tried to set it on the first table? (a report not using command with several linked tables)

It you can point to examples or even better give one that is usable in our case, it would be appreciated very much.

IF I TRY TO USE THE CRYSTAL REPORT ATTACHED IN OUR FIRST POST ABOUT THIS, WE GET THE MESSAGE:

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

this must be because the report uses columns data from different tables in the details of the report. This leaves us with the same problem as described in the first post.

Try

  Dim Sql As String = "SELECT Initial.Navn, Initial.Passw, Interessentadresse.Interessentnr,   Interessentadresse.Type FROM initial INNER JOIN Interessentadresse ON Initial.InteressentNr=Interessentadresse.Interessentnr where bla bla.."

  Dim conn As New SqlConnection(connString)

  conn.Open()

  Dim sd As New SqlDataAdapter(New SqlCommand(Sql, conn))

  Dim ds As New Data.DataSet()

  Dim navn As String = report.Database.Tables.Item(0).Name

  If navn.ToUpper = "COMMAND" Then

       sd.Fill(ds, navn)

       report.Database.Tables.Item(navn).SetDataSource(ds)

  Else

       sd.Fill(ds, navn)

       Dim table As DataTable

       For Each table In ds.Tables

            Dim filnavn As String = Path.GetTempPath() & table.TableName & ".xml"

            table.WriteXml(filnavn, System.Data.XmlWriteMode.WriteSchema)

       Next

       For Each mnTable As CrystalDecisions.CrystalReports.Engine.Table In report.Database.Tables

            if File.Exists(Path.GetTempPath() & mnTable.Name & ".xml") Then

                 mnTable.Location = Path.GetTempPath() & mnTable.Name & ".xml"

            End If

       Next

  End If

  conn.Close()

  Catch ex As Exception

  End Try

[former_member11696 · Accepted solution]

You are correct, If you use more than one table then you need to add each table into the DS.

Since you are using XML you need to get the tables into the DS before exporting to XML file. What you are doing is saving each table in it's own XML file, not correct.

So work is:

Query the DB and load each table into a DS.Table[]

Save the complete DS into an XML file

report.SetLcoation(XMLfile);

So my code is doing a bunch of different things. Load data into a DS and saves to XML, you don't need to save to xml if you are not going to use the xml file.

But, if you do have a lot of data then use XML, CR can handle 100 meg XML files where a Dataset is typically limited to about 5K rows.

Don

[Former Member · Accepted solution]

well, to explain it very simple how our scenarie works.

If we have a Crystal Report made from adding several tables and not using "command" object.

In the asp.net code, we can get the reports sql statement by firing the following 2 statement. This will work no mather if the report is created from command or the table approach.

Dim gp As New GroupPath()

Dim sql As String = report.ReportClientDocument.RowsetController.GetSQLStatement(gp)

This will then give us the SQL statement from the report, which is the same SQL statement as if we opened the report and chose database/Show SQL Query (see screenshot_crystal.png). We then add a WHERE CLAUSE from the code to this SQL statement, which is based on what the user chooses in our application.

This will then give us a full SQL statement, which is the records we want to show in our report.

For example this SQL statement could be:

SELECT "Tilskud"."Type", "Tilskud"."Sel", "Tilskud"."Afd", "Tilskud"."Lejemaal", "Tilskud"."Lejer", "Tilskud"."Belob", "Interessentadresse"."Navn", "Interessentadresse"."Modul", "Interessentadresse"."Initial", "Interessentadresse"."Datoret", "Tilskud"."CPRnr", "Tilskud"."Kommune"

FROM  { oj ("Tilskud" Tilskud INNER JOIN "Lejer" Lejer ON          Tilskud."Sel" = Lejer."Sel" AND      Tilskud."Afd" = Lejer."Afd" AND      Tilskud."Lejemaal" = Lejer."Lejemaal" AND      Tilskud."Lejer" = Lejer."Lejer")      INNER JOIN "Interessentadresse" Interessentadresse ON          Lejer."Interessentnr" = Interessentadresse."Interessentnr" and interessentadresse.type='I'}

WHERE Lejer."Sel"=1

ORDER BY "Tilskud"."Type", "Tilskud"."Sel", "Tilskud"."Afd", "Tilskud"."Lejemaal", "Tilskud"."Lejer"

Remember this is a very simple report/example with only, and the dataset and records is a lot of times more than 5 kb.

How can you transform this SQL statement into one usable XML file, we can call report.setLocation(XmlFile) with?

As we see it, report.setLocation wants the XML file to be grouped by each used tables, with underlying data to be useable.

As you can see our approach is different from yours....

A example of that would be greatly appreciated.

Sincerly Jan