Solved

Help with filter Expression in SSRS

Posted on 2012-04-03
12
494 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
 

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VB6 ListBox Question 4 30
Optimizing a query 3 33
SQL 2008 R2 calc date formula 3 23
Error running stored procedure 11 14
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

914 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now