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
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. :-)
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.
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,867 Super User 2024 Season 2
Martin Dráb 229,173 Most Valuable Professional
nmaenpaa 101,156