What expressions should I use in a Month on Month change report? (SSRS)

Question

I have a report that is set up as below. I'm using row groups of day of month (which I intend to make invisible in the final draft) to compare the 1st of this month with the first of last month and so on.

When I run it it looks like this

Which is perfect. However what I need is to fill in the blank row on each day with the bottom row minus the top row in order to get the change from month to month and I've no idea how to achieve that or if it's even possible. I assume if it is possible it will be via the use of expressions but other than the simple ones in this report

eg Calls answered % =Fields!Calls_Answered.Value/Fields!Calls_Offered.Value

I'm in a bit beyond my depth.

It might be an issue that is best solved at the SQL level rather than the SSRS level. If it's useful the SQL query I use is this.

SELECT
CONVERT(DATE,FORMAT([Start Time],'dd/MM/yyyy'),103) AS [Date]
,[Client Name]
,[Account]
,SUM(IIF([Type] in ('Normal operator call','Caller rang off','Caller  rang off during divert','No suitable operator logged on'),1,0)) AS [Calls Offered]
,SUM(IIF([Type] in ('Normal operator call'),1,0)) AS [Calls Answered]
,SUM(IIF([Type] in ('Caller rang off','Caller rang off during  divert','No suitable operator logged on'),1,0)) AS [Ring Offs]
,SUM(IIF([Ring (secs)] <= 20 AND [Type] in ('Normal operator  call'),1,0)) AS [Answered in 20 Secs]
,SUM(IIF([Ring (secs)] > 5 AND [Type] in ('Caller rang off','Caller  rang off during divert','No suitable operator logged on'),1,0)) AS [CRO After 5 Secs]
,AVG(IIF([Type] in ('Normal operator call'),[Ring (secs)],null)) AS 'Avg Time to Answer'
,AVG(IIF([Type] in ('Normal operator call'),[Connected (secs)],null))  AS 'Avg Call Time'
FROM InboundCallsView    
WHERE [Client Name] = (@Client) and [Start Time] >= DATEADD(m,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()), 0))    
GROUP BY FORMAT([Start Time],'dd/MM/yyyy'),[client name],[account]    
ORDER BY [Client Name],[date]

=Day(Fields!Date.Value) ie the number of the day as given by my [Date] field — tomdemaine

Tab Alleman Tab Alleman · Accepted Answer · 2016-06-23T14:15:04

You could do a SUM() with an IIF() function that multiplies the measure * -1 if it meets the condition that would put it in the top row.

In pseudo-code it would look something like this, using [Calls Answered] as an example:

SUM(IIF({This is the top row}, -1 * [Calls Answered], [Calls Answered]))

I don't know all the possible parameters of your report, so I don't know specifically how you will determine whether a row would be the top. One way with your sample results would be to check if the DATEDIFF in months (between [Date] and GETDATE()) is zero or not.

What expressions should I use in a Month on Month change report? (SSRS)

1 Answers