Calculating percentage of total from Running Total field

[Former Member]

This has probably been answered before, but I couldn't find anything.

I have a report that shows insurance policies sold by amount grouped by Market and by Region. Here is an example of what I have so far:

Market________Amount_____% of total

Bay Area_____1,985,289______100.0

Central_______1,948,872_______49.5

Inland________3,743,975_______48.4

North________ 1,014,230_______11.7

Orange Co____1,548,956_______15.1

San Diego____2,903,433________22.1

Ventura______1,192,828________8.3

CA Region___14,337,583

My CA Region is a Running Total field of the Amount field and my percent just takes the Amount and divides it by the CA Region Total.

As you can see the % does not calculate correctly. I know that it is because of the way CR calculates data on it's 3 passes.

What is the best way to make this work?

[vitaly_izmaylov · Accepted solution]

You are right about evaluation passes.

You are doing your whole calculation in WhileReadingRecords and it calculates percentage as following

1,985,289 of 1,985,289 = 100%

1,948,872 of ( 1,985,289 +1,948,872) = 49.5%

3,743,975 of (3,743,975+ 1,985,289 +1,948,872) = 48.4

etc.

I mean the calculation is correct, but not what you need

You need to calculate percentage in WhilePrintingRecords. Create a formula:

WhilePrintingRecords;

({your Amount field or formula} / {CA Region RunningTotal})*100

then add this formula to details section

[Former Member · Accepted solution]

OK, I tried this but I still get the same result. One thing I didn't mension is that I'm not using the details section, I'm using a Group footer. Will that make a difference?

[vitaly_izmaylov · Accepted solution]

Can you use a summary instead of Running Total?

Summarize the Amount field and use this summary for percentage calculation

[Former Member · Accepted solution]

I could use a summary field except my running total is summing only certain records based on date. My report compares year over year production and my source data contains the production amounts for both years in one column. I may try to go back to my source data and try to separate the two periods I'm compring so I don't have to filter in CR.