Hi all,
I was wondering if it would be possible to use the Top Departments report and add a column for percentage of total sales of which that department is responsible? I am using HQ 2.0.200
Thanks for any suggestions/help!
*This post is locked for comments
Hello Archelle, No apology necessary! A crystal report version would be great. Thanks for the help.
Cheers,
Craig
hello craig! sorry for the late reply
btw, i've tried to convert this to qrp but failed due to the restrictions of rms, but then, i can make a copy of this to crystal report. Let me know if that would be ok thanks!
Any chance of getting a copy of this report? I've been trying to get it to work but have been unsuccessful.
Regards,
Craig
Not sure I can get into QRP and it actually function, but I can just run this in SSMS and export. Thanks!
Edit: Figured it out and it works perfectly!
If you know how to convert this to qrp report, here is the sql for you.
SELECT tbl.Name department ,
tbl.AmtSold ,
tbl.AmtSold / SUM(tbl.AmtSold) OVER ( ) AS Percentage
FROM ( SELECT dbo.Department.Name ,
SUM(TE.Quantity * te.Price) AmtSold
FROM ( SELECT StoreID ,
TransactionNumber ,
CAST(time AS DATE) datesold
FROM dbo.[Transaction] WITH ( NOLOCK )
) t
LEFT JOIN ( SELECT StoreID ,
TransactionNumber ,
ItemID ,
Quantity ,
Price
FROM dbo.TransactionEntry WITH ( NOLOCK )
) te ON te.StoreID = t.StoreID
AND te.TransactionNumber = t.TransactionNumber
LEFT JOIN ( SELECT ID ,
DepartmentID
FROM dbo.Item WITH ( NOLOCK )
) item ON item.ID = TE.ItemID
LEFT JOIN dbo.Department WITH ( NOLOCK ) ON dbo.Department.ID = item.DepartmentID
LEFT JOIN ( SELECT id ,
storecode
FROM dbo.Store WITH ( NOLOCK )
) store ON store.ID = T.StoreID
WHERE t.datesold >= '2016-01-01'
GROUP BY dbo.Department.Name
) tbl
ORDER BY tbl.AmtSold DESC
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (