Gabe Lebron
asked on
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!Forcastsalesm onth.Value
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!Forcastsalesm
ASKER
Not sure what you mean. I have no else .. This is all i need
=iif(Fields!SDyear.Value=2 012 ,Fields!Sales.Value/Fields !Forcastsa lesmonth.V alue
what if i dont have an else
=iif(Fields!SDyear.Value=2
what if i dont have an else
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 !Forcastsa lesmonth.V alue"
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
ASKER
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
hi ,
please check the below expression .
=iif(Fields!SDyear.Value=y ear(today( )) ,Fields!Sales.Value/Fields !Forcastsa lesmonth.V alue, "")
please check the below expression .
=iif(Fields!SDyear.Value=y
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Not sure how .. or what you mean Data set is connect to my MRP system. Want me to pull out in Excel format?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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(Fi elds!Forca stsalesmon th.Value)> 0,Fields!S ales.Value /Fields!Fo rcastsales month.Valu e, 0))
=iif(Fields!SDYEAR.Value<>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
refer the below expression and fill the your own formula in the else part..
=iif(Fields!SDyear.Value=2