Expression to loop through rows

Microsoft Sql server 2008 reporting serv...
January 26, 2010 at 08:16:20
Specs: Windows XP
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.
TIA

Edit: Post moved to Database forum by Office Moderator


See More: Expression to loop through rows

Report •

#1
February 4, 2010 at 10:20:02
Below I repeated the table and after that I post the results that I expected.
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

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.
35-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.

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


Report •
Related Solutions


Ask Question