Minor ad hoc changes are made to the dashboard of sales that we just completed to create a dashboard of open orders. This report is intended to be informational in nature, and is typically used to review work created through yesterday; not to monitor and work with today's activity.
If you used the view for History only, you will need to completely recreate the previous report using the view for the Open transactions. If you used the downloadable view (see Preface for download location), you can follow the remaining steps. If you did use the History view, we've included the view for open transactions in the upcoming section.
This report is for the same company that sells all across the U.S., and sells products from a variety of manufacturers. This company has a direct sales force, a physical showroom where customers can purchase in person, and a web presence where customers can place orders directly via their website.
The owner needs to be able to review what is happening with open orders/sales. Since this is to review what is going on, work through yesterday is considered real time. This means we do not need the report to constantly refresh; refreshing once daily is sufficient and appropriate. We can use all the work we just completed for the previous report on posted/historical sales dashboard.
We'll be using the existing sales dashboard to keep the owner from having multiple analysis reports, reducing complications and training.
There are two parts to these steps. The first part is technical, containing steps that involve work in SQL Server. The second part is the actual building of the report. You may choose to obtain assistance with the technical part, especially if you do not have access to SQL Server.
Create the following view in SQL Server if you used the view for historical transactions in the previous report, and then follow the steps for building the previous report. If you used the view that includes both open and historical documents, move directly to the non-technical matter:
Tip
For directions to create a view, follow the technical matter step for the first report in Chapter 2, Business Intelligence for the General Ledger.
CREATE VIEW view_BI_SOP_OpenDocs AS SELECT CASE SH.soptype WHEN 3 THEN 'Invoice' WHEN 4 THEN 'Return' WHEN 1 THEN 'Quote' WHEN 2 THEN 'Order' WHEN 5 THEN 'Backorder' WHEN 6 THEN 'Fulfillment_Order' ELSE 'Other' END AS Doc_Type, SH.sopnumbe AS Doc_Number, SH.docdate AS Doc_Date, SH.glpostdt AS GL_Date, SH.bachnumb AS Batch, SH.custnmbr AS Customer_ID, SH.custname AS Customer, SH.cstponbr AS Customer_PO, SH.mstrnumb AS Master_Num, SL.locncode AS Inventory_Site, SL.itemnmbr AS Item_ID, SL.itemdesc AS Item_Desc, CASE sl.soptype WHEN 4 THEN -sl.quantity ELSE sl.quantity END AS Quantity, SL.uofm AS Unit, CASE SL.soptype WHEN 4 THEN -sl.unitcost ELSE sl.unitcost END AS Unit_Cost, SL.extdcost AS Extended_Cost, SL.unitprce AS Unit_Price, CASE sl.soptype WHEN 4 THEN -sl.xtndprce ELSE sl.xtndprce END AS Extended_Price, CASE sl.soptype WHEN 4 THEN -( SL.xtndprce - SL.extdcost ) ELSE ( SL.xtndprce - SL.extdcost ) END AS [Gross Margin], SL.reqshipdate AS Requested_Ship_Date, SL.actlship AS Actual_Ship_Date, SL.slprsnid AS Salesperson_ID, SL.shipmthd AS Shippping_Method, SL.prstadcd AS Ship_To_Address_ID, SL.shiptoname AS Ship_To_Name, SL.cntcprsn AS Contact_Person, SL.address1 AS Address1, SL.address2 AS Address2, SL.address3 AS Address3, SL.city AS City, SL.state AS State, SL.zipcode AS Zip, SL.country AS Country, IM.itmclscd AS Item_Class, IM.uscatvls_1 AS Manufacturer, IM.uscatvls_2 AS Category2, IM.uscatvls_3 AS Category3, IM.uscatvls_4 AS Category4, IM.uscatvls_5 AS Category5, IM.uscatvls_6 AS Category6 FROM dbo.sop10100 AS SH LEFT OUTER JOIN dbo.sop10200 AS SL ON SH.soptype = SL.soptype AND SH.sopnumbe = SL.sopnumbe LEFT OUTER JOIN dbo.iv00101 AS IM ON SL.itemnmbr = IM.itemnmbr WHERE ( SH.voidstts = 0 ) GO GRANT SELECT ON view_BI_SOP_OpenDocs TO DYNGRP
If you haven't already done so, complete the steps in the previous example, creating a sales dashboard.
- Click on the Document_Date timeline and select the Time Level arrow in the top- right corner. This is only if you want to be able to review by days. Select DAYS on the Time Level drop-down list:
- If you are using the view that shows both unposted and posted documents, in the Status slicer, select Open, which will unmark History:
- Final results show all quotes for 4/12/17. I see that the only quote(s) was by salespeople Paul W. and Nancy B. These quotes were for customers Contoso, Ltd. and Aaron Fitz Electrical; for servers to be delivered in Nebraska and Illinois:
