Summing SubReport

[Eric]

I'm developing a report that forecasts revenue for a job
by quarter over the duration of the job. The duration
may be more than a year.

I've successfully created a Report/subreport that shows:
Job# Year 1stQ 2ndQ 3rdQ 4thQ
1234 2003 0 0 0 100
2004 100 50 200 50
2005 150 0 0 0

The Job# is in the main report and the year/quarters are
the subreport. They in the Detail level. This all works
dandy.

Now I need to report totals summing the activity by year
for each quarter. I need to do this in group and report
footers. I've tried cloning the Detail level subreport
and changing the textboxes for each quarter to be sums.
I've tried setting the Running Sum property to "On
Group". I've tried doing it explicitly with =sum
([Q1Revenue]), etc. At best, I'm getting a sum that is
only only has the last detail line reported before the
group footer. At worst, the report hangs during
formatting and I have to break out of it.

Any advise on how to get sums working in this application
would be greatly appreciated....Eric

 

[Ralf Mengarda]

[email protected]...

I'm developing a report that forecasts revenue for a job
by quarter over the duration of the job. The duration
may be more than a year.

I've successfully created a Report/subreport that shows:
Job# Year 1stQ 2ndQ 3rdQ 4thQ
1234 2003 0 0 0 100
2004 100 50 200 50
2005 150 0 0 0

The Job# is in the main report and the year/quarters are
the subreport. They in the Detail level. This all works
dandy.

Now I need to report totals summing the activity by year
for each quarter. I need to do this in group and report
footers. I've tried cloning the Detail level subreport
and changing the textboxes for each quarter to be sums.
I've tried setting the Running Sum property to "On
Group". I've tried doing it explicitly with =sum
([Q1Revenue]), etc. At best, I'm getting a sum that is
only only has the last detail line reported before the
group footer. At worst, the report hangs during
formatting and I have to break out of it.

Any advise on how to get sums working in this application
would be greatly appreciated....Eric

Hey,

i don´t know if i catched that right but try
this:
textfield with datasource =Domsum("[Activity]";"[YourTable]";"[YourYear]='"
& year e.g 2003 & "'")
for every year

gR

 

[Marshall Barton]

I'm developing a report that forecasts revenue for a job
by quarter over the duration of the job. The duration
may be more than a year.

I've successfully created a Report/subreport that shows:
Job# Year 1stQ 2ndQ 3rdQ 4thQ
1234 2003 0 0 0 100
2004 100 50 200 50
2005 150 0 0 0

The Job# is in the main report and the year/quarters are
the subreport. They in the Detail level. This all works
dandy.

Now I need to report totals summing the activity by year
for each quarter. I need to do this in group and report
footers. I've tried cloning the Detail level subreport
and changing the textboxes for each quarter to be sums.
I've tried setting the Running Sum property to "On
Group". I've tried doing it explicitly with =sum
([Q1Revenue]), etc. At best, I'm getting a sum that is
only only has the last detail line reported before the
group footer. At worst, the report hangs during
formatting and I have to break out of it.

I think your best bet is to create a query that calculates
the totals you want. Then create another subreport (based
on that query) that can be placed in the group footer. Use
the group ID in the link master/child properties.

 

[Eric]

Marsh...After my original post, I kept playing with the
problem and in fact tried what you've suggested. It
worked kinda.

I made a new query joining the main report record source
query with the table holding the detail year/quarter
revenue records for each Job#. The table has a right
join with the query on Job#. The query groups by Job
Class (the field that is the report GroupID) and the
year. The 4 quarter fields in the record are summed.

The subreport is set up with the field name for the group
(Job Class) as the parent and child links.

I get output, but the amounts reported are wrong. A step
forward as at least I'm getting some kind of results to
debug. Any ideas why the results are inaccurate?

Also, it takes a relatively long time (several minutes)
to run. This is going to be a problem even if I can get
the correct results. Any ideas in this area as well?

TIA for any advise you may have....Eric

-----Original Message-----

I'm developing a report that forecasts revenue for a job
by quarter over the duration of the job. The duration
may be more than a year.

I've successfully created a Report/subreport that shows:
Job# Year 1stQ 2ndQ 3rdQ 4thQ
1234 2003 0 0 0 100
2004 100 50 200 50
2005 150 0 0 0

The Job# is in the main report and the year/quarters are
the subreport. They in the Detail level. This all works
dandy.

Now I need to report totals summing the activity by year
for each quarter. I need to do this in group and report
footers. I've tried cloning the Detail level subreport
and changing the textboxes for each quarter to be sums.
I've tried setting the Running Sum property to "On
Group". I've tried doing it explicitly with =sum
([Q1Revenue]), etc. At best, I'm getting a sum that is
only only has the last detail line reported before the
group footer. At worst, the report hangs during
formatting and I have to break out of it.

I think your best bet is to create a query that calculates
the totals you want. Then create another subreport (based
on that query) that can be placed in the group footer. Use
the group ID in the link master/child properties.

 

[Marshall Barton]

Marsh...After my original post, I kept playing with the
problem and in fact tried what you've suggested. It
worked kinda.

I made a new query joining the main report record source
query with the table holding the detail year/quarter
revenue records for each Job#. The table has a right
join with the query on Job#. The query groups by Job
Class (the field that is the report GroupID) and the
year. The 4 quarter fields in the record are summed.

The subreport is set up with the field name for the group
(Job Class) as the parent and child links.

I get output, but the amounts reported are wrong. A step
forward as at least I'm getting some kind of results to
debug. Any ideas why the results are inaccurate?

Also, it takes a relatively long time (several minutes)
to run. This is going to be a problem even if I can get
the correct results. Any ideas in this area as well?

The key to any report is to get the record source query
working properly before you try to use it in a report.
Until you get the correct numbers, etc, just debug it in
the query design window.

I don't know if I can spot anything that would cause the
report to be so slow, certainly not until I can see the
relevant tables and queries.