Solved

Help with filter Expression in SSRS

Posted on 2012-04-03
12
496 Views
Last Modified: 2012-05-16
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
0
Comment
Question by:gotti777
  • 6
  • 6
12 Comments
 
LVL 11

Expert Comment

by:SThaya
ID: 37800785
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
 

Author Comment

by:gotti777
ID: 37800823
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
 
LVL 11

Expert Comment

by:SThaya
ID: 37800849
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:gotti777
ID: 37800877
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
 
LVL 11

Expert Comment

by:SThaya
ID: 37800931
hi ,

  please check the below expression .

=iif(Fields!SDyear.Value=year(today()) ,Fields!Sales.Value/Fields!Forcastsalesmonth.Value, "")
0
 

Author Comment

by:gotti777
ID: 37801827
Hmm I get no errors but,  its returns no data.  Its blank
t
0
 
LVL 11

Assisted Solution

by:SThaya
SThaya earned 500 total points
ID: 37801879
can u send some data in the data set
0
 

Author Comment

by:gotti777
ID: 37801920
Not sure how .. or what you mean  Data set is connect to my MRP system.   Want me to pull out in Excel format?
0
 
LVL 11

Assisted Solution

by:SThaya
SThaya earned 500 total points
ID: 37801930
=iif(Fields!SDyear.Value<>year(today()),"",iif(sum(Fields!Forcastsalesmonth.Value)>0,Fields!Sales.Value/Fields!Forcastsalesmonth.Value, 0))
0
 

Author Comment

by:gotti777
ID: 37802113
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
 
LVL 11

Accepted Solution

by:
SThaya earned 500 total points
ID: 37803050
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
 

Author Comment

by:gotti777
ID: 37803436
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

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question