Calculate the rate of change in SSRS

Question

I need to calculate the rate of change in drug usage between two dates using SSRS. I am used to using SQL, therefore I am having a difficult time with SSRS.

I have the following information:

                             Avg_Alcohol_Use_Month   Avg_Drug_Use_Month 
First_interview_Date              1.63%                    1.34%
      1/30/2017

Followup_interview_date           2.58%                     .80%
      6/30/2017

How do I create a report that reflects the rate of change in drug usage between two dates? I need to create the report in SSRS but, I don't know how to write a query in SSRS that will reflect the rate of change.

I cannot create the query in SQL because I only have access to the data through SSRS.

Answer 1

(This example is for SQL Server)

You can do it in SQL if you save the initial results in a table or a temp data structure. If you subquery, you can subtract the line's rates by the previous line's rate. This is by date, so you chose the MAX(date) for the given patient/doctor, whatever that (Primary Key?) is. In this case I have used "PatientID" to identify the patient. See below:

--Assume your values are saved in a table or other temp table
DECLARE @tmp TABLE (PatientID int, Interview_Date date, Avg_Alcohol_Use_Month decimal (4,2), Avg_Drug_Use_Month decimal (4,2))
INSERT INTO @tmp 
VALUES
 (1, '2017-01-30', 1.63, 1.34)
,(2, '2017-06-30', 2.58, 0.80)
,(1, '2017-03-01', 1.54, 1.23)
,(1, '2017-07-02', 3.21, 0.20)
,(2, '2017-08-23', 2.10, 4.52)

SELECT PatientID
      ,Interview_Date
      ,Avg_Alcohol_Use_Month
      ,Avg_Drug_Use_Month
      ,Avg_Alcohol_Use_Month
       -
       (SELECT Avg_Alcohol_Use_Month
          FROM @tmp T2
         WHERE T2.PatientID = T1.PatientID
           AND T2.Interview_Date = (SELECT MAX(Interview_Date) 
                                      FROM @tmp T3
                                     WHERE T3.Interview_Date < T1.Interview_Date
                                       AND T3.PatientID = T1.PatientID
                                       -- or whatever PK that makes the row unique for the patient.
                                   )
       ) AS [Alcohol Use Rate change]
      ,Avg_Drug_Use_Month
       -
       (SELECT Avg_Drug_Use_Month
          FROM @tmp T2
         WHERE T2.PatientID = T1.PatientID
           AND T2.Interview_Date = (SELECT MAX(Interview_Date) 
                                      FROM @tmp T3
                                     WHERE T3.Interview_Date < T1.Interview_Date
                                       AND T3.PatientID = T1.PatientID
                                       -- or whatever PK makes the row unique for the patient.
                                   )
       ) AS [Drug Use Rate change]
  FROM @tmp T1
ORDER BY PatientID, Interview_Date

Use such a query as the dataset for SSRS.