Search on Fieldnames inside external joined tables

davidjoubard · June 2018

Hello,
I Have delphi version XE Version 15.0.3953.35171 and ReportBuilder Version 14.08
I want to launch a report from my delphi code
The SQL in my main pipeline is something like that =

Select *
...
FROM INVOICE INVOICE_1
LEFT OUTER JOIN STOREHUGETOTAL STOREHUGETOTAL_1 ON
(STOREHUGETOTAL_1.HTTINVOICENUMBER = INVOICE_1.INVINVOICENUMBER)
AND (STOREHUGETOTAL_1.HTTNOSOCAUX = INVOICE_1.INVNOSOCAUX)
LEFT OUTER JOIN STORESTATE STORESTATE_1 ON
(STORESTATE_1.SSTIDSTATE_GESTCOM = STOREHUGETOTAL_1.HTTIDSTATE_GESTCOM)
LEFT OUTER JOIN STOREHUGETOTAL STOREHUGETOTAL_2 ON
(STOREHUGETOTAL_2.HTTIDSTATE_GESTCOM = STORESTATE_1.SSTIDSTATE_GESTCOM)
AND (STOREHUGETOTAL_2.HTTTYPE = 'DAILY')
LEFT OUTER JOIN STORESTATE STORESTATE_2 ON
(STORESTATE_2.SSTIDSTATE_GESTCOM = STORESTATE_1.SSTGROUPEDBYSTORESTATE_GESTCOM)
LEFT OUTER JOIN STOREHUGETOTAL STOREHUGETOTAL_3 ON
(STOREHUGETOTAL_3.HTTIDSTATE_GESTCOM = STORESTATE_2.SSTIDSTATE_GESTCOM)
LEFT OUTER JOIN STORESTATE STORESTATE_3 ON
(STORESTATE_3.SSTIDSTATE_GESTCOM = STORESTATE_2.SSTGROUPEDBYSTORESTATE_GESTCOM)
LEFT OUTER JOIN STOREHUGETOTAL STOREHUGETOTAL_4 ON
(STOREHUGETOTAL_4.HTTIDSTATE_GESTCOM = STORESTATE_3.SSTIDSTATE_GESTCOM)
...

The thing to see is that the sames tables are joined more than once, so there are suffixed _1, _2, _3 and so on (the is the default behavior of the report builder designer)

In my code when I want to filter on a field SSTIDSTATE_GESTCOM inside STORESTATE,

I do it with a TFiltreReport Object = SSTIDSTATE_GESTCOM , soEqual ,
In fact, it translates STORESTATE_1.SSTIDSTATE_GESTCOM =

But when I want to do something like that
TFiltreReport Object = STORESTATE_2.SSTIDSTATE_GESTCOM , soEqual ,
or
TFiltreReport Object = SSTIDSTATE_GESTCOM _2 , soEqual ,

it does not recognize the fieldname, and it skips the filter criteria

Is it possible to specify the table name or the exact alias of a fieldname inside the property Fieldname of the TFiltreReport ?

Best regards,
David

nicocizik · June 2018

Hi David,

I apologize but I'm a bit unclear about your question.

1. What is TFiltreReport? This is not an object or class of ReportBuilder.

2. You "code" does not appear to be Delphi. Is this part of your SQL query?

3. Are you using RAP? This question is posted in the RAP category.

4. Are you using DADE (built-in data workspace)? If so, the usual way to manually add a search criteria in code is to use the TppSQLBuilder class to do so. See the help topics for the class or the wiki articles below.

http://rbwiki.digital-metaphors.com/category/dade/sqlbuilder/

davidjoubard · July 2018

Hi Nico,
Thanks for your answer, I am still have this issue to solve in the next weeks.
I understand that my question can be confusing.
Trying to be clear, I can say that I am using RAP but not DADE, not using TppSQLBuilder.
Indeed TFiltreReport is one of the objects from our application.

I created a file report .RTM which contains inside its main pipelaine the SQL query I wrote on my previous message.

After in our application, I do this stuff =

FReport := TppReport.Create(FMain)

... some code ...

FReport.CreateAutoSearchCriteria( ... );

... some code ...

FReport.Print;

When i call FReport.CreateAutoSearchCriteria( MainPipeLine, SSTIDSTATE_GESTCOM , soEqual , );

the function translate SSTIDSTATE_GESTCOM => STORESTATE_1.SSTIDSTATE_GESTCOM by default

But sometimes I want to Filter on STORESTATE_2.SSTIDSTATE_GESTCOM (not STORESTATE_1.SSTIDSTATE_GESTCOM )

So I Tried to call the function like that =

FReport.CreateAutoSearchCriteria( MainPipeLine, STORESTATE_2.SSTIDSTATE_GESTCOM, soEqual , );

But it does not recognize the fieldname, and it skips the filter criteria

It's why I am asking if is it possible to specify the table name or the exact alias of a fieldname inside the parameter FiledName of the function CreateAutoSearchCriteria()

I Hope you will understand.
Best reagrds,
David.

nicocizik · July 2018

Hi David,

I suggest using the TdaSQLBuilder to create AutoSearch criteria in code.

DADE is the data workspace where your main query for your report .rtm. If you are using the Report.CreateAutoSearchCriteria, you are using DADE

.

Unfortunately the CreateAutoSearchCriteria is a fairly old way to creating AutoSearch criteria in code and it only checks the actual field names when creating criteria.

The TdaSQLBuilder provides a much easier way to access the underlying SQL object and allows for search criteria to be created based on the field names or aliases.

See the help topics for TdaSQLBuilder and TdaSQLCriteriaList for more information on this object. Below is some sample code that uses an alias you might use.

uses daSQLBuilder;

---

  lSQLBuilder := TdaSQLBuilder.Create(MyPipeline);

  lSQLBuilder.SearchCriteria.AddAutoSearch('MyPipeline', 'SSTIDSTATE_GESTCOM2', '=', Value);
  lSQLBuilder.ApplyUpdates;

  lSQLBuilder.Free;

davidjoubard · July 2018

Hello,
Tank you for your answer Nico, I am going to try with TdaSQLBuilder.
Best regards,
David

Search on Fieldnames inside external joined tables

Comments