Calculating percentage in subsummary

[Kent Searight]

Hi all,

I'm throwing the towel in and asking for help on this one.

I'd like to show the percentage of sales goals in a subsummary report. Here's my current setup:

The report shown is based on the table Projects.

The subsummary fields under the headers New Quotes and New Orders are based on Projects as well.

The number I want to come up with in Goal Achieved is the result of the New Quotes value of a given quarter divided by the value in Budgeted Quarterly Sales Goal (shown in header), then that result quotient multiplied by 100. In the case of the example it should be 103% for Q-2 2007

The value in Budgeted Quarterly Sales Goal comes from a related table of salespeople, keyed via salespeople ID in Projects.

Anyway, as you can see in the example it's not resolving. I've tried everything I can think of.

Thanks in advance for any help!

[comment]

I believe something like this should work:

GetSummary ( sTotalOfQuotes ; Quarter ) / Salespeople::Budgeted Quarterly Sales Goal )

I didn't get the part of multiplying by 100. The result in your example should be 1.03001016, which can be FORMATTED to display as 103%.

Edited October 7, 2007 by Guest

[Kent Searight]

Hi Michael,

I got it to work finally. I had originally tried using a "quarter" field to sort by that's from a related table "Quotes". When I created a field "quarter" in Projects and used your calculation it worked. Thanks!

The hardest part in creating this report is that I had originally created it in the table Quotes (since that is where the prices are), related to Projects via projectID. I had to scrap that idea because of the following:

One quote can be the winning quote, but addendum quotes can be added after the Project is won, therefore several quotes can make up the total of the winning bid.

A Project can be opened without a quote assigned to it (a work rule I've tried to change, but without success).

Because of those factors, every time a quote is imported (quotes are generated in Access), I now set the opening bid price and any winning bid prices into the fields "bidOrigPrice" and "bidWinTotal". This allows me to report from Projects.

Does all that make sense or do you think there may be a better way of handling this?

Any help is appreciated!

[comment]

Yes, unfortunately the breakField in GetSummary() must be a local field (for no good reason that I can think of, since an unstored calculation field = related::Field works perfectly well).

do you think there may be a better way of handling this?

It's a bit too complex to say, but I wonder if you couldn't find only the relevant records in Quotes before generating the report. I don't see that your report summarizes by Project, so a quote-less project would be ignored anyway, and the addendum quotes, if found, would be still included - though maybe not within the same time period.

[Kent Searight]

Yes, unfortunately the breakField in GetSummary() must be a local field (for no good reason that I can think of, since an unstored calculation field = related::Field works perfectly well).

I didn't realize that until today.

...but I wonder if you couldn't find only the relevant records in Quotes before generating the report. I don't see that your report summarizes by Project,...

There actually is a variation of the sample report that includes Projects listed under "week beginning on".

...so a quote-less project would be ignored anyway, and the addendum quotes, if found, would be still included - though maybe not within the same time period.

That's the other issue (time period). In order to generate the report per the client's specs, the grand total (winning bid + addendums) must be reported as of the date of the winning bid. As you noted, an addendum could actually have been added outside of any date range.

I'm still not sold on the idea of reporting from Projects (vs Quotes), but since it works I'll stick with it until some other method proves more beneficial.

Thanks for your help!

[gene.seeker]

Is there any way to divide a subsummary by a grandsummary when the breakfield is not local (e.g. in the table on which the layout is based)?

For example I have a report with academic years across the top and student majors in the left column. The subsummary is sorted by STUDENTS::majors. The summary fields int his part on the layout are s_yeari which is a summary based on c_yeari, which scores "1" when i="year".

The layout has a grand summary part with these fields also.

This is all working fine and is returning correct numbers, but I'd like to calculate percentages, but GetSummary seems to be not working when I try this, and I'm starting to think that it's because my breakfield is not stored in the table on which the layout is based. (GetSummary works when I use a field in the table, so the syntax is correct.)

Is there a way to make this work or should I switch this all around so the layout is based on the students table, which contains the breakfield?