I am creating a report in SSRS which calculates and compares different values for each customer. I have set two date parameters, FROM and TO using the only date field (Fields!pday.Value!)

I want all paid amounts (=SUM(Fields!memrepay.mprinc.Value) by "Customer-1" in selected date range (this field has to be calculated in date range) in Textbox1; but also, all paid amounts done from the beginning of his membership up selected TO parameter in TextBox2, which is the same (=SUM(Fields!memrepay.mprinc.Value). It makes sense that report sum up for that range in both text boxes - not from beginning for just TestBox2.

Is it possible to skip the selected "FROM" date for expression in Textbox2 and allow it to sum all paid amounts, from beginning up to selected "TO" parameter?

In other way, I need only TextBox2 to sum all amounts from 1/1/2005 (beginning of payment table) up to selected TO parameter.

Thanks!

• Edited by Hamed Da 18 hours 58 minutes ago more accurate information

Hi Hamed ,

Try the below expression

=SUM(IIF(CDate(Fields!DateColumn.Value) >= "01/01/2013" AND CDate(Fields!DateColumn.Value) <=Parameters!ToDateParameter.Value, Fields!paid.Value, 0),"DatasetName")

Thanks Milan, but it didn't work. It is because we have only one date field that I have used it for FROM and TO parameter, and I need this date range for other data. 

I have tried different expressions similar to yours, none worked. I don't know how, but I should define the start date in expression OR find a way to allow it skip the selected FROM parameter, than it will SUM up all to selected TO parameter.

Any suggestion(s)?

Hi Hamed

I tested it on my local environment and it worked successfully.

Can you please post your sample dataset and along with some screenshots about your requirement.

Here's the query: 

SELECT
  memrepay.lnr
  ,memrepay.pday
  ,memrepay.tcode
  ,memdues.dday
  ,memrepay.mprinc AS [memrepay mprinc]
  ,memrepay.mint AS [memrepay mint]
  ,memdues.mprinc AS [memdues mprinc]
  ,memdues.mint AS [memdues mint]

FROM
  memdues
  INNER JOIN memrepay
    ON memdues.lnr = memrepay.lnr AND memdues.memid = memrepay.memid
  INNER JOIN loan
    ON loan.lnr = memrepay.lnr AND loan.memid = memrepay.memid
WHERE
  memdues.dday >= @dday
  AND memdues.dday <= @dday2
  AND memrepay.pday >= @pday
  AND memrepay.pday <= @pday2

And Here's the sample screenshot:

As you can see, this customer has paid 1667 this month on 5/28/2015 as my date parameters are between 5/1/2015 and 5/31/2015, while I also need to have Total paid up to now from an unknown date, but Total is also the same as monthly paid amount.

memdues.dday >= @dday
  AND memdues.dday <= @dday2
  AND memrepay.pday >= @pday
  AND memrepay.pday <= @pday2
 INSTEAD OF ABOVE FILTER USE BETWEEN OPERATOR e.g
SELECT * FROM Tabblename WHERE dateBETWEEN '2005-01-01' AND @YourDateParamter