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

Anonymous
2023-05-18T21:39:49.1866667+00:00

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

User's image

User's image

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,869 questions
{count} votes

1 answer

Sort by: Most helpful
  1. AniyaTang-MSFT 12,421 Reputation points Microsoft Vendor
    2023-05-19T01:50:21.9666667+00:00

    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.

    1

    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

    0 comments No comments