summarizing dates from two tables?

rbuser

I would like my end user to be able to create a report like:

THIS YEAR LAST YEAR

Attempts Success % Attempts Success %
Jan 36 12 33% 10 2 20%
Feb
Mar (etc.)
...
Nov
Dec

Annual 480 120 25%
TOTAL

Monthly 40 10 25%
AVG

I have two tables in the same data base, one for attempts
and a separate one for successes, and each has the dates,
although the success table has a couple fields that must be
analyzed to produce the actual success date.

Since the user may want to create many reports on this
template, as much as possible should be done on the end-user
side.

MY ATTEMPTS: I have put the results of a query into the
detail band, and made the detail band invisible. Created
the above report layout and variables in the Summary Band.
When the detail band text is calculated (DBTextOnGetText
event), I accumulate the variable values that belong in the
summary report.

PROBLEM #1: The OnGetText event is referred to many times,
so I have had to use a global variable in order to check to
see that a value is not accumulated from the detail lines
more than once. Seems there may be a more efficient way.

PROBLEM #2: I can get the correct values for attempts or for
successes, by setting the data pipeline to the query for
attempts or for successes, but not both at the same time.
If I try to include the success table and the attempt table
in the same query, all the data is present in the resulting
query, where in theory it can be parsed through, but the
resulting joined table is so huge with many repeated attempt
data sets, that the thousands of records make the analysis
VERY slow.

IDEAL - if I could simply read through the attempts table to
calculate the year and month variable to accumulate,
and then simply read through the success table to do the
same. Or can I create two separate queries and read through
them like a table to calculate the variables. I do not see
how to loop through the query sets????

Pardon my long question. Any help much appreciated!

Scott Stalheim

rbuser

Don't known the queries you are using but it can be faster not joining these
tables but creating 2 selects with a union all.
The use of indexes and detailled selections can be done on each query.
The layout of these selects must be the same but you can trick the union by
doing a select A, B, 'whatever', C union select A, B, D, 'whatever'.
If you use this technique for reportbuilde it is even better to create a
view on these selects/union all. So you can still use dade.
We are using thsi technique very often for some complex queries.

Hope this might give you a hint.

nardmoseley

I would agree with Harry suggestion of creating a database view or creating
a temp table in the database that contains the combined data. Particularly
since you have many reports to perform on this data. Doing some work on the
database side of things can greatly simply the reports.

The other approach I can think of is to build two queries and link them. One
of the queries can be the 'master' and one can be a lookup (i.e. it is a
detail query but there is a one-to-one relationship with the master).
Connect the Report to the master data. Then in the report layout you can
connect the data-aware components to either query.



--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Thanks for the help. It turns out that it was a little
work, but i changed the data side to accomodate the reports.
This will now give the end-user many more user-friendly
options.

But back to my other question:

I hide the detail band because the user is really only
interested in the summary of data. So I have the
end-user accumulate values for the summary band variables by
having him use the OnGetText event from the detail band.

PROBLEM is that I find the OnGetText event must fire several
times, so I must use a global variable to track this and
prevent too many calculations from occuring. This slows up
the process, and is confusing to the usual end-user who
wishes to build on my sample reports. Is there a better
event to use to accumulate totals from the detail band?

Scott
stalheim@tds.net

nicocizik

Hi Scott,

Whenever making calculations in ReportBuilder, it is always best to make
calculations in the OnCalc event of the TppVariable component. For instance
you could place a TppVariable component in your detail band with the
visibility set to False. Then in its OnCalc event, update the value of
another TppVariable component in the summary band essentially keeping a
runnng grand total. See the article below for more information.

----------------------------------------------------------------------
TECH TIP: Performing Calculations
----------------------------------------------------------------------

Calculations can be done either on the data access side
or within ReportBuilder.

When designing reports there are always decisions to be made as to
how much processing to do on the data side versus the report side.
Usually doing more on one side can greatly simplify the other. So it is
often a personal choice based on the power and flexibility of the data
and report tools being used.


DataAccess
----------

a. Use SQL - using SQL you can perform many common calculations:

example: Select FirstName + ' ' + LastName As FullName,
Quantity * Price AS Cost,


b. Delphi TDataSets enable you to create a calculated TField object
and use the DataSet.OnCalcFields event

c. Perform any amount of data processing, summarizing, massaging
etc. to build a result set (query or temp table) to feed to the report.


ReportBuilder
-------------

Calculations in ReportBuilder are performed primarily using
the TppVariable component.

a. Set the Variable.DataType

b. Code the calculations using the Variable.OnCalc event.

c. Use the Timing dialog to control the timing of the OnCalc event.
To access the Timing dialog, right click over the Variable
component and select the Timing... option from the speed menu.

d. Set the LookAhead property to True, when you need to display
summary calculations in the title, header, group header, etc.

e. To perform calculations based on the results of other
calculations use the Calc Order dialog of the band. To access
the Calc Order dialog, right click over the Band component
and select the Calc Order... option from the speed menu.


By using TppVariable components ReportBuilder will take care of caching
intermediate results of accumlated calcs that cross pages.

There are a number of calculation examples in the main demos.dpr
project.

---

Additional Notes:

1. Do NOT use Band.BeforePrint or Band.AfterPrint. These events fire
multiple times and therefore should not be used for calculations.

2. Do NOT store results in variables that exist outside of the reports.
For example - form level variables.


--