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

synchronizing fields

edited November 2002 in Devices
I want to modify an existing report and add some counts. I need to add a
new query since there is no way to incorporate this with the existing one.
The report does a daily detail uses group footer to display daily totals.
It is in the group footer that I want the addtionial data to display. The
group footer has the field ppDBText11 which is the date field. I have added
a variable and added a Calc event where I pass a parameter to my new query
so as to populate my new ppLabelField with the new output that I wish to
display. My problem is that all the ways I have tried to pass the
ppDBText11 date (that advances each day), I only get the initial start date,
and therefore the results for each date.

My method would work if I could cature the ppDBText11 date each time it
changed, and then run the query again and repopulate the field for display.

I have also tried adding another datapipeline to hold my dataset but am not
able to pull this off. I am not all that up on Report Builder but can
usually get it to do what I want.

RBPro 5.56
D-5 Ent.
IBO
TIA
Larry

Comments

  • edited November 2002
    You won't want to change the query as the report is running. Two options
    exist.

    1. Define a field on dataset (use a SQL query) which is a calculated field.
    This field will show up in the datapipeline and you can then assign it to
    the DBText in the group footer.

    2. Use a TppVariable and in the OnCalc event to sum or count using the
    datapipeline's field value.

    Value := Value + Report.Datapipeline['Amount Paid']


    Cheers,

    Jim Bennett
    Digital Metaphors

  • edited November 2002
    Thanks
    Larry Killen
  • edited November 2002
    I am probably not explaining my dilema properly. I'll try to expand.

    First, I am assuming there is no way to attach a second query (and therefore
    2nd datasource, to a pipeline). It wouldn't seem possible to me, but I
    could be wrong.

    I have a couple of fields that are displayed in a group footer that roll on
    the date (daily) which is defined in the group by clause of the query.
    Works fine - lasts a long time.

    My client wants to add an additional field to the report but the only thing
    that field has in common is the date. The existing query is already versy
    complex and four deep join and I have tried to integrate the new query into
    the existing one and the results is not practical. It is painfully slow,
    even though properly indiced.

    So all I want to do is to drop another field on the report group footer from
    a different query that rolls on the same data. To do so, one would assume
    (at least I do) that the dates in the two queries need to be bound in some
    fashion, since there is a high probability that one query will return a
    value for a give date where the other won't.

    Is there a way to add another field value from a seaparate query as
    described above.

    ver 5.56
    D-5 ent
    IB 6.5
    IBO 4.x


  • edited November 2002
    You'll have to fire a parameterized query to get this record information as
    the other query is traversed by RB. You'll need a separate datasource and
    datapipeline to add a new field for this report. You can connect the DBText
    in the report to this new pipeline and field value, even though the report
    is connected to a different pipeline.


    Cheers,

    Jim Bennett
    Digital Metaphors

  • edited November 2002
    I can't believe that Report Builder can't handle this! Below are two
    queries I want in my report. The dates are identical but return two
    different Item types, Doors and DrawerFronts. I have tried using Views but
    they are unforgivably slow. The columns are all indiced optimally and the
    indices are utilized. Views cannot be filtered and the cardinal counts are
    very high. I have also combined the results in one query and it is equally
    slow. > 10 minutes for about two weeks data.

    Running each query indepently takes less then 2 seconds each.


    I tried the second pipeline but only get the first date in the second
    datasource and therefore that qnty for each date that the first pipeline
    fires.

    All I want is a report that shows:

    ShipDate QntyDoorsShipped QntyDFShipped
    11/04/2002 1105 542
    11/05/2002 758 56
    11/06/2002 882 329

    etc., etc.

    How do I accomplish this?



    select sum(oi.quantity) as QntyDoorsShipped, o.SHIP_DATE
    from order_item OI
    inner join orders O on (OI.order_id = O.order_id)
    inner join product P on (OI.product_id = P.product_id)
    where
    P.product_type_id = 2 and
    o.SHIP_DATE >= :BegShipped and
    o.SHIP_DATE < :EndShipped
    group by o.SHIP_DATE


    select sum(oi.quantity) as QntyDFShipped, o.SHIP_DATE
    from order_item OI
    inner join orders O on (OI.order_id = O.order_id)
    inner join product P on (OI.product_id = P.product_id)
    where
    P.product_type_id = 3 and
    o.SHIP_DATE >= :BegShipped and
    o.SHIP_DATE < :EndShipped
    group by o.SHIP_DATE






  • edited November 2002
    From my experience, RB can pretty much do anything. I reread from the top of
    this thread. You have a data pipeline. It works fine. You want more
    information from a different query on top of this query.

    Limitations are such that you can't hook up multiple data pipelines to a
    single report. This is necessary in ordre to traverse a dataset
    automatically by RB so you don't have to fire a parameterized query on every
    record which would be slow. You can hook the second datapipeline up to a
    new subreport. Place the new subreport in the group footer. Resize the
    detail subreport's detail band to zero height. Use the detail subreport's
    summary band to print the summary information. Give this a shot. I think
    you'll have to do something similar to this. If you can't get it working
    with a separate subreport and datapipeline, then let me know.

    Cheers,

    Jim Bennett
    Digital Metaphors

This discussion has been closed.