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
