Replacing SQL just before to open

Xavi

I need to modify the SQL statement before to FR executes it. Parameter system it is not enough.

For example my SQL statement is:

SELECT * FROM Customer WHERE %%MyFilterTag%% ORDER BY %%MyOrderTag%%

Then, from the source, I need to replace %%MyFilterTag%% by my custom filter and %%MyOrderTag%% by my custom sort.

Is there a FR event that is called just before the database is opened?


Thank you

AlexTZ

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

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

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

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

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

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

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

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