Skip to main content

Notifications

Microsoft Dynamics RMS forum

Add Supplier table ang group by Supplier to report

Posted on by Microsoft Employee

 Hi guys,

 I am looking for a little help from someone with sql experiece. I have the regular detailed sales report from rms 2.0  but I am looking to add the supplier table to the report and also to be able to group it by supplier. I would appreciate any advice or direction

this is what I currently have which will group it by category

 PreQuery1 = <BEGIN>
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ViewItems') DROP VIEW ViewItems
<END>
   PreQuery2 = <BEGIN>
     CREATE VIEW ViewItems AS
         SELECT
         CASE WHEN Category.ID IS NULL THEN 0 ELSE Category.ID END AS CategoryID,
         SUM(Item.Quantity) as Quantity,
         SUM(Item.Quantity * Item.Cost) as InvCost
 
         FROM Item
         LEFT JOIN Category ON Item.CategoryID = Category.ID
         GROUP BY Category.ID
         
     <END>
   TablesQueried = <BEGIN>
      FROM Item
     LEFT JOIN TransactionEntry WITH(NOLOCK) ON Item.ID = TransactionEntry.ItemID
         INNER JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber
         LEFT JOIN Category WITH(NOLOCK) ON Item.CategoryID = Category.ID
         LEFT JOIN Supplier WITH(NOLOCK) ON Item.SupplierID = Supplier.ID
     LEFT JOIN ViewItems WITH(NOLOCK) ON Item.CategoryID = ViewItems.CategoryID


<END>
   SelCriteria = ""
   GroupBy = "Category.ID, Category.Name"
   SortOrder = ""
End ReportSummary

 

Thanks in advance,

kind regards,

Margaret

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Re: Add Supplier table ang group by Supplier to report

    Convoluted your the best ever, I should have thought of that. Your see the profit margin is actually incorrect on the default sales report but after some lengthy talks with MS they put up correct reports for profit margin but in the new reports they have taken out the supplier field altogether but I can just go back to the original report use the correct column information for the profit margin column and thats it. Really really appreciate your post back. :-)

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Add Supplier table ang group by Supplier to report

    Hi Margaret

    You should be able to group by Supplier with the default report, simply drag and drop the supplier column so that it is the first column on the report - the top row will be your "group by" totals which will be totals by Supplier (the default column is Department).

    I believe the Supplier is hidden by default - right click any column header and select SHOW / HIDE COLUMNS, then check mark the Supplier column; drag it over so its the first column - then memorize the report so that it retains your settings.

    Hope this helps.


     

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 Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

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,867 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,173 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans