July 26, 2013 at 11:35 am
We would like to generate a report that shows the following:
SalesPersonA - Sales for Jan-June2012 - Sales for Jan-June2013
SalesPersonB - Sales for Jan-June2012 - Sales for Jan-June2013
SalesPersonC - Sales for Jan-June2012 - Sales for Jan-June2013
I can write 2 datasets that find "Sales for Jan-June2012" and "Sales for Jan-June2013" - but I can't figure out how to display the results in one table, sorted by Sales Person...
Then we will want to calculate the diff between 2012 & 2013, but I think I can write the expression for that.
July 28, 2013 at 3:20 pm
My preferred approach would be to do the pivoting of the data in the query for the dataset, like this:
select
sales_person_id,
JanJun2012 = sum(case when month(sale_date) <= 6 and year(sale_date) = 2012 then sale_amount else 0 end),
JanJun2013 = sum(case when month(sale_date) <= 6 and year(sale_date) = 2013 then sale_amount else 0 end)
from
sales_data
group by
sales_person_id
But this can also be done in SSRS by putting the following expressions in a scope that represents each sales person:
=Sum(IIf(Month(Fields!sale_date.Value) <= 6 And Year(Fields!sale_date.Value) = 2012, Fields!sale_amount.Value, 0))
=Sum(IIf(Month(Fields!sale_date.Value) <= 6 And Year(Fields!sale_date.Value) = 2013, Fields!sale_amount.Value, 0))
July 28, 2013 at 5:23 pm
pluto1415 (7/26/2013)
We would like to generate a report that shows the following:SalesPersonA - Sales for Jan-June2012 - Sales for Jan-June2013
SalesPersonB - Sales for Jan-June2012 - Sales for Jan-June2013
SalesPersonC - Sales for Jan-June2012 - Sales for Jan-June2013
I can write 2 datasets that find "Sales for Jan-June2012" and "Sales for Jan-June2013" - but I can't figure out how to display the results in one table, sorted by Sales Person...
Then we will want to calculate the diff between 2012 & 2013, but I think I can write the expression for that.
SELECT
result.SalesPersonID,
result.SalesPerson,
result.[2013 Sales] - result.[2012 Sales] AS [Difference]
FROM
(
SELECT
Y12.SalesPersonID,
Y12.SalesPerson,
SUM(Y12.Sales) OVER (PARTITION BY Y12.SalesPersonID) AS [2012 Sales],
SUM(Y13.Sales) OVER (PARTITION BY Y13.SalesPersonID) AS [2013 Sales],
FROM
[Sales for Jan-June2012] AS Y12
INNER JOIN
[Sales for Jan-June2013] AS Y13
ON Y12.SalesPersonID = Y13.SalesPersonID
) result
ORDER BY
result.SalesPerson
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply