Master-Detail relationship for large detail tables

Comments

• AlexTZ

  June 2012 edited 5:45AM

  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

  June 2012 edited 5:45AM

  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

  June 2012 edited 5:45AM

  #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

  June 2012 edited 5:45AM

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

  June 2012 edited 5:45AM

  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

  July 2014 edited 5:45AM

  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

  July 2014 edited 5:45AM

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