Replacing SQL just before to open

Comments

• AlexTZ

  December 2009 edited 9:15PM

  Hello,
  
  There is no such event. You may enumerate available datasources and replace SelectCommand before running a report:
  

  report.Load(...);
  foreach (Base c in report.AllObjects)
  {
    if (c is TableDataSource)
    {
      TableDataSource table = c as TableDataSource;
      // replace macros in table.SelectCommand here...
    }
  }
  report.Show();
  
• FogRider

  January 2010 edited 9:15PM

  I have the same problem, and using the Alexe's code I'm getting only the following list of objects:
  
  FastReport.ReportPage
  FastReport.ReportTitleBand
  FastReport.PageHeaderBand
  FastReport.TextObject
  FastReport.DataBand
  FastReport.TextObject
  FastReport.TextObject
  FastReport.DataHeaderBand
  FastReport.TextObject
  FastReport.TextObject
  FastReport.LineObject
  FastReport.PageFooterBand
  
  I'm not getting any TableDataSource object in report who have two TableDataSources.
  Please help me with that. It's urgent.
  
  Another approach would be to implement some kind of parametrisation of SQL queries in report.
  Lets say I want to print list of products (SELECT code, name FROM product).
  Before print user want to filter his list of products by several parameters (in business application).
  User filters his data by: code like '0%' and length between 10 and 20 ...
  In report SQL statement we could enter: SELECT code, name FROM product [param_where].
  "param_where" would be parameter who we pass from business application's code.
  
  This approach is very usable in edit (design) mode and in release mode.
  
  Thanks.
• AlexTZ

  January 2010 edited January 2010

  Sorry, my mistake. The correct code is:
  
  foreach (Base c in report.Dictionary.AllObjects)
  ...
  
  The parameterized sql is not an option. [] are reserved symbols in many sql dialects.
• MikeInNH

  January 2010 edited 9:15PM

  
  I'm actually looking at doing the same thing. I just started to look at reporting tools and there are some features I need.
  
  One feature I need is to replace the SQL just before execution so I can add our Security.
  
  Here's an example:
  
  Report will generate following SQL.
  
  Select f1, f2, f3, role from xyz
  
  I need to replace the sql with following.
  
  Select f1, f2, f3, role from xyz as a
  Inner Join Security as b on b.role = a.role
  Inner Join User as c on c.User = b.User
• AlexTZ

  January 2010 edited 9:15PM

  Hello,
  
  If you know the name of the query, you may use the following code:
  
  TableDataSource table = Report.GetDataSource("MyQuery") as TableDataSource;
  table.SelectCommand = "select * from ... ...";
  
  You can put this code in the report script, the best place is Report.StartReport event.
• Xavi

  January 2010 edited 9:15PM

  
  I would ask you as suggestion to add an event in FR, for example OnBeforeOpen, where you can change the SQL string in runtime just before FR executes it.
  
  The current solution, iterating all report objects, works fine, but it is not efficient, specially in environments like slow web servers, many users and reports with many objects.
  
  Replacing SQL just before open is a common requirement.
  
  
  Thank you
  
• AlexTZ

  January 2010 edited 9:15PM

  I'll think about using expressions in SQL (just like in the Text object now). The problem is that [] symbols cannot be used, this will confuse developers.
• Xavi

  January 2010 edited 9:15PM

  I think that for now, a simple event would be enough. It would give more flexibility to the developer.
  In my case, I use embedded functions in the SQL, for example
  
  SELECT * FROM Customers WHERE %%GetValue('Filter')%%
  
  
  
  
  Thank you