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.
#1
André Arnaud de Cal...
291,431
Super User 2024 Season 2
#2
Martin Dráb
230,503
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
