I would like to generate month wise category-wise sales report.Is it possible in RMS?
The Format will should be
DEPARTMENT | CATEGORY | ITEMLOOKUPCODE | DESCRIPTION | JAN | FEB | MARCH | JUNE | JULY | AUG | TOTAL |
TECHNOLOGY | LAPTOP & NETBOOK | 888228942216 | LENOVO G400 NON TOUCH | 2 | 4 | 8 | 10 | 5 | 6 | 35 |
Total 35 pcs sold from Jan to August.Is it Possible?
*This post is locked for comments
Hi Mr Ed Boyer,
Thanks a Lot for ur queries..i tried to run the report,its showing some errors..i will sned ut the report in ur mail..can u plz check it, what will be the problem?
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'MonthlySalesView') Drop View MonthlySalesView;
GO
Create View MonthlySalesView as
select month([transaction].Time) as MonthNum, YEAR([Transaction].[time]) as YearNum, sum(transactionentry.quantity) as QTY, Item.ID as ItemID, Item.ItemLookupCode , Item.[Description] , Department .Name as Department, Category .name as Category from [transactionentry]
left join [Transaction] on [transactionentry].TransactionNumber = [Transaction] .TransactionNumber
left join Item on TransactionEntry.ItemID = item.ID
left join Category on item.CategoryID = Category .ID
left join Department on item.DepartmentID = Department .ID
where [transaction].time between '2014/01/01' and '2014/12/31'
group by month([transaction].Time) ,YEAR([Transaction].[time]), Item.ID, Item.ItemLookupCode , Item.[Description] , Department .Name, Category .name;
GO
Select ItemID, Yearnum, Itemlookupcode, [Description], Department, Category
,(select SUM( Qty) from monthlysalesview as MSV where MSV.ItemID = MOnthlysalesview.ItemID and MOnthNum =1 ) as JanQTY
,(select SUM( Qty) from monthlysalesview as MSV where MSV.ItemID = MOnthlysalesview.ItemID and MOnthNum =2) as FebQTY
,(select SUM( Qty) from monthlysalesview as MSV where MSV.ItemID = MOnthlysalesview.ItemID and MOnthNum =3) as MarQTY
,(select SUM( Qty) from monthlysalesview as MSV where MSV.ItemID = MOnthlysalesview.ItemID and MOnthNum =4) as AprQTY
,(select SUM( Qty) from monthlysalesview as MSV where MSV.ItemID = MOnthlysalesview.ItemID and MOnthNum =5) as MayQTY
,(select SUM( Qty) from monthlysalesview as MSV where MSV.ItemID = MOnthlysalesview.ItemID and MOnthNum =6) as JunQTY
,(select SUM( Qty) from monthlysalesview as MSV where MSV.ItemID = MOnthlysalesview.ItemID and MOnthNum =7) as JulQTY
,(select SUM( Qty) from monthlysalesview as MSV where MSV.ItemID = MOnthlysalesview.ItemID and MOnthNum =8) as AugQTY
,(select SUM( Qty) from monthlysalesview as MSV where MSV.ItemID = MOnthlysalesview.ItemID and MOnthNum =9) as SepQTY
,(select SUM( Qty) from monthlysalesview as MSV where MSV.ItemID = MOnthlysalesview.ItemID and MOnthNum =10) as OctQTY
,(select SUM( Qty) from monthlysalesview as MSV where MSV.ItemID = MOnthlysalesview.ItemID and MOnthNum =11) as NovQTY
,(select SUM( Qty) from monthlysalesview as MSV where MSV.ItemID = MOnthlysalesview.ItemID and MOnthNum =12) as DecQTY
, (select SUM(QTY) from MonthlySalesView as MSV where MSV.ItemID = MOnthlysalesview.ItemID ) as TotalQty
from MonthlySalesView
This will do what you want. You can put it in a report if you wish. First part prior to GO would be PreQuery1. The next part would be PreQuery2.
Then the last part would go under tablesqueried.
This query lumps multiple years together, so if you need years seperated out then run the query seperately for each year.
It is possible, but need to build a 'view' first and then read it from there to your new report. Hope this will light some idea to your requirements.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,902 Super User 2024 Season 2
Martin Dráb 229,302 Most Valuable Professional
nmaenpaa 101,156