Add Supplier table ang group by Supplier to report

(0) Share

Report

Posted on by Community Member 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

All responses (2)

Answers (0)

Community Member Microsoft Employee on at

Like (0)

Report

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 Microsoft Employee on at

Like (0)

Report

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.