Master-Detail relationship for large detail tables

D?©boraZB

Hi,

I'm developing a generical report generator. The report receives a dataset for its creation. In my case, I need to create a master-detail N:N relationship to represent the data.

In this link, there's an master-detail example which fills a dataset using SQL:
http://documentation.devexpress.com/#Windo...omDocument11225

What I want is similar, but my master SQL has some filter criteria, such as:
"SELECT CategoryID, CategoryName, Picture FROM Categories WHERE CategoryName LIKE '%Beverages%'".

Using the following detail SQL would be unfeasible, because I may have large detail tables:
"SELECT CategoryID, ProductID, ProductName, UnitPrice FROM Products"

Worrying about performance issues, detail SQL must return only the respective products of the selected categories.

How can I achieve this?

AlexTZ

Hello,

You have several options:
1) add the filter criteria to the detail sql too.
2) add parameter to the detail sql and bind the parameter's value to the master:

SELECT CategoryID, ProductID, ProductName, UnitPrice FROM Products WHERE CategoryID = @param1
@param1 will be defined as follows:
Name = param1
DataType = Int32
DefaultValue = 0
Expression = [master_table.CategoryID]

D?©boraZB

Solution number 1 doesn't work for me, because I use two independent and generical SQLs (which will be passed by the user).

Solution number 2 is exactly what I need, but I'm affraid I didn't let some information clear: I use C# and Firebird database. And I don't see how to implement your solution using that.

Thanks for your time!

AlexTZ

#2 is for the case if you added a datasource using the "Data|Add Data Source..." menu. It is not suitable for datasources provided by an application.

D?©boraZB

It must be a generical datasourse, since SQLs will be passed by the user. Datasource must be dynamically created.
Any other ideas?!

AlexTZ

If you create a datasource in your app and register it in the report, you must supply a ready-to-use datasource filled with data. There is no option to somehow decrease the memory consumption in this case.
Another option is to create datasources in the report using the "Data|Add data source" menu. In this case you can control how the detail datasource is filled. Note that you may pass user-generated sql to the report datasource:

(report.GetDataSource("the_master_datasource") as TableDataSource).SelectCommand = "select * from ...";

Hans

AlexTZ wrote: »

Hello,

You have several options:
1) add the filter criteria to the detail sql too.
2) add parameter to the detail sql and bind the parameter's value to the master:

SELECT CategoryID, ProductID, ProductName, UnitPrice FROM Products WHERE CategoryID = @param1
@param1 will be defined as follows:
Name = param1
DataType = Int32
DefaultValue = 0
Expression = [master_table.CategoryID]

I am trying to use the option 2 as you described above to print a Master-Detail report using a database (SQL Anywhere 16).
However, it prints only the *first* Master record with it Details records. When I right click at the Master data source I see several records, all the Offers do have rows in the database.

I have attached a simplified example of my report.
It contains a table Offers and Details. In the Offers the Select statement is (nummer is the ID):
SELECT * FROM offertes WHERE nummer BETWEEN 979030 AND 979040 ORDER BY nummer DESC;
The Detail contains this Select statement:
SELECT * FROM offerteregels WHERE offerte = :offerte_nummer;
And the Detail has a parameter named 'offerte_nummer' with the expression '[Offertes.nummer]

What do I am wrong..?


Tia
Hans

Hans

Sorry, found it. I had to add a Detail Band.
Ignore my post please.