How do I calculate the sum of Adjusted Amount for the previous month using a parameter

Question

I have a summary report and I need an SSRS expression to give me the previous amount based on a parameter the user is putting in to pull data. I have tried =SUM( IIF( Fields!TotalAdjusted.Value >= DateAdd(DateInterval.Month, -1, Parameters!ReportDate.Value) AND Fields!dtMonth.Value < Parameters!ReportDate.Value, Fields!TotalAdjusted.Value, 0 ) ) but it gives same value no matter what store i enter. As you can see in the image the previous month should not have the same numbers for each property.

The original query for the report is

Declare @Prop as int = 8005;

Declare @ReportDate as Date = '2023-03-30';

Select q.strAgeing,

   q.[A/R Rent],

      q.[A/R Fees],

      q.[Total A/R],

      q.[Percent A/R],

     

      qrySTARSAR.dtMonth,

      qrySTARSAR.mnyDelqRent as STARSARDelqRent,

      qrySTARSAR.mnyDelqRentMil as STARSARDelqMilRent,

      qrySTARSAR.mnyRentOnOcc as STARSARRentOnOcc,

       qrySTARSAR.TotalAdjusted,

       qrySTARSAR.TotalAdjusted /qrySTARSAR.mnyRentOnOcc as 'Percentage Adjusted'

FROM (

Select

'11-30' as strAgeing,

SUM(mnyRent0to30) 'A/R Rent',

Sum(mnyFees0to30) 'A/R Fees',

SUM(mnyRent0to30) + Sum(mnyFees0to30) 'Total A/R',

Sum(mnyRent0to30) / CAST(Sum(mnyOccupiedRent) as float) * 100 as 'Percent A/R',

1 as intOrderCtrit

FROM tblUBMReport

LEFT JOIN tblUBMInsuranceShare

on tblUBMInsuranceShare.intProp = tblUBMReport.intProp

Where tblUBMReport.intProp IN (@Prop)

and dtReport = @ReportDate

UNION

SELECT

'31-60' as strAgeing,

SUM(mnyRent31to60) 'A/R Rent',

Sum(mnyFees31to60) 'A/R Fees',

Sum(mnyRent31to60) + Sum(mnyFees31to60) 'Total A/R',

Sum(mnyRent31to60) / CAST(Sum(mnyOccupiedRent) as float) * 100 as 'Percent A/R',

2 as intOrderCrit

FROM tblUBMReport

LEFT JOIN tblUBMInsuranceShare

on tblUBMInsuranceShare.intProp = tblUBMReport.intProp

Where tblUBMReport.intProp IN (@Prop)

and dtReport = @ReportDate

UNION

SELECT

'61-90' as strAgeing,

SUM(mnyRent61to90) 'A/R Rent',

Sum(mnyFees61to90) 'A/R Fees',

Sum(mnyRent61to90) + Sum(mnyFees61to90) 'Total A/R',

Sum(mnyRent61to90) / CAST(Sum(mnyOccupiedRent) as float) * 100 as 'Percent A/R',

3 as intOrderCrit

FROM tblUBMReport

LEFT JOIN tblUBMInsuranceShare

on tblUBMInsuranceShare.intProp = tblUBMReport.intProp

Where tblUBMReport.intProp IN (@Prop)

and dtReport = @ReportDate

UNION

SELECT

'Over 90' as strAgeing,

SUM(mnyRent90Plus) 'A/R Rent',

Sum(mnyFees90Plus) 'A/R Fees',

Sum (mnyRent90Plus) + Sum(mnyFees90Plus) 'Total A/R',

Sum(mnyRent90Plus) / CAST(Sum(mnyOccupiedRent) as float) * 100 as 'Percent A/R',

4 as intOrderCrit

FROM tblUBMReport

LEFT JOIN tblUBMInsuranceShare

on tblUBMInsuranceShare.intProp = tblUBMReport.intProp

Where tblUBMReport.intProp IN (@Prop)

and dtReport = @ReportDate) q

OUTER APPLY (

SELECT TOP 1 

    SUM(mnyDelqRent) AS mnyDelqRent, 

    SUM(mnyDelqRentMil) AS mnyDelqRentMil, 

    SUM(mnyRentOnOcc) AS mnyRentOnOcc,

    MAX(dtMonth) AS dtMonth,

          SUM(mnyDelqRent)- SUM(mnyDelqRentMil) as TotalAdjusted

FROM tblSTARSAR 

WHERE intProp = intProp 

GROUP BY intProp, dtMonth

ORDER BY intProp, dtMonth DESC

)qrySTARSAR

Order By intOrderCtrit

Answer 1

Hi @Jannette Jones

I'm not particularly clear about your detailed design. According to your expression, it seems that you want to calculate the total of the eligible TotalAdjusted.

But you're comparing Fields!TotalAdjusted.Value with the time value, which is a bit problematic.

If you want to define a time range, it should be more reasonable to use an expression like the following.

=SUM( IIF( Fields!dtMonth.Value >= DateAdd(DateInterval.Month, -1, Parameters!ReportDate.Value) AND Fields!dtMonth.Value < Parameters!ReportDate.Value, Fields!TotalAdjusted.Value, 0 ) )

Of course, I am not particularly clear about the date format of your dtMonth, you may need to modify it.

Best regards,

Aniya