Jim,
Are the Typeid fixed in number(a defined group it is easier to do this way)
or are they dynamic(can range from A...to BB depending on user input and
definition)?.
i.e. A to F used to define all of the functional types or is A... to BB for
larger subdividing of the typeid by the user.
I've done a project tracking systems (several years ago) for capital project
for a large client that does 10-20 million a year in plant improvements.
That's why I understand your question a little better. I had reports that
had projectno, projectsubsect and projectdet and had to have totals for all
of the above doing comparison against the project budget (project total,
project subsection, project detail).
What I did was create a report with breaks for all the detail with subtotals
for the cost and project budget amount. Then reduced that down to display
just the recap of projsubsect and final reduced it down to show just project
totals. I had three reports that they could look at to see how they were
doing on their spending.
If the number of TypeID are fixed then you could do something like this in
your code!
CREATE TEMPORARY table Ttypetotals (typeid text ??,typetotal currency)
INSERT INTO Ttypetotals (typeid,typetotal) +
select typeid,sum(prjqty*trarate) +
from datatable group by typeid +
where prjdate between .vbdate and .vedate +
order by typeid
select typetotal into vtypeA indic itypeA from Ttypetotals where typeid =
'A'
.
.
select typetotal into vtypeZ indic itypeZ from Tytpetotals where typeid =
'Z'
This will give you the totals for all the typeid's in a var between the date
range you selected
Then in your report footer define the range of vars for vtypeA to vtypeZ to
display this data.
Another option is create your detail report just listing the detail in
order! Copy it to another report add breaking to this report by typeID
(showing the detail) with sum of the totals in break footer. Then copy this
report and eliminate the detail section and have a recap of just the breaks
sum by TypeID totals(recap report no detail). Then print the detail report
with out the breaks for your first report with not form feed and then print
the recap report showing the temp table totals. (combine printing of two
reports).
if the TypeID are dynamic then it is hold new ball game! Because you have
to define the var in the report to display in the report footer. This would
not be possible because var definition in the report is static(done upon
report creation) and can not be defined on the fly.
Best regards,
Oma
