Calculating dollar figure by various percentages within a report.

[Lanie]

I am a little lost here and not sure where to start and sure could use your help. <br>
<br>
I have this commission report created and functioning well, now the client needs to have an addition field added that will calculation a percentage of the total and display that amount on the report. Which I can do if it were a consitant percentage. The problem is that the percent of total various with each sales rep. In other works RP1 = 30%, RP2 = 50% etc. <br>
<br>
The report currently queries on RP and Start and then End date. <br>
<br>
I was going to set up separate reports for each Sales Person and do the calculation in the report, but that leads to further problems down the road...for example how does it get handled when adding new reps?<br>
<br>
I would appreciate all suggestions. Thanks Lanie<br>
<br>
<p>Lanie<br><a href=mailto:etroidl@conaxfl.com>etroidl@conaxfl.com</a><br><a href= > </a><br>

 

[elizabeth]

Create a new field SalesPercent in your tblSalesRep. Then you can use it in your underlying query.

 

[Lanie]

Thanks Elizabeth, I have done that, but not sure how to have it calculate unless I set up identical reports for each rep.<br>
<br>
Is there a way for me to get an if rep=b then % is 30% of the calculated amount? Or something like that. This way I could use one report for the various reps.<br>
<br>
You can sure tell that I have only a very basic knowledge of hard coding.<br>
<br>
<br>
<br>
<br>
<br>
<br>

 

[elizabeth]

Relax, you don't need any code. Do the calc in the query. Create a new column in your query grid, by keying something like this where the field name usually goes:<br>
CommisionAmt: [SalesAmt] * [CommissionPercent]<br>
This creates a new result column in your underlying query called CommissionAmount. With your report in design view, use the FieldList tool on the toolbar to open your filedList, and Drag & Drop the new field CommissionAmt onto your report. You can then use this same report for all your Reps.<br>
<br>

 

[Lanie]

Thanks elizabeth. I guess I freaked at first....I added a percentage field to the table and the query and then added the field to the report and did an unbound with the calculated of the CommTotal * the percentage. Made the percentage field invisible. <br>
<br>
It works great and runs fast.<br>
<br>
I don't know if this is a &quot;clean&quot; way of doing it, but it works.... <p>Lanie<br><a href=mailto:etroidl@conaxfl.com>etroidl@conaxfl.com</a><br><a href= > </a><br>

 

[elizabeth]

You're welcome I don't know whether it's faster to do a calc in a query or a report. If it's the same, the only advantage of putting the calc into the query is that you are able to avoid adding the one invisible control.