Expression to loop rows

[Jordancdc]

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.

 

[Jordancdc]

Something to add. This report has been moved to SSRS 2008 from SSRS 2005. For some reason it used to work but isn't anymore. Its wrong, but at least worked in SSRS 2005. The actual error displays

"An error occurred during local report proecessing.
An error has occurred during report processing.
Query execution failed for dataset "DataSet2"
A severe eror occurred on the current command. The results, if any, should be discarded. Operation cancelled by user."

The stored procedure hasn't changed and still works in T-SQL. But the report errors out.

TIA

 

[Jordancdc]

I recoded my SSRS report and it works now as before. I still am having trouble inserting the code I was given into T-SQL stored procedure. I've gotten the stored procedure to compile and output the proper data but i haven't figured out how to get the right arrangement to get it into the proper textbox.

 

[Jordancdc]

I can get the rate per 100,000 population into a variable but how do I get the variable into my SSRS report? If the dataset is queried it returns just teh variable and not the rest of the fields in the table.

 

[Nesy123456789]

You can add an additional field to be displayed by your stored procedure which does the calculation then add this field to the table as an ordinary column.
The "over partition" function should help you here.

e.g in proc:
Select state, ((Sum(age)/(Sum(age)))* 100000) over (partition by state) as stateAge, ((Sum(age)/(Sum(age)))* 100000) over (partition by state,AgeRange) as stateAgebyRange.

If you don't have the AgeRange as a field you can add CASE statements into the function.