Expression to loop through rows in SSRS 3

[Jordancdc]

There is no SSRS forum so I figured this is the closest thing. Anyway here is my problem.
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

 

[simian336]

There is no IIF in sql server see Case statement in BOL.

Simi

 

[Jordancdc]

I understand your statement. My first statement was that since there is no SSRS forum that I would post this in the next best area, which seemed like SQL Server forum.
Thank you.

If someone would like to move this post to a more appropriate area that is okay by me.

 

[gmmastros]

Based on the sample data... can you post the expected results?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

There is an SSRS forum

http://www.tek-tips.com/threadminder.cfm?pid=1462

MAy have better luuck there

"NOTHING is more important in a database than integrity." ESquared

 

[Jordancdc]

Thanks for taking the time to respond. 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.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.

 

[gmmastros]

Below... I show a table variable with your data hard coded in it. You should be able to copy/paste this to a query window and run it without any modifications. Once you are satisfied that it produces the correct results, you simply need to remove the table variable stuff at the top and change the query below to use your actual table and columns.

Declare	@Temp 
Table   (State VarChar(20), 
        A1 Int, A2 Int,
        A3 Int, A4 Int,
        A5 Int, A6 Int,
        A7 Int, A8 Int,
        A9 Int, A10 Int,
        A11 Int, Totalageid Int,
        Totalpopageid Int)

Insert Into @Temp Values('Alabama', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 14325.41, 11393, 79530)
Insert Into @Temp Values('Alabama', 0, 0, 0, 0, 0, 0, 0, 0, 0, 5987.25, 0, 12500, 208777)
Insert Into @Temp Values('Alabama', 0, 0, 0, 0, 0, 0, 0, 0, 2569.587, 0, 0, 8410, 327290)
Insert Into @Temp Values('Alabama', 0, 0, 0, 0, 0, 0, 0, 1178.494, 0, 0, 0, 6086, 516422)
Insert Into @Temp Values('Alabama', 0, 0, 0, 0, 0, 0, 603.8339, 0, 0, 0, 0, 4020, 665746)
Insert Into @Temp Values('Alabama', 0, 0, 0, 0, 0, 293.4552, 0, 0, 0, 0, 0, 1871, 637576)
Insert Into @Temp Values('Alabama', 0, 0, 0, 0, 175.6336, 0, 0, 0, 0, 0, 0, 1057, 601821)
Insert Into @Temp Values('Alabama', 0, 0, 0, 129.9195, 0, 0, 0, 0, 0, 0, 0, 840, 646554)
Insert Into @Temp Values('Alabama', 0, 0, 20.29428, 0, 0, 0, 0, 0, 0, 0, 0, 125, 615937)
Insert Into @Temp Values('Alabama', 0, 43.19546, 0, 0, 0, 0, 0, 0, 0, 0, 0, 103, 238451)
Insert Into @Temp Values('Alabama', 937.2025, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 571, 60926)
Insert Into @Temp Values('Alaska' , 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 12825.46, 532, 4148)
Insert Into @Temp Values('Alaska' , 0, 0, 0, 0, 0, 0, 0, 0, 0, 5154.713, 0, 718, 13929)
Insert Into @Temp Values('Alaska' , 0, 0, 0, 0, 0, 0, 0, 0, 2126.81, 0, 0, 586, 27553)
Insert Into @Temp Values('Alaska' , 0, 0, 0, 0, 0, 0, 0, 754.2974, 0, 0, 0, 502, 66552)
Insert Into @Temp Values('Alaska' , 0, 0, 0, 0, 0, 0, 394.9193, 0, 0, 0, 0, 430, 108883)
Insert Into @Temp Values('Alaska' , 0, 0, 0, 0, 0, 219.6756, 0, 0, 0, 0, 0, 217, 98782)
Insert Into @Temp Values('Alaska' , 0, 0, 0, 0, 131.8894, 0, 0, 0, 0, 0, 0, 128, 97051)
Insert Into @Temp Values('Alaska' , 0, 0, 0, 113.743, 0, 0, 0, 0, 0, 0, 0, 120, 105501)
Insert Into @Temp Values('Alaska' , 0, 0, 36.7786, 0, 0, 0, 0, 0, 0, 0, 0, 36, 97883)
Insert Into @Temp Values('Alaska' , 0, 22.69804, 0, 0, 0, 0, 0, 0, 0, 0, 0, 9, 39651)
Insert Into @Temp Values('Alaska' , 750.9882, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 76, 10120)

Select	100000.0 * Sum(Case When A1  <> 0 Then TotalAgeId End)/Sum(Case When A1  <> 0 Then Totalpopageid End) As A1,
        100000.0 * Sum(Case When A2  <> 0 Then TotalAgeId End)/Sum(Case When A2  <> 0 Then Totalpopageid End) As A2,
        100000.0 * Sum(Case When A3  <> 0 Then TotalAgeId End)/Sum(Case When A3  <> 0 Then Totalpopageid End) As A3,
        100000.0 * Sum(Case When A4  <> 0 Then TotalAgeId End)/Sum(Case When A4  <> 0 Then Totalpopageid End) As A4,
        100000.0 * Sum(Case When A5  <> 0 Then TotalAgeId End)/Sum(Case When A5  <> 0 Then Totalpopageid End) As A5,
        100000.0 * Sum(Case When A6  <> 0 Then TotalAgeId End)/Sum(Case When A6  <> 0 Then Totalpopageid End) As A6,
        100000.0 * Sum(Case When A7  <> 0 Then TotalAgeId End)/Sum(Case When A7  <> 0 Then Totalpopageid End) As A7,
        100000.0 * Sum(Case When A8  <> 0 Then TotalAgeId End)/Sum(Case When A8  <> 0 Then Totalpopageid End) As A8,
        100000.0 * Sum(Case When A9  <> 0 Then TotalAgeId End)/Sum(Case When A9  <> 0 Then Totalpopageid End) As A9,
        100000.0 * Sum(Case When A10 <> 0 Then TotalAgeId End)/Sum(Case When A10 <> 0 Then Totalpopageid End) As A10,
        100000.0 * Sum(Case When A11 <> 0 Then TotalAgeId End)/Sum(Case When A11 <> 0 Then Totalpopageid End) As A11
From	@Temp

There are a couple "tricks" that I would like to explain. First, Case/When is used here to control that data that is used.

Let's take a closer look:

If column A1 is not 0, use TotalAgeId. When A1 is 0, the case/when evaluates to NULL. NULL is ignored in the SUM aggregate.
[tt]100000.0 * Sum([!]Case When A1 <> 0 Then TotalAgeId End[/!])/Sum(Case When A1 <> 0 Then Totalpopageid End) As A1[/tt]

----------------------------------------------------

If column A1 is not 0, use TotalpopageId. When A1 is 0, the case/when evaluates to NULL. NULL is ignored in the SUM aggregate.
[tt]100000.0 * Sum(Case When A1 <> 0 Then TotalAgeId End)/Sum([!]Case When A1 <> 0 Then Totalpopageid End[/!]) As A1[/tt]

----------------------------------------------------

After the correct value is used, we sum the values and do the division. The last trick is...
[tt][!]100000.0 * [/!]Sum(Case When A1 <> 0 Then TotalAgeId End)/Sum(Case When A1 <> 0 Then Totalpopageid End) As A1[/tt]

You want to multiply by 100000, but I am multiplying by 100000.0. Why? If your columns are integers (like I am guessing they are) your SUM's will return an integer. An integer divided by an integer results in an integer. So Select 999/1000 would result in 0. To force non-integer division, you simply use a non-integer value like 100000.0.

Also, please realize that there are likely to be various ways to accomplish this. You may get different advice from others.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Jordancdc]

The query works right as expected. I was expecting to use and expression in SSRS to fill the textboxes containing the numbers. I might not have thought about how to use the query enough. You know anyway to access the method you showed me to fit into an SSRS table texbox? I appreciate the help but am I clear on how I don't understand how to apply?

Thank you:

Jordan

 

[gmmastros]

Honestly, I don't know. I don't use SSRS.

I think that returning the correct data from the database usually simplifies reporting requirements. That's why I helped you that way. I am sorry that I cannot help you any more than this.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Crowley16]

ssrs text boxes doesn't support t-sql, but now that you know the t-sql, why not setup a new dataset in your report and let the database do the calculating?

--------------------
Procrastinate Now!

 

[Jordancdc]

Understand your advice. I have a t-sql stored procedure calculating the data for the SSRS table. My trouble incorporating the new code into the existing stored procedure which seems to work except for calculation of the rate for the state totals.

Also I seem to be caught in a situation where the updated t-sql stored procedure does not update the SSRS dataset.

 

[Jordancdc]

Thanks Crowley16, sorry took so long to respond. The use of a second dataset with the method outlined earlier produced the desired results.

Not wanting my program written for me but any idea how to get an character to print if a rate numerator is less than 20? Not in the totals, but in teh State/Age intersection.
Sort of like

State   All    Under 2  2-5  6-15  16-24 
        ages 

United 
States  400     40       78   101    301

Alabama 310.1   ^      98.6  99.1  107.5

The karet in the Under 2 column indicates a rate numerator less than 20.