Copy Autosearch values to Subreport

rbuser

I have a report that has one sub report. The main report and the sub report
each have their own table with search criteria.
When the report runs I populate the search criteria on the main report but
would like to copy this search criteria to the sub report when the sub
report runs.

I have tried using GetAutoSearchValues with the following code:

lDataModule := daGetDataModule(ppReport2);

for liIndex := 1 to lDataModule.DataViewCount-1 do
begin
lQueryDataView := TdaQueryDataView(lDataModule.DataViews[liIndex]);

lSQL := lQueryDataView.SQL;

lSQL.Criteria[0].Value :=
ppReport2.AutosearchFields[0].SearchExpression;
lSQL.Criteria[1].Value :=
ppReport2.AutosearchFields[1].SearchExpression;
lSQL.Criteria[2].Value :=
ppReport2.AutosearchFields[2].SearchExpression;
lSQL.Criteria[3].Value :=
ppReport2.AutosearchFields[3].SearchExpression;


lQueryDataView.OutOfSync;

end;

Everything runs fine but the sub report does not seem to be using the search
criteria of the main report.

I am running RB Pro version 15.01

Thanks

nicocizik

Hi Phil,

Take a look at the following article/example on copying the same
autosearch criteria to multiple queries. You would want to do something
similar in your case as well.

http://www.digital-metaphors.com/rbWiki/DADE/SQLBuilder/How_To...Apply_Single_AutoSearch_to_multiple_queries

I also recommend upgrading your version of RB to the latest (15.04) to
stay up to date with all bug fixes and enhancements.

rbuser

That article did not seem to help me much. I could not get the example to
run or the code example to work with my code.
I have also tried to copy the auto search values using the following:

procedure TIWUserSession.ConfigureSubReportSearchCriteria;
Var
lSubReportSQL: TdaSQL;
begin
If GetSQLObject(ppSubReport2.Report, lSubReportSQL) Then
Begin
lSubReportSQL.Criteria[0].Value :=
ppReport2.AutosearchFields[0].SearchExpression;
lSubReportSQL.Criteria[1].Value :=
ppReport2.AutoSearchFields[1].SearchExpression;
lSubReportSQL.Criteria[2].Value :=
ppReport2.AutoSearchFields[2].SearchExpression;
lSubReportSQL.Criteria[3].Value :=
ppReport2.AutoSearchFields[3].SearchExpression;
End;
end;


function TIWUserSession.GetSQLObject(aReport: TppCustomReport; var aSQL:
TdaSQL): Boolean;
var
liIndex: Integer;
lDataModule: TdaDataModule;
lDataView: TdaDataView;
begin
aSQL := nil;
{get the datamodule}
lDataModule := daGetDataModule(aReport.MainReport);
if (lDataModule <> nil) then
begin
for liIndex := 0 to lDatamodule.DataViewCount - 1 do
begin
lDataView := lDataModule.DataViews[liIndex];
if (lDataView <> nil) and (lDataView is TdaADOQueryDataView) and
(lDataview.DataPipelines[0] = aReport.DataPipeline) then
aSQL := TdaADOQueryDataView(lDataView).SQL;
end;
end;
Result := (aSQL <> nil);
end;


As with my previous attempt it seems to apply the search expression to the
sub report but when the report generates it's pulling in all the data not
what is defined by the search criteria.

nicocizik

Hi Phil,

I apologize, the example in the article uses RAP. Below is a link to a
version that does not use RAP. I tested it and it functions correctly
copying the search criteria from one query to another.

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

I highly suggest using the TdaSQLBuilder rather than the TdaSQL object.
The SQLBuilder was designed as an easy-to-use wrapper around the SQL
object for performing tasks such as this one.

Though your code may function, you are assigning the values too late for
them to have any effect. As in the example, the autosearch criteria
needs to be set before the pipelines are opened.

nicocizik

Another (much easier) option would be to use Report Parameters to keep
your autosearch criteria synchronized across multiple datasets.

http://www.digital-metaphors.com/RBWiki/End-User/Fundamentals/Report_Parameter_Fundamentals

rbuser

Thanks for the new example. This one helped point me in the right direction
and it looks like I have things working now.