synchronizing fields

rbuser

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

digitalmetaphors

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

rbuser

Thanks
Larry Killen

rbuser

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

digitalmetaphors

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

rbuser

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

digitalmetaphors

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