Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to show results from 2 timeframes? Expand / Collapse
Author
Message
Posted Friday, July 26, 2013 11:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 26, 2013 1:06 PM
Points: 1, Visits: 4
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.
Post #1478125
Posted Sunday, July 28, 2013 3:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 28, 2014 1:02 PM
Points: 150, Visits: 445
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))

Post #1478411
Posted Sunday, July 28, 2013 5:23 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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


Post #1478416
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse