perform calculation for only desired rows

hi all, i wasnt quite sure where to look to answer my particular problem so i am posting up this thread in the hopes that someone can point me in the right direction. in my report, i am showing sales figures for an area. i added a table to display sales from this year, sales from last year, and then the comparable percentage('this' divided by 'last' and then minus one). to account for some ppl who didnt have sales last year, i was able to use an IIF expression to return "N/A" in the textbox. my problem is i want the compared percentage to show for the area total, but not including ppl who were an 'N/A'. i am assuming that some sort of expression will be needed for the area total row, i.e. i want the area total to sum up this year and last year and then get the percentage, but i need to filter out the individuals who didnt have data for last year.

as an example

nsty nsly percentage

total: 15 7 X

A: 5 4 25%

B: 5 3 66%

C: 5 0 N/A

the percentage for total, X, should be (10/7)-1, with p0 as the format; right now it is summing rows A, B, and C.. how can i exclude row C from the calculation?

am i on the right track by researching filters and expressions, or is this a matter for the query

thanks in advance for any help

Hi

An expression will be able to handle this fine.
Use the IIF function to only include nsty if nsly is greater than zero.
For your total % sum use something like this:

= (Sum(IIF(Fields!nsly.value > 0,Fields!nsty.value,0))/Sum(Fields!nsly.Value)) -1

Hope this helps

Cheers
Mark
|||

thanks for the response; i think i still need to expand on the expression a little bit but at least i know how to go about it now.