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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
SThayaConnect With a Mentor Technical 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 "
0
 
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,  ........)
0
 
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
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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"
0
 
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
0
 
SThayaTechnical MAnagerCommented:
hi ,

  please check the below expression .

=iif(Fields!SDyear.Value=year(today()) ,Fields!Sales.Value/Fields!Forcastsalesmonth.Value, "")
0
 
gotti777Author Commented:
Hmm I get no errors but,  its returns no data.  Its blank
t
0
 
SThayaConnect With a Mentor Technical MAnagerCommented:
can u send some data in the data set
0
 
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?
0
 
SThayaConnect With a Mentor Technical MAnagerCommented:
=iif(Fields!SDyear.Value<>year(today()),"",iif(sum(Fields!Forcastsalesmonth.Value)>0,Fields!Sales.Value/Fields!Forcastsalesmonth.Value, 0))
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))
0
 
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
0
All Courses

From novice to tech pro — start learning today.