Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 How to show results from 2 timeframes? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, July 26, 2013 11:35 AM
 Forum 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-June2013SalesPersonB - Sales for Jan-June2012 - Sales for Jan-June2013SalesPersonC - Sales for Jan-June2012 - Sales for Jan-June2013I 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 Group: General Forum Members Last Login: Thursday, July 21, 2016 9:25 AM Points: 176, Visits: 543
 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_datagroup 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 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-June2013SalesPersonB - Sales for Jan-June2012 - Sales for Jan-June2013SalesPersonC - Sales for Jan-June2012 - Sales for Jan-June2013I 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 ) resultORDER BY result.SalesPerson`
Post #1478416

 Permissions