SQLTeam.com | Weblogs | Forums

Pivot Data in SSRS

sql2012

#1

Hi Guys I ask for help with the current report that I'm busy with. I'm writing report that will filter last 12 months data. Below is my query:
SELECT
AH.customername,
AH. accountdate,
AH. revenue,
EA.AccountNumber,
DATENAME(month, AH.accountdate) As AccountMonth,
DATEADD(year,-1,(dateadd(month, datediff(month, -1, getdate()) - 1, -1) + 1)) AS StarDate
FROM
_accounthistory AH
INNER JOIN Account EA ON EA.Customer = AH.customer

WHERE AH.accountdate>= DATEADD(year,-1,(dateadd(month, datediff(month, -1, getdate()) - 1, -1) + 1))
AND AH.accountdate<= dateadd(month, datediff(month, -1, getdate()) - 1, 1)
AND DATEDIFF(month,DATEADD(year,-1,(dateadd(month, datediff(month, -1, getdate()) - 1, -1) + 1)), dateadd(month, datediff(month, -1, getdate()) -1, 1)) =12

ORDER BY AH.customername ASC

I want my data to look like this in the report:
Customer Account Number as Rows Account Month Revenue as Column. The query is working perfectly fine but i cannot see to put this data on the matrix accordingly. On the report account number that appear more than twice but have different revenue doesn't show.

And I want to archive this


#2

Just do a Google search for "matrix report in ssrs"... There is no shortage of tutorials, including YouTube videos.


#3

Hi Jason

Thanks for reply. I've managed to solve it. there was duplicated data that was making all the confusion.