Home Subreports
New Blog Posts: Merging Reports - Part 1 and Part 2

Subreport shows detail for All items

edited May 2004 in Subreports
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

Comments

  • edited May 2004

    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

    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited May 2004
    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?

  • edited May 2004

    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


    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
This discussion has been closed.