Create AutoSearch fields in code

rbuser

Hi guys,

I have couple of questions in regards to RAP:

1. How can I determine if user edited SQL manually or if it was generated by
QueryDesigner ?
2. I understand that if user edited SQL manually, RB wouldn't allow to
add/edit search criteria; To get around this, I want to display some custom
Search Dialog and then re-generate SQL query. I tried to do that in the
following code to force RAP to display my custom Search Dialog Form on
globalOnCreate event( I overridden TAutoSearchDialog.GetPanelClassForField
method and created my custom SearchDialogs by deriving them from
TppSimpleSearchPanel):

Report.ShowAutoSearchDialog := True;
sField := Report.CreateAutosearchCriteria('MyPipeLine', 'MyField', soLike,
'', True);

But this doesn't seem to work.
It didn't work even if the Query was generated by Query Designer.
How to add search criteria "on the fly" ?

Cheers,
Dmitry

nardmoseley

1. The TdaSQL.EditSQLAsText boolean property indicates whether the SQL has
been edited as text. The following is an example of how to extract the
TdaSQL object associated with a dataview.

http://www.digital-metaphors.com/tips/ExtractSQLObject.zip

2. RAP was originally designed to enable calculations to be performed and
the report elements to be controlled while the report is generating. Most
of the events in RAP occur too late to be useful for working with the
DataSet. The exception to this is the AutoSearch related events, but these
only fire if AutoSearchFields[] are present. You might using the
Report.Template.OnLoadEnd event instead - this is a Delphi event - not RAP.

3. The following is an example of manually editing SQL and using AutoSearch.

http://www.digital-metaphors.com/tips/EditSQLAndSearch.zip



--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Thanks Nard,

Number one solved.

Regarding the answer number 2.

From what you said, it's not possible to create autosearch fields in RAP, is
it ? Or even if they created in RAP code, it's too late as those criteria
won't get "picked up" by report engine ? What I really wanted to was invoke
my pass-trough function on Global onCreate RAP event passing report as var
parameter. Then edit report's dataview SQL and pass report back to RAP.
Well, I don't know what to do then . Your answer number 3 is not applicable
in my case as we have end-user solution(with Report Explorer) and I want all
reports to be self-contained and not re-compile exe every time we add/edit
report.

Thanks a lot,
Dmitry

nardmoseley

The autosearch related events occur early enough to accomplish what you
need. However these events only occur when at least one autosearch field is
defined.

A possible work around might be to define at least one autosearch criteria
for each report. (In the Report.Template.OnLoadEnd event you could do check
each report and add an autoseartch field if none exist.) Then use the
BeforeAutoSearchDialogCreate event in RAP to clear the fake autosearch field
and define any others that you need. Use the OnGetAutoSearchFieldValues to
call your RAP function that modifies the TdaSQL.



--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Thanks Nard,

that seems to be the way to go !!!

Cheers,
Dmitry

rbuser

I"ve been send to this thread as i had a similar question.
No problem on the possible solution but how do i test this, my case is
slightly different, and i can't develop my report from the designer, as the
autosearch dialogs do not get prompted in a switch to preview !

nardmoseley

By design, the report designer preview tab does not automatically show the
autosearch dialog, because that would be annoying when interatively
designing and previewing the report. You can press the Search button on the
preview tab to display the autosearch dialog. When you launch the report
directly to preview or print, then AutoSearch dialog is automatically
displayed.


--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Nard, i've modified the code, but i'm getting a crash in rbdad77.
My idea was the following, the report runs with the existing dade sets. But
the ordering doesn't work.
So, i just modify the sql, to a resultset with the same fieldnames as the
generated sql.
But ... missing something somewhere i'm afraid. (In this test de where's are
still hardcoded).

Or, is the system trying to parse my sql statement ??
Must i first set a property to indicate that i'm editing the sql manually ?

procedure TSdwRapSet_001_TGBO_SqlFunction.ExecuteFunction(aParams:
TraParamList);
var
aReport : TppCustomReport;
aDataModule : TdaDataModule;
aDataView : TdaDataView;
aSql : TdaSql;
iLoop : Integer;
SqlStatement : TStringList;
begin
LogStart('SdwRap', 'TSdwRapSet_001_TGBO_SqlFunction.ExecuteFunction');
GetParamValue(0, aReport);
aDataModule := daGetDataModule(aReport.MainReport);
aSql := nil;
if aDataModule <> nil then
begin
iLoop := 0;
while (iLoop < aDataModule.DataViewCount) and (aSql = nil) do
begin
aDataView := aDataModule.DataViews[iLoop];
LogIt('SdwRap', 'aDataView', aDataView);
if (aDataView <> nil)
and (aDataView is TdaQueryDataView) then
// and (aReport.Datapipeline.Dataview = aDataview) then
begin
aSql := TdaQueryDataView(aDataView).SQL;
LogIt('SdwRap', 'aSql', aSql);
LogIt('SdwRap', 'MagicSql Before', aSQL.GetMagicSQLText.Text);
if aSql.DataPipelineName = 'Complex' then
begin
SqlStatement := TStringList.Create;
SqlStatement.Add('SELECT DSH.COMP_ID,');
SqlStatement.Add(' TF.COMPF_ID,');
SqlStatement.Add(' (CF.COMPF_NAME + '' '' +
ISNULL(CF.COMPF_EXT,'''')) AS COMPF_NAME,');
SqlStatement.Add(' CPRF_NBR AS PERFORMANCE_NUMBER,');
SqlStatement.Add(' CPRF_ID AS PERFORMANCE_ID,');
SqlStatement.Add(' SUM(HALL_CAPACITY) AS
SUM_RBld_001_Visitors_By,');
SqlStatement.Add(' SUM(DSH_TICKETS) AS
SUM_RBld_001_Visitors_B_2,');
SqlStatement.Add(' SUM(DSH_GBO) AS
SUM_RBld_001_Visitors_B_3,');
SqlStatement.Add(' (SELECT SUM(DSH_GBO)');
SqlStatement.Add(' FROM TS_DAYSALHEAD DSH2');
SqlStatement.Add(' INNER JOIN TS_TICKFEAT TF2 ON
DSH2.COMP_ID = TF2.COMP_ID AND DSH2.TICKF_ID = TF2.TICKF_ID');
SqlStatement.Add(' WHERE DSH2.COMP_ID = DSH.COMP_ID');
SqlStatement.Add(' AND DSH_DATE BETWEEN
CONVERT(DATETIME,''2004-01-01'',120) AND
CONVERT(DATETIME,''2004-01-02'',120)');
SqlStatement.Add(' AND TF2.COMPF_ID = TF.COMPF_ID) AS
SUM_RBld_001_Visitors_B_4');
SqlStatement.Add('FROM TS_DAYSALHEAD DSH');
SqlStatement.Add(' INNER JOIN TS_TICKFEAT TF ON DSH.COMP_ID =
TF.COMP_ID AND DSH.TICKF_ID = TF.TICKF_ID');
SqlStatement.Add(' INNER JOIN TS_COMPFEAT CF ON TF.COMP_ID =
CF.COMP_ID AND TF.COMPF_ID = CF.COMPF_ID');
SqlStatement.Add(' INNER JOIN COMPLEX COMP ON DSH.COMP_ID =
COMP.COMP_ID');
SqlStatement.Add(' INNER JOIN COUNTRY_PERFORMANCES CPRF ON
COMP.COU_ID = CPRF.COU_ID');
SqlStatement.Add(' AND ( DATEPART(HOUR, DSH_TIME) >
CPRF_FROM_HOUR');
SqlStatement.Add(' OR ( DATEPART(HOUR, DSH_TIME)
= CPRF_FROM_HOUR');
SqlStatement.Add(' AND DATEPART(MINUTE,
DSH_TIME) >= CPRF_FROM_MIN))');
SqlStatement.Add(' AND ( DATEPART(HOUR, DSH_TIME) <
CPRF_TO_HOUR');
SqlStatement.Add(' OR ( DATEPART(HOUR, DSH_TIME)
= CPRF_TO_HOUR');
SqlStatement.Add(' AND DATEPART(MINUTE,
DSH_TIME) <= CPRF_TO_MIN))');
SqlStatement.Add(' INNER JOIN TS_HALL H ON DSH.COMP_ID =
H.COMP_ID AND DSH.HALL_ID = H.HALL_ID');
SqlStatement.Add('WHERE DSH.COMP_ID = ''DECA'' AND DSH_DATE
BETWEEN CONVERT(DATETIME,''2004-01-01'',120) AND
CONVERT(DATETIME,''2004-01-02'',120)');
SqlStatement.Add('GROUP BY DSH.COMP_ID,');
SqlStatement.Add(' TF.COMPF_ID,');
SqlStatement.Add(' (CF.COMPF_NAME + '' '' +
ISNULL(CF.COMPF_EXT,'''')),');
SqlStatement.Add(' CPRF_NBR,');
SqlStatement.Add(' CPRF_ID');
SqlStatement.Add('ORDER BY SUM_RBld_001_Visitors_B_4 DESC,
TF.COMPF_ID');
aSql.SQLText := SqlStatement;
LogIt('SdwRap', 'Statement Set');
SqlStatement.Free;
LogIt('SdwRap', 'After Sql', aSQL.SQLText.Text);
LogIt('SdwRap', 'MagicSqlAfter', aSQL.GetMagicSQLText.Text);
end;
end;
Inc(iLoop);
end;
end;
LogEnd('SdwRap', 'TSdwRapSet_001_TGBO_SqlFunction.ExecuteFunction');
end;

nardmoseley

Please see my first response to this thread. It contains relelvant
information and download links to examples. It states that you must set the
TdaSQL.EditSQLAsText boolean property to manually edit the text.

Once you manually edit the SQL Text RB will not longer generate the SQL or
modify SQL based upon AutoSearch parameters values. This is shown in one of
the examples mentioned above.

--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Nard,

I've rechecked my mail and i've seen that i posted a source-code-version
without the EditSQLAsText set. I had tried it but with or without, still
crashing. As this seems again to become a case that i will have to provide a
'crashing' example (what i realy do understand), i started to try it over
another way, but i've noticed some strange things. These might be normal but
i would like them to be confirmed.
I first started with a normal adoquery on a datamodule, in regular delphi. I
assigned it to be my main datasource, from that moment on, the 2 other
datasources that are in dade, not even linked ones couldn't be found(opened)
anymore, is a mixture impossible ??
Secondly, i'm developping mu reports from a form that contains everything
needed to develop and store the templates in the database. For my
'hardcoded' datasets and eventually delphi-code i created a datamodule, i've
put my dataset on in. The report find the data and even if i run the
formbased app it works.
Than, on my report server i also include this datamodule. Now, when trying
to run my report rap complains that he can't calculate some variables. In
the rap code of these vars i'm referencing the dataset that is on the
datamodule. Might it be that he can't find it ??

Happy Eastern.

nardmoseley

A. You should be able to mix datapipelines on a form/datamodule with the
DADE datapipelines. I tried this and could not find an issue. Please specify
steps for me to recreate it. In my test I place a TppReport on form and
created a DADE query. Then placed a DBPipeline on the form and both appeared
available in the Design workspace of RB, by that I mean I can select either
one for the report or for a data-aware component. I tried the same test with
a datamodule and that also worked (I used DBDemos, but same should be true
for ADO or any other connectivity)

This is an example of linking a Dade DataPipeline to a DataPipeline on a
form:

http://www.digital-metaphors.com/tips/LinkDADEPipeToStandardPipe.zip


B. The server is multi-thread - global datamodules are not used - these
would not be thread safe. The Server requires a separate database connection
for each thread. Therefore all data access components and code should be
placed on the same datamodule as the ReportVolume component. This is shown
in the demos and tutorials.



--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com

rbuser

On item B

Are there any techniques that make it easy to develop the report with
'hard-code' and run it on a server without copying the code.
I'm thinking the way you run the webtier as a dll and a exe.

Beacause i'm afraid that copying code will result in errors ........

rbuser

What i did, i've placed a ppReport in the datamodule in my server, and from
my form-based/develop/report application i'm using this datamodule too.
I know that in earlier versions of RB it warned not to place a report on a
datamodule.
Although this report is only used to develop isn't there a problem placing
this on the server module ????

rbuser

Part 18

No just joking, but i'm trying to get some special stuff done and its
getting very close to a solution.
My problem was that i wanted to use the autosearch & dade & a regular
ADOquery in 1 report.
Got this part working fine, i'm generating in rap the query of the adoquery
on the OnGetAutoSearchValues.
Works fine (code below), but, at the same time i wanted not a double
environment and i used my server-datamodule in a form.
Works fine also, but .....

Now, when running my reports in a server the parameters don't get loaded, as
i'm using the parameter to indicate to the webtier which form to display
this is very enoying.

Any idea or suggestion ??



var

aPipL : TppDBPipeline;

aDs : TDataSource;

aQry : TADOQuery;

xFrom,

xTo : TDateTime;

aSql : TStringList;

WasOpen : Boolean;

begin

csSendMsg('OnGetAutoSearchValues');

aPipL := Report.DataPipeline;

aDs := aPipL.DataSource;

aQry := aDs.DataSet;

aSql := TStringList.Create;

xFrom := Report.AutoSearchCriteriaByName('DaySalBuffer',
'Dsh_Date').Values[0];

xTo := Report.AutoSearchCriteriaByName('DaySalBuffer',
'Dsh_Date').Values[1];

csSendString('From Date', FormatDateTime('dd/mm/yyyy', xFrom));

csSendString('To Date', FormatDateTime('dd/mm/yyyy', xTo));

aSql.Add('SELECT DSH.COMP_ID,');

..........

aSql.Add('ORDER BY TGBO DESC, TF.COMPF_ID');

csSendStringList('Sql Would Be', aSql);

if aQry.Active then

begin

WasOpen := True;

aQry.Active := False;

end

else

WasOpen := False;

aQry.SQL := aSql;

if WasOpen then

aQry.Active := True;

end;

rbuser

Sorry, found the problem.

As there existed alreay a 'hardcoded' but unused (in server) report on the
datamodule with the parameters with this name the 'running' report its
parameters got other names. So i couldn't find the parameters.

I will reference the parameters by index !!

Otherwise, finally working !!!!!!

nardmoseley

RAP is designed to enable the code to be saved with the report.

If you have to write code outside of RAP then the code needs to reside in
the same datamodule as the ReportVolume. One instance is used for each
thread.

With the WebTier, the code resides in the WebModule - one instance is used
for each thread.


--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com

nardmoseley

When using Delphi 6 and Delphi 7, there are no limitations for placing a
TppReport on a Delphi TDataModule. (It is only Delphi 5 and only at Delphi
design-time that a limitation exists).


--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com