Have a dataset in SSRS in table format by State and AgeGroup. The table has State as rows and AgeGroup as columns. For each row there are zeros in all teh columns except the column containing the particular AgeGroup and the last 2 columns which are totalageid and totalpopageid. I want to do write this IIF expression.
=IIF((Fields!Under_1_year_2_.Value)<> 0 ,(Sum(Fields!Totalageid.Value) /(Sum (Fields!Totalpopageid.Value) * 100000),0)
So the table looks like this
Alabama 0 0 0 0 0 0 0 0 0 0 14325.41 11393 79530
Alabama 0 0 0 0 0 0 0 0 0 5987.25 0 12500 208777
Alabama 0 0 0 0 0 0 0 0 2569.587 0 0 8410 327290
Alabama 0 0 0 0 0 0 0 1178.494 0 0 0 6086 516422
Alabama 0 0 0 0 0 0 603.8339 0 0 0 0 4020 665746
Alabama 0 0 0 0 0 293.4552 0 0 0 0 0 1871 637576
Alabama 0 0 0 0 175.6336 0 0 0 0 0 0 1057 601821
Alabama 0 0 0 129.9195 0 0 0 0 0 0 0 840 646554
Alabama 0 0 20.29428 0 0 0 0 0 0 0 0 125 615937
Alabama 0 43.19546 0 0 0 0 0 0 0 0 0 103 238451
Alabama 937.2025 0 0 0 0 0 0 0 0 0 0 571 60926
Alaska 0 0 0 0 0 0 0 0 0 0 12825.46 532 4148
Alaska 0 0 0 0 0 0 0 0 0 5154.713 0 718 13929
Alaska 0 0 0 0 0 0 0 0 2126.81 0 0 586 27553
Alaska 0 0 0 0 0 0 0 754.2974 0 0 0 502 66552
Alaska 0 0 0 0 0 0 394.9193 0 0 0 0 430 108883
Alaska 0 0 0 0 0 219.6756 0 0 0 0 0 217 98782
Alaska 0 0 0 0 131.8894 0 0 0 0 0 0 128 97051
Alaska 0 0 0 113.743 0 0 0 0 0 0 0 120 105501
Alaska 0 0 36.7786 0 0 0 0 0 0 0 0 36 97883
Alaska 0 22.69804 0 0 0 0 0 0 0 0 0 9 39651
Alaska 750.9882 0 0 0 0 0 0 0 0 0 0 76 10120
The result of the expression should be the rate per 100,000 for all the states with that particular age group.
Also...
Based on the table above there should be 11 age groups. The rate per 100,000 population should be for each age group and is based on dividing the sum of the age group frequencies by the sum of the age group populations and multiplying by 100,000. The age group frequency is the next to last column and is only for the age group that has a non-zero value in the row. The age group population is the last column and is only for the age group that has a non-zero value in the row.
The table above is only for 2 states. I did the rate calculations by hand so that you could see how they are solved.
< 1 910.7
1-4 40.3
5-14 22.6
15-24 127.6
25-34 169.5
35-44 173.0
45-54 574.5
55-64 1130.0
65-74 2535.2
75-84 5935.2
85+ 14251.0
Thanks for taking the time to advise me on how to work with SSRS for calculating a total for a table iwth rate calculations.
I have already done the calculation for each state total rate per 100,000. That was =(Sum(Fields!Totalageid.Value )/(Sum(Fields!Totalpopageid.Value)))* 100000.