none
SSRS IIF EXPRESSION TO FILTER DATA BASED ON PARAMATER

    Question

  • I'M CURRENTLY WORKING ON A SSRS REPORT AND AM NEW TO SSRS.  THE REPORT IS A UNIT COST REPORT WHERE I NEED ACTUAL UNITS THROUGH THE CURRENT DATE BUT THEN ALSO NEED ACTUAL UNITS THROUGH A SPECIFIC PERIOD BASED ON TWO PARAMATERS (BEG DATE) AND (END DATE).  BOTH THE ACTUAL UNITS TO DATE AND ACTUAL UNITS THROUGH PERIOD WILL NEED REPRESENTED AT THE SAME TIME IN TWO SEPARATE COLUMNS ON THE REPORT (EXAMPLE BELOW). 

    JOB | ACTUAL UNITS TO DATE |  ACTUAL UNITS THRU PERIOD |

    2882|...................600....................|........................200........................|

    I'VE SETUP TWO REPORT PARAMATERS (BEG DATE) AND (END DATE) THAT I WAS HOPING TO REFERENCE IN AN IIF EXPRESSION TO OBTAIN THE ACTUAL UNITS THRU PERIOD.  THE EXPRESSION I HAVE COME UP WITH IS LISTED BELOW.

    IIF(Fields!ActualDate.Value)>Parameters!BegDate.Value,IIF(Fields!ActualDate.Value<ParametersEndDate.Value,Sum(Fields!ActualUnits.Value),0),0)  
    

    WHEN INSERTING THIS EXPRESSSION IT IS NOT TOTALING THE ACTUAL UNITS FOR THE DATE RANGE SELECTED IN THE PARAMATERS CORRECLY.  I'M NOT SURE WHAT I'M MISSING SO ANY HELP WILL BE GREALTY APPRICATED.  I'M ALSO NOT SURE IF THIS WOULD BE THE BEST METHOD TO OBTAIN THIS DATA SO ANY SUGGESTIONS WILL ALSO BE GREATLY APPRICATED.

    THANKS

    Wednesday, May 30, 2012 1:25 PM

Answers

  • Hi

    Just a thought, perhaps you want to also include the current day in your values? maybe changing the > and < to >= and <= may give you the correct answer? so:

    =IIF(	Fields!ActualDate.Value >= Parameters!BegDate.Value and Fields!ActualDate.Value <= ParametersEndDate.Value
    	,	Sum(Fields!ActualUnits.Value)
    	,	0
    	) 

    Also, have you set your location in your report? i.e. if you are in the UK and report set to US for example, you may be getting funny date convo errors and hence the date range wont be as expected (have a look at the parameter value by placing the parameter value in a textbox to see how it is formatted).

    In your dataset query you may wish to set the dateformat by writing the following code at the beginning of your query:

    SET DATEFORMAT YMD 

    or whatever format suites!

    Shaheen

    Wednesday, May 30, 2012 5:08 PM

All replies

  • Hi,

    Give a try to this one

    =Sum(IIF(Fields!ActualDate.Value>Parameters!BegDate.Value and Fields!ActualDate.Value<ParametersEndDate.Value),Fields!ActualUnits.Value,0)


    Hope this will help you !!!
    Sanjeewan


    Wednesday, May 30, 2012 1:30 PM
  • Thanks for the response Sanjeewan.  I tried the expression and for some reason its giving me a #Error when previewing the report.  Any Thoughts? 

    RYAN PRICE

    Wednesday, May 30, 2012 1:53 PM
  • Hi,

    try this

    =IIF(Fields!ActualDate.Value>Parameters!BegDate.Value and Fields!ActualDate.Value<ParametersEndDate.Value),Sum(Fields!ActualUnits.Value),0)



    Hope this will help you !!!
    Sanjeewan

    Wednesday, May 30, 2012 2:01 PM
  • Thanks again Sajeewan.  This expression is working but it seems to not be summing the data correcly based on the date paramaters.  It looks like the sum fuction is not properly summing the total data that is between the date range paramaters.  Any other ideas on this will be greatly appriciated.

    RYAN PRICE

    Wednesday, May 30, 2012 3:38 PM
  • Hi

    Will you post screen shot how its Summing the value



    Hope this will help you !!!
    Sanjeewan

    Wednesday, May 30, 2012 3:50 PM
  • Hi

    Just a thought, perhaps you want to also include the current day in your values? maybe changing the > and < to >= and <= may give you the correct answer? so:

    =IIF(	Fields!ActualDate.Value >= Parameters!BegDate.Value and Fields!ActualDate.Value <= ParametersEndDate.Value
    	,	Sum(Fields!ActualUnits.Value)
    	,	0
    	) 

    Also, have you set your location in your report? i.e. if you are in the UK and report set to US for example, you may be getting funny date convo errors and hence the date range wont be as expected (have a look at the parameter value by placing the parameter value in a textbox to see how it is formatted).

    In your dataset query you may wish to set the dateformat by writing the following code at the beginning of your query:

    SET DATEFORMAT YMD 

    or whatever format suites!

    Shaheen

    Wednesday, May 30, 2012 5:08 PM