Subreport shows detail for All items

rbuser

I am editing a copy of an existing sales report.
We want to make a report that will just show
the totals unless we want to expand it.

I made a subreport, with the same query
and added the detail fields to that.
I clicked "Drill Down" for the Item Number field.

But when I expand the detail it shows detail for
all items over again, not just the one it should
be showing.

I think there must be something else I need to
do to "join" it, but I don't know what.

Please help,

Thanks!
Sue Mitchel

nardmoseley

You need to define a master/detail linking relationship between the totals
and the detail. Please the followig article.


------------------------------------------------------
Tech Tip: Linking SQL Queries for Master/Detail Data
------------------------------------------------------

The following example shows two options for linking SQL queries to create a
master/detail relationship.

In this example, we are using Delphi's DBDemos data to create a
Customer/Order relationship. Thus we wish to link the Orders detail to the
Customer master.

I. Delphi Query Linking
------------------------

a. Set the detail TQuery.DataSource property to point to the master
query's TDataSource component.

b. In the SQL "Where" clause for the detail query use a ':' followed by
the linking field name from the master:

example
select *
from orders
where orders.CustNo = :CustNo

Now each time the master record position changes, the detail query will
automatically be refreshed with the correct result set.


II. RB DataPipeline Linking
-----------------------------

a. Set the detail DataPipeline.MasterDataPipeline to point to the master
DataPipeline.

b. Use the detail DataPipeline.MasterFieldLinks property to define the
linking relationship

c. In the SQL for the detail, retrieve all records and sort them by the
linking master field:

select *
from Orders
order by CustNo


Notes:

1. Using RB DataPipeline, each query is executed only a single time - thus
performance is much faster.

2. RB Professional and Enterprise Editions include a visual Data environment
for creating SQL queries, defining linking relationships, and creating
Ask-At-Runtime parameters. Using the RB tools you could create the above
linked queries in about 10 seconds.


--
Tech Support mailto:support@digital-metaphors.com
Digital Metaphors http://www.digital-metaphors.com




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

"Susan Mitchel" wrote in message
news:40964581$1@dm500....

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

rbuser

When I right-click on the query and select View Server SQL,
I can see it, but I can't edit it...

I did not understand your response, but I thought the gist meant
I should make a second query and link it to the first.

I painstakingly created another query the same as the first
with the same tables, sort orders, filters...

I dragged that query on top of the first to link it
and linked them on the line-item detail guid.

I cancelled the report after about twenty minutes.
I tried linking them on the item number. It's still
wrong and shows every detail, not just this item,
but at least it's fast.

How else can I make this work, what else might I be doing wrong?

nardmoseley

A drill down report is essentially a master/detail report in which the
detail is only displayed when the user interactively clicks on the drill
down component to expand the detail.

On the Data side of things you need a summary query (i.e. master) and a
detail query. The detail query needs to be linked to the master query.
ReportBuilder does not currently support linking on a field of type Guid, so
you need to either use a different field or in the SQL convert the Guid to a
string.

The main report will be connected to the summary query and the drill down
subreport should be connected to the detail query.

There is an example of creating a drill down subreport in the Developers
Guide tutorials (RBuilder\Developers Guide\RBuilder.pdf).


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