September 8, 2014 at 8:57 am
Looking for year over year calculation to show how much improvement over same time last year.
Eg.
Add totals for Jan-Aug (most recent full month)
divide by total for Jan-Aug (last year).
the expression needs to be dynamic so that after Sep is passed, it will show one additional month's totals in the numerator and denominator.
Does someone know what the expression for this would be?
September 8, 2014 at 11:03 am
I can't speak for anyone else, but I'd have to know what your data set looks like in order to hazard a guess.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 8, 2014 at 6:10 pm
DDL
USE [Sandbox]
GO
CREATE TABLE [dbo].[Example1](
[Division] [varchar](20) NULL,
[activity_date] [date] NULL,
[SentLeads] [int] NULL,
[Referrals] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Example1] ([Division], [activity_date], [SentLeads], [Referrals])
VALUES (N'Central', CAST(0x2E350B00 AS Date), 13, 1),
(N'Central', CAST(0x34350B00 AS Date), 12, 1),
(N'Central', CAST(0x3A350B00 AS Date), 11, 2),
(N'Central', CAST(0x46350B00 AS Date), 20, 3),
(N'Central', CAST(0x4A350B00 AS Date), 10, 1),
(N'Central', CAST(0x58350B00 AS Date), 12, 0),
(N'Central', CAST(0x67350B00 AS Date), 23, 2),
(N'Central', CAST(0x76350B00 AS Date), 18, 1),
(N'Central', CAST(0x77350B00 AS Date), 18, 0),
(N'East', CAST(0x2E350B00 AS Date), 17, 1),
(N'East', CAST(0x34350B00 AS Date), 21, 1),
(N'East', CAST(0x3A350B00 AS Date), 13, 2),
(N'East', CAST(0x46350B00 AS Date), 25, 3),
(N'East', CAST(0x4A350B00 AS Date), 26, 2),
(N'East', CAST(0x58350B00 AS Date), 30, 0),
(N'East', CAST(0x67350B00 AS Date), 27, 1),
(N'East', CAST(0x76350B00 AS Date), 21, 1),
(N'East', CAST(0x77350B00 AS Date), 17, 0),
(N'Central', CAST(0x9C360B00 AS Date), 15, 2),
(N'Central', CAST(0xA2360B00 AS Date), 14, 1),
(N'Central', CAST(0xA8360B00 AS Date), 13, 3),
(N'Central', CAST(0xB4360B00 AS Date), 21, 4),
(N'Central', CAST(0xB8360B00 AS Date), 12, 2),
(N'Central', CAST(0xC6360B00 AS Date), 15, 1),
(N'Central', CAST(0xD4360B00 AS Date), 25, 3),
(N'Central', CAST(0xE3360B00 AS Date), 20, 2),
(N'Central', CAST(0xE4360B00 AS Date), 19, 1),
(N'East', CAST(0x9C360B00 AS Date), 20, 2),
(N'East', CAST(0xA2360B00 AS Date), 23, 1),
(N'East', CAST(0xA8360B00 AS Date), 19, 3),
(N'East', CAST(0xB4360B00 AS Date), 30, 5),
(N'East', CAST(0xB8360B00 AS Date), 31, 3),
(N'East', CAST(0xC6360B00 AS Date), 32, 1),
(N'East', CAST(0xD4360B00 AS Date), 29, 2),
(N'East', CAST(0xE3360B00 AS Date), 23, 2),
(N'East', CAST(0xE4360B00 AS Date), 20, 1),
(N'Central', CAST(0x89350B00 AS Date), 13, 1),
(N'Central', CAST(0x8F350B00 AS Date), 12, 1),
(N'Central', CAST(0x95350B00 AS Date), 11, 2),
(N'Central', CAST(0xA0350B00 AS Date), 20, 3),
(N'Central', CAST(0xA4350B00 AS Date), 10, 1),
(N'Central', CAST(0xB2350B00 AS Date), 12, 0),
(N'Central', CAST(0xC3350B00 AS Date), 23, 2),
(N'Central', CAST(0xD2350B00 AS Date), 18, 1),
(N'Central', CAST(0xD3350B00 AS Date), 18, 0),
(N'East', CAST(0x89350B00 AS Date), 17, 1),
(N'East', CAST(0x8F350B00 AS Date), 21, 1),
(N'East', CAST(0x95350B00 AS Date), 13, 2),
(N'East', CAST(0xA0350B00 AS Date), 25, 3),
(N'East', CAST(0xA4350B00 AS Date), 26, 2),
(N'East', CAST(0xB2350B00 AS Date), 30, 0),
(N'East', CAST(0xC3350B00 AS Date), 27, 1),
(N'East', CAST(0xD2350B00 AS Date), 21, 1),
(N'Central', CAST(0xE4350B00 AS Date), 13, 1),
(N'Central', CAST(0xEA350B00 AS Date), 12, 1),
(N'Central', CAST(0xF0350B00 AS Date), 11, 2),
(N'Central', CAST(0xFC350B00 AS Date), 20, 3),
(N'Central', CAST(0x00360B00 AS Date), 10, 1),
(N'Central', CAST(0x0E360B00 AS Date), 12, 0),
(N'Central', CAST(0x1F360B00 AS Date), 23, 2),
(N'Central', CAST(0x2E360B00 AS Date), 18, 1),
(N'Central', CAST(0x2F360B00 AS Date), 18, 0),
(N'East', CAST(0xE4350B00 AS Date), 17, 1),
(N'East', CAST(0xEA350B00 AS Date), 21, 1),
(N'East', CAST(0xF0350B00 AS Date), 13, 2),
(N'East', CAST(0xFC350B00 AS Date), 25, 3),
(N'East', CAST(0x00360B00 AS Date), 26, 2),
(N'East', CAST(0x0E360B00 AS Date), 30, 0),
(N'East', CAST(0x1F360B00 AS Date), 27, 1),
(N'East', CAST(0x2E360B00 AS Date), 21, 1),
(N'East', CAST(0x2F360B00 AS Date), 17, 0),
(N'East', CAST(0x2F360B00 AS Date), 17, 0),
(N'Central', CAST(0x40360B00 AS Date), 13, 1),
(N'Central', CAST(0x46360B00 AS Date), 12, 1),
(N'Central', CAST(0x4C360B00 AS Date), 11, 2),
(N'Central', CAST(0x58360B00 AS Date), 20, 3),
(N'Central', CAST(0x5C360B00 AS Date), 10, 1),
(N'Central', CAST(0x6A360B00 AS Date), 12, 0),
(N'Central', CAST(0x7A360B00 AS Date), 23, 2),
(N'Central', CAST(0x89360B00 AS Date), 18, 1),
(N'Central', CAST(0x8A360B00 AS Date), 18, 0),
(N'East', CAST(0x40360B00 AS Date), 17, 1),
(N'East', CAST(0x46360B00 AS Date), 21, 1),
(N'East', CAST(0x4C360B00 AS Date), 13, 2),
(N'East', CAST(0x58360B00 AS Date), 25, 3),
(N'East', CAST(0x5C360B00 AS Date), 26, 2),
(N'East', CAST(0x6A360B00 AS Date), 30, 0),
(N'East', CAST(0x7A360B00 AS Date), 27, 1),
(N'East', CAST(0x89360B00 AS Date), 21, 1),
(N'East', CAST(0x8A360B00 AS Date), 17, 0),
(N'East', CAST(0x8A360B00 AS Date), 17, 0)
The above DDL contains count of SentLeads for every month of 2012 and only for the first 3 months of 2013 because in this scenario the present is Apr 2013.
SELECT
datepart(year,[activity_date])
,sum([SentLeads])
FROM [Sandbox].[dbo].[Example]
group by datepart(year,[activity_date])
The above SQL query gets a sum of sentLeads per Year.
But what I need is to calculate the sum of SentLeads for every month fully in the past of the current year (Jan, Feb, Mar), and divide that by the sum of sent leads for the corresponding months (jan, feb, mar) of the former year (2012). The query needs to be able to accommodate however many months have passed.
Can you point to a link or do you know the expression?
September 8, 2014 at 7:24 pm
I got a start on the solution... hopefully this will send you in the right direction. If you have 2012, you can use LAG to compare. The only catch is that you can't have months with no values.
SELECT Division
, Activity_Month
, Activity_Year
, MonthlyLeads
, MonthlyReferrals
, Leads12MosAgo
, Referrals12MosAgo
, Leads12MosPctDelta
, Referrals12MosPctDelta
FROM (
SELECT Division
, Activity_Month
, Activity_Year
, MonthlyLeads
, MonthlyReferrals
, LAG(MonthlyLeads,12) OVER (PARTITION BY Division ORDER BY Activity_Year, Activity_Month) AS Leads12MosAgo
, LAG(MonthlyReferrals,12) OVER (PARTITION BY Division ORDER BY Activity_Year, Activity_Month) AS Referrals12MosAgo
, MonthlyLeads/CAST(LAG(MonthlyLeads,12) OVER (PARTITION BY Division ORDER BY Activity_Year, Activity_Month) AS float) AS Leads12MosPctDelta
, MonthlyReferrals/CAST(LAG(MonthlyReferrals,12) OVER (PARTITION BY Division ORDER BY Activity_Year, Activity_Month) AS float) AS Referrals12MosPctDelta
FROM
(SELECT Division
, MONTH(activity_date) AS Activity_Month
, YEAR(activity_date) aS Activity_Year
, SUM(sentLeads) AS MonthlyLeads
, SUM(Referrals) AS MonthlyReferrals
FROM Example1
GROUP BY Division, YEAR(activity_date), MONTH(activity_date)) x
) y
WHERE Leads12MosPctDelta IS NOT NULL
ORDER BY Division, Activity_Year, Activity_Month;
HTH
(Sorry, still learning how to read... missed the part where you want the delta in %.)
September 8, 2014 at 8:11 pm
Wow, that seems complicated bordering on absurd. In sql can't we do a case statement, count of sentleads where datepart(month, current date-1year) is less than the datepart(month, current date), of currenyear, and then count(sent leads) ? Sorry, that's just psuedocode but im on , my phone. Anyways, im looking for ssrs expression not the query....notice the forum:-) so, probably an iif statement, yes?
September 9, 2014 at 6:34 am
But, since I am not comparing the difference between rows in a sql result set LAG() is not what I'm looking for.
LAG() "Accesses data from a previous row in the same result set without the use of a self-join in SQL Server 2014. LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row. "
I am looking to
(1) add the current year's to-date sentLeads
(2) add last year's sentLeads for the same period (so if 4 months have elapsed this year, than the first four months count(sentLeads) from last year.
(3) subtract last years count from this year's count to get a percentage
September 9, 2014 at 6:52 am
3 questions:
1. Can you post the query you are currently using for your SSRS dataset because the query you posted earlier doesn't have months?
2. To be clear, the only data you want is for completed months of the current year (not current month because it isn't completed) and the equivalent months the previous year?
3. Why does it matter if the calculation is done in T-SQL or an SSRS expression? I'd have to see how you returning your data to the report to figure our if it is even possible to do in an expression, hence question 1.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 9, 2014 at 8:16 am
Because I was intrigued by this problem I put some effort into figuring out what you want. I don't think doing this in an SSRS expression is the best way to do it. I think you should be doing it in T-SQL. Here's what I came up with using your test data:
Query I used:
/* Had to have a date that was in the past so need the data parameter/variable */
DECLARE @Date DATE = '2013-04-08';
/* need the detail data in the report. Be easier and more efficient to just return what
I need, but the requirement is an SSRS expression */
SELECT
YEAR(E.activity_date) AS theYear,
MONTH(E.activity_date) AS theMonth,
SUM(E.SentLeads) AS SentLeads
FROM
dbo.Example1 AS E
WHERE
/* need to get data 15 months back form the current month in process to be able to
get 13 months prior to the most recently completed month */
E.activity_date >= DATEADD(MONTH,
DATEDIFF(MONTH, '1900-01-01',
DATEADD(MONTH, -15, @Date)),
'1900-01-01') AND
` /* Don't want the in-process month's data */
E.activity_date < DATEADD(MONTH, DATEDIFF(MONTH, '1900-01-01', @Date),
'1900-01-01')
GROUP BY
YEAR(E.activity_date),
MONTH(E.activity_date)
ORDER BY theYear, theMonth;
In the report:
=Sum(Fields!SentLeads.Value)'this is:' The IIF is saying, check the year of the current row against the year in the rest of the rows in the dataset and if not equal then find the row that has a matching month and return the sentLeads else return NULL
' SUM the values found in the IIF
=SUM(Microsoft.VisualBasic.Interaction.iif(Fields!theYear.Value <> Lookup(Fields!theMonth.Value, Fields!theMonth.Value, Fields!theYear.Value, "TestDataSet"), Lookup(Fields!theMonth.Value, Fields!theMonth.Value, Fields!SentLeads.Value, "TestDataSet"), System.DBNull.Value))
This is definitely not how I think it should be done, but it appears to work. It isn't intuitive and it isn't efficient as the SQL Server is returning more data than is needed for the requirement and the report has to do more processing than it probably has to. Based on the requirements you shared I'd be more inclined to do a query like this:
/* Had to have a date that was in the past so need the data parameter/variable */
DECLARE @Date DATE = '2013-04-08';
WITH leads
AS (
SELECT
YEAR(E.activity_date) AS theYear,
MONTH(E.activity_date) AS theMonth,
SUM(E.SentLeads) AS SentLeads
FROM
dbo.Example1 AS E
WHERE
/* need to get data 15 months back form the current month in process to be able to
get 13 months prior to the most recently completed month */
E.activity_date >= DATEADD(MONTH,
DATEDIFF(MONTH, '1900-01-01',
DATEADD(MONTH, -15, @Date)),
'1900-01-01') AND
/* Don't want the in-process month's data */
E.activity_date < DATEADD(MONTH, DATEDIFF(MONTH, '1900-01-01', @Date),
'1900-01-01')
GROUP BY
YEAR(E.activity_date),
MONTH(E.activity_date)
)
SELECT
SUM(L1.SentLeads) AS ThisYearSentLeads,
SUM(L2.SentLeads) AS LastYearSentLeads,
SUM(L1.SentLeads)/(SUM(L2.SentLeads) * 1.0) * 100 AS pctOfLastYearsLeads
FROM
leads AS L1 LEFT JOIN
leads AS L2 ON
L1.theYear <> L2.theYear AND
L1.theMonth = L2.theMonth
WHERE
L1.theYear = YEAR(@Date);
And then just have a simple table in the report.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 9, 2014 at 9:03 am
..
--Quote me
September 9, 2014 at 9:09 am
Thanks Jack, I'll take a look at this (just got it). I really appreciate it
btw, earlier I said
subtract last years count from this year's count to get a percentage
But I meant subtract last years count from this year's count AND divide by last year's count to get a percentage.
Ps, the ddl I provided was generated using SSMS, right clicking on the database, selecting Tasks, generating schema and data for table I created for the exercise but lost my own ddl for. The dates were put into that hex format by SSMS....but when you run the ddl query it creates original dates.
OK, now to reviewing what you wrote. crossing my fingers!
September 9, 2014 at 9:12 am
KoldCoffee (9/9/2014)
Thanks Jack, I'll take a look at this (just got it). I really appreciate itbtw, earlier I said
subtract last years count from this year's count to get a percentage
But I meant subtract last years count from this year's count AND divide by last year's count to get a percentage.
Ps, the ddl I provided was generated using SSMS, right clicking on the database, selecting Tasks, generating schema and data for table I created for the exercise but lost my own ddl for. The dates were put into that hex format by SSMS....but when you run the ddl query it creates original dates.
OK, now to reviewing what you wrote. crossing my fingers!
Can you answer the 3 questions I posted previously and repeat here:
3 questions:
1. Can you post the query you are currently using for your SSRS dataset because the query you posted earlier doesn't have months?
2. To be clear, the only data you want is for completed months of the current year (not current month because it isn't completed) and the equivalent months the previous year?
3. Why does it matter if the calculation is done in T-SQL or an SSRS expression? I'd have to see how you returning your data to the report to figure our if it is even possible to do in an expression, hence question 1.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 9, 2014 at 11:21 am
Jack, Yes I need completed months of the current year compare with same months last year. My actual query is sizable and used to feed multiple tables and charts in an SSRS report. Therefore the DDL I gave was only for this forum, narrowed to be enough for addressing the specific issue. I already have Month and Year values in my dataset, and it wasn't hard to edit your expression:
=SUM(Microsoft.VisualBasic.Interaction.iif(Fields!theYear.Value <> Lookup(Fields!theMonth.Value, Fields!theMonth.Value,
Fields!theYear.Value,"TestDataSet"),
Lookup(Fields!theMonth.Value, Fields!theMonth.Value, Fields!SentLeads.Value, "TestDataSet"), System.DBNull.Value))
I do need to do in Expression. Could you please explain what this expression is doing? While it didn't fail in my report, it's output was simply '1' and I have no idea how to read it to adapt it.
September 9, 2014 at 2:43 pm
Okay the expression gets the leads from last year that match this year and sums them.
Binoogle SSRS Lookup to understand how the Lookup function works.
Attached is a working report (embedded data source), based on your test data. It isn't pretty but I think it does most of what you want.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 9, 2014 at 3:07 pm
ok, while you were responding I was coming up with the most thorough summation of what I was after with step by step instructions on how to use my DDL to recreate the report in SSRS and images included. So, here is;
using the DDL provided above, create a new dataset using the following query
SELECT [activity_date]
, month([activity_date]) MonNo
, year([activity_date]) Year
,[SentLeads]
,[Referrals]
FROM [Sandbox].[dbo].[Example1]
Now make the report.
(1) insert matrix item
(2) select MonNo to Columns cell
(3) select sentLeads to Data cell
(4) select Year to Row cell
(5) insert row inside group below bottom row
(6) add Referrals to Data cell
(7) r-click on Year row group and add filter WHERE Year = year(now)-1 so that it pulls 2013.
(8) you are done with first table, make a copy of it so you have two tables and only change the expression on Year so that it's year(now)-2
(9) now you will have two tables, one for 2013, one for 2012 with sum of sent leads and referral across the page grouped by month number.
see the attached images.
in the second of two images you'll see the column that I want. It's in red and called YoY.
Assuming that the current year is 2013 and the elapsed months of 2013 are 1, 2 and 3 (jan, feb, mar), the only months of interest at this time will be the sames months of the year before.
Using my sample data the calculations for variances .14 and .77 are gotten as follows:
sentLeads =
(104+141+136) - (87+123+124) / (87+123+124)
Referrals =
(12+16+11) - (8+9+5) / 8+9+5
I need an expression that will do these YOY variance calculations automatically, and always for however months of the current year that have elapsed.
Can someone please show me the expression needed for that one column/two values?
September 9, 2014 at 3:28 pm
The expression I provided should still work for what you need, you'll just need to figure out how to get it to work with the dataset you have and within the report you have. If you can't do that I'd suggest hiring an SSRS consultant to come in and work with you to get it done.
It is nearly impossible to come up with working solutions without all the information needed. I provided a working report that met the original requirements you posted or was close and now the layout of the report is completely changed and is a matrix, not a table and has additional data displayed.
Why do you need activity_date in the dataset? It isn't used in the example report you provided.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply