Help with filter Expression in SSRS

I am running a matrix for sales on last 3 years and attached a forcast for current year.
now then i try and create a % to forcast since its a martix it adds up all 3 years.  I have the years broken up by SDYEAR how can i make an expression that just  uses year 2012

i think i have the logic..  just not sure how to wright it
=Fields!Sales.Value(where SDyear =201)/Fields!Forcastsalesmonth.Value
 
1
gotti777Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SThayaTechnical MAnagerCommented:
hi
 refer the below expression and fill the your own formula in the else part..





=iif(Fields!SDyear.Value=201 ,Fields!Sales.Value/Fields!Forcastsalesmonth.Value,  ........)
gotti777Author Commented:
Not sure what you mean.   I have no else ..  This is all i need

=iif(Fields!SDyear.Value=2012 ,Fields!Sales.Value/Fields!Forcastsalesmonth.Value
what if i dont have an else
SThayaTechnical MAnagerCommented:
Hi ,

  if suppose in your data set (Fields!SDyear.Value)  has 2011 data then what is ur formula for %to forecast ......?

> if the dataset only contains current year data , then no need for iif...... etc directly u can include the formula "Fields!Sales.Value/Fields!Forcastsalesmonth.Value"
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

gotti777Author Commented:
Yes my data has 2011 and 2010.  your right thats why i need the filter.  My forcast is for currewnt year 2012..  I only want it to take the sales data for (SDYEAR) 2012 and devide by Forcastsalesmonth.  2010 and 2011 i want to go nothing with
SThayaTechnical MAnagerCommented:
hi ,

  please check the below expression .

=iif(Fields!SDyear.Value=year(today()) ,Fields!Sales.Value/Fields!Forcastsalesmonth.Value, "")
gotti777Author Commented:
Hmm I get no errors but,  its returns no data.  Its blank
t
SThayaTechnical MAnagerCommented:
can u send some data in the data set
gotti777Author Commented:
Not sure how .. or what you mean  Data set is connect to my MRP system.   Want me to pull out in Excel format?
SThayaTechnical MAnagerCommented:
=iif(Fields!SDyear.Value<>year(today()),"",iif(sum(Fields!Forcastsalesmonth.Value)>0,Fields!Sales.Value/Fields!Forcastsalesmonth.Value, 0))
gotti777Author Commented:
Hmmm same deal,  Expression has no error but returns empty fields  I even hard codeed the data and same result


=iif(Fields!SDYEAR.Value<>(2012),"",iif(sum(Fields!Forcastsalesmonth.Value)>0,Fields!Sales.Value/Fields!Forcastsalesmonth.Value, 0))
SThayaTechnical MAnagerCommented:
Hi,

  clear one thing ..

1. if your record contains 2012 data then the formula sholud be "Fields!Sales.Value/Fields!Forcastsalesmonth.Value"
now we got the above .
2. my question is if 2011 data is avilable inyour report then what data you need to show in the column "% to forcast "

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gotti777Author Commented:
Yes I have a matrix, that shows all of 2011 and 2012 todate like this  
with seprate data base with forcast linked now forcast is just for current year but when i try and do a sale/ forcast sales to get percent to where i am .. it combines 2011 and 2012

12
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.