Skip to main content

Notifications

Microsoft Dynamics RMS forum

REPORT HELP (BATCH + DEPT SALES)

Posted on by Microsoft Employee

I WANT TO BE ABLE TO CREATE A VIEW TABLE THAT AM ABLE VIEW DEPT SALES PER BATCH AS WHEN THEY ARE PRINTED ON THE ZREPORT

I WANT A TABLE OR ABLE TO EXPORT AS A CSV FILE 

TO SHOW SALES AMOUNT THAT ARE LINKED TO BATCHES

A SQL COMMAND THAT WILL HELP ME

*This post is locked for comments

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: REPORT HELP (BATCH + DEPT SALES)

    SQL Command:  View created from above query supplied by Archelle Marcito

    Create View [YourViewNameHere]  AS

    SELECT  b.BatchNumber ,

                  d.Name AS Department ,

                  SUM(te.Price * te.Quantity) AS TotalSales

          FROM    dbo.[Transaction] t

                  LEFT JOIN dbo.TransactionEntry te ON te.TransactionNumber = t.TransactionNumber

                  INNER JOIN dbo.Batch b ON b.BatchNumber = t.BatchNumber

                  INNER JOIN Item i ON i.ID = te.ItemID

                  LEFT JOIN dbo.Department d ON d.ID = i.DepartmentID

        GROUP BY b.BatchNumber ,

                  d.Name

    Had to leave out where clause for time, but you could probably add this to the view if required.  Also the order by clause is not used or needed in a view.  You would use the order by when querying the view.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: REPORT HELP (BATCH + DEPT SALES)

    Is it possible to create as a view table so I don't have exec the command every time like it create the last of depart along the the batch number attach to it

  • Verified answer
    archelle16 Profile Picture
    archelle16 1,735 on at
    RE: REPORT HELP (BATCH + DEPT SALES)

    Hello here's a simple stored procedure i created for u that displays BatchNumber, department and its equivalent sales.

    Execute this first:

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE PROCEDURE DepartmentSalesPerBatch

       @fromdate AS DATETIME ,

       @todate AS DATETIME

    AS

       BEGIN

           SET NOCOUNT ON

           SET @fromdate = CAST(@fromdate AS DATETIME)

           SET @todate = DATEADD(hour, 24, @todate)

           SET @todate = DATEADD(SECOND, -1, @todate)

           SELECT  b.BatchNumber ,

                   d.Name AS Department ,

                   SUM(te.Price * te.Quantity) AS TotalSales

           FROM    dbo.[Transaction] t

                   LEFT JOIN dbo.TransactionEntry te ON te.TransactionNumber = t.TransactionNumber

                   INNER JOIN dbo.Batch b ON b.BatchNumber = t.BatchNumber

                   INNER JOIN Item i ON i.ID = te.ItemID

                   LEFT JOIN dbo.Department d ON d.ID = i.DepartmentID

           WHERE   t.Time > = @fromdate

                   AND t.[Time] <= @todate

           GROUP BY b.BatchNumber ,

                   d.Name

           ORDER BY b.BatchNumber ,

                   d.Name

       END

    GO

    RUN THE ABOVE QUERY and USE THE FOLLOWING SYNTAX:

    exec DepartmentSalesPerBatch

             @fromdate = 'key in ur date from here'

             @todate= 'key in ur to date here'

    HOPE THIS HELPS. THANKS.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: REPORT HELP (BATCH + DEPT SALES)

    STOREDB

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: REPORT HELP (BATCH + DEPT SALES)

    store db

  • Suggested answer
    archelle16 Profile Picture
    archelle16 1,735 on at
    RE: REPORT HELP (BATCH + DEPT SALES)

    So u are looking for an sqlcommand to view department sales per batch? Is this command to run in HQ database or store db instead?

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: REPORT HELP (BATCH + DEPT SALES)

    email me at edsrmsanswers@gmail.com.  We can discuss getting this create for you.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: REPORT HELP (BATCH + DEPT SALES)

    please don't type in all CAPS.  Its considered yelling.

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

News and Announcements

Give Back to the Community this Month

Quick Links

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

#2
Martin Dráb Profile Picture

Martin Dráb 229,135 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans