Subreports in Footer

rbuser

I have just about tried everything I can think of to get this working and I
can't seem to find the answer searching the newsgroups or in the demos so I
thought I would try to explain my problem and hopefully someone may have a
solution for me.

I have a report with 2 dataviews. The first dataview is for the main report
and consists of 4 autosearch fields which I supply in code from a custom
form and each use the between operator. This dataview is also sorted by 3
fields (let's say field1, field2 and field3) and the report prints a summary
of the detail in the footer of field2. Now, in the footer of field 3 I want
to add a subreport that is linked to the second dataview. This dataview
will contain the exact same data/fields as the first dataview except it's
sorted differently since I need to display the data in a slightly different
way and this is the subreport I can't get to work correctly.

This second dataview has the same autosearch fields as the first with the
exception that the operator for field3 would be equals since I want only the
data that is for the field3 group. Does this make sense.

Does anyone of a way I can accomplish this?

Would it be possible to supply the autosearch values to the subreport each
time the subreport is printed and also grab the value of field3 from the
main dataview?


I am using report builder 7.03 enterprise and Delphi 5 enterprise.

nardmoseley

Sorry, but I am having a hard time following the description. Here are some
observations.

Each report/subreport needs to be connected to its own dataview. If you are
using the Query tools to build SQL dataviews, then each query will fire a
single time, at the beginning of the report. Therefore you will need to
supply the autosearch values to all of the applicable queries prior to
generating the report.

The Footer band is the one band in RB that must always be Static height -
therefore only fixed style subreports can be used. As an alternative, use a
GroupFooter or Summary band.

main - dataview1
detail
group footer/summary
- subreport - dataview2

If the data is grouped then dataview2 needs to linked to dataview3 so that
it will only print the appropriate records.



--
Nard Moseley
Digital Metaphors Corporation
www.digital-metaphors.com

rbuser

Sorry, it's been a confusing issue for me and I found it hard to explain.

I am trying to print the sub reports in the group footer but I can't seem to
narrow the data down to what I need. How do I get dataview 2 to only
contain the records used in the group footer/summary?

Let's take these 4 sample records.

Field 1 Field 2 Field 3
=======================
ABC 1 3.00
ABC 2 4.00
ABC 2 1.00
DEF 3 4.00

Now the group footer/summary is by field 1 and I am display the sum of field
3 which would be 8.00 for the first group footer and 4.00 for the second.
Now, in the group footers I want a sub report that will group by field 2 for
just the records used in that group footer and also sum field 3. So the sub
report for the first group footer would have 2 listings. The first being
3.00 and the second being 5.00. The sub report for the second group footer
would have only one listing with a value of 4.00

I am currently proving the autosearch values prior to generating the report
but this is causing the sub reports to display the same values in all the
group footers.

Can I link the two tables together using the query tools by field 1 and also
provide autosearch values to field 2 and field 3 prior to generating the
report?

Does this help explain or does it muddy the water some more.

Thanks,
Phil

nardmoseley

For the detail query try creating a summary query that is grouped by field2
and sums Field3 as show below. Then link it to the master on Field1.

Select Field1,
Field2,
Sum(Field3) as SumField3,
From myTable
Group By Field1, Field2



--
Nard Moseley
Digital Metaphors Corporation
www.digital-metaphors.com

rbuser

The problem that I see with this is that I am providing autosearch values on
the primary dataview to limit the data used. I have an autosearch for
field1, field2 and field3.

The method you describe would work but would pull ALL the data linked to
field1 where I may only be pulling a range of data based on the autosearch
values supplied for field2 and field3.

If I can link the tables on field1 and also supply autosearch values to the
subreports dataview when the report is run then that may work. Can this be
done?

Thanks,
Phil

rbuser

I just tried what you recommended below but I am not getting the correct
results.

The totals displaying for the subreport in each group footer are the same.
The link to the master table does not seem to be working and it's seeing all
the data when the subreport prints.

Thanks,
Phil

nardmoseley

I created a couple of examples and was able to accomplish what you describe.
I created a sample paradox table that contains the data from your example.

You can email support@digital-metaphors.com and I can email you a zip file
containing the examples.

1. For the first step, I created a master/detail dataview relationship and
used a subreport in the group footer of the main report to print the detail
data (i.e. the summarized results).

2. For the second step I added autosearch criteria to the master query. I
used the Report.OnGetAutoSearchFieldValues event to apply the search value
entered by the user to the second detail query. I used RB 9 to do this,
because it contains a new TdaSQLBuilder class that simplifies manipulating
sql at runtime.

Here is the TdaSQLBuilder code I used...

procedure TForm1.ppReport1GetAutoSearchValues(Sender: TObject);
var
lSQLBuilder: TdaSQLBuilder;
lsSearchValue: String;
begin

// create the sql build object for the detail data
lSQLBuilder := TdaSQLBuilder.Create(ppSubReport1.DataPipeline);

// clear the current search criteria
lSQLBuilder.SearchCriteria.Clear;

// apply autosearch value to the detail query
if not ppReport1.AutoSearchFields[0].ShowAllValues then
begin
lsSearchValue := ppReport1.AutoSearchFields[0].SearchExpression;
lSQLBuilder.SearchCriteria.Add('Sample', 'Field1', '=', lsSearchValue);
end;


lSQLBuilder.Free;

end;


--
Nard Moseley
Digital Metaphors Corporation
www.digital-metaphors.com