Skip to main content

Notifications

Microsoft Dynamics RMS (Archived)

CUTOMIZED MONTHWISE CATEGORY SALES REPORT

Posted on by Microsoft Employee

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

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: CUTOMIZED MONTHWISE CATEGORY SALES REPORT

    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?

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: CUTOMIZED MONTHWISE CATEGORY SALES REPORT

    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.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: CUTOMIZED MONTHWISE CATEGORY SALES REPORT

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Complete!

🔔 Be sure to subscribe to the new forums you are interested in to stay up to date! 🔔

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,902 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,302 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans