Automatic Monthly Sales Report – Reporting services

A quick to solve problem came up on last Friday. One of our customers needs as part of their contract with us, a list of all the sales order lines we have created for them for the previous month.This report is needed on the first of each month.

To solve this I chose my tool, reporting services and set to work.

As always everyone uses Dynamics GP differently, for this company the back order document type is not used. Thus all items remain on the same order throughout the lifecycle of the order. This makes reporting a relative doddle.
Customers are grouped using the Territory ID field by us so we can report on groups of customers. That results in the following SQL to drive the report, this was pasted into the query of a new report, and the body of the report created to show the results. We wanted to show voided orders, you can exclude them with a VOIDSTTS=0 in the where for each half.

WITH SOPWorkHist 
AS
(SELECT    SOP10100.SOPTYPE,  SOP10100.SOPNUMBE, SOP10100.DOCDATE,
    SOP10100.CUSTNMBR, SOP10100.CSTPONBR, SOP10100.CUSTNAME, SOP10200.ITEMNMBR, 
    SOP10200.ITEMDESC, SOP10200.OXTNDPRC, SOP10200.QUANTITY, 
    SOP10200.QTYCANCE, SOP10200.QTYTBAOR, IV00101.USCATVLS_6, 
    IV00101.USCATVLS_1
FROM         IV00101 WITH (NOLOCK) RIGHT OUTER JOIN
                      SOP10100 WITH (NOLOCK) INNER JOIN
                      SOP10200 WITH (NOLOCK) ON SOP10100.SOPNUMBE = SOP10200.SOPNUMBE 
                      AND SOP10100.SOPTYPE = SOP10200.SOPTYPE ON 
                      IV00101.ITEMNMBR = SOP10200.ITEMNMBR
WHERE     (SOP10100.DOCDATE > @StartDate) AND (SOP10100.DOCDATE < @EndDate) 
AND (SOP10100.SOPTYPE = 2)
AND SOP10100.CUSTNMBR IN(
SELECT CUSTNMBR FROM RM00101 WHERE  (RM00101.SALSTERR = @CustomerIdent))
UNION 
SELECT     SOP30200.SOPTYPE, SOP30200.SOPNUMBE, SOP30200.DOCDATE, 
           SOP30200.CUSTNMBR, SOP30200.CSTPONBR, SOP30200.CUSTNAME, SOP30300.ITEMNMBR, 
           SOP30300.ITEMDESC, SOP30300.OXTNDPRC, SOP30300.QUANTITY, SOP30300.QTYCANCE, 
           SOP30300.QTYTBAOR, 
           IV00101.USCATVLS_6, IV00101.USCATVLS_1
FROM         IV00101 WITH (NOLOCK) RIGHT OUTER JOIN
                      SOP30200 WITH (NOLOCK) INNER JOIN
                      SOP30300 WITH (NOLOCK) ON SOP30200.SOPTYPE = SOP30300.SOPTYPE 
                      AND SOP30200.SOPNUMBE = SOP30300.SOPNUMBE ON 
                      IV00101.ITEMNMBR = SOP30300.ITEMNMBR
WHERE     (SOP30200.DOCDATE > @StartDate) AND (SOP30200.DOCDATE < @ENDDate) 
AND (SOP30200.SOPTYPE = 2) 
AND SOP30200.CUSTNMBR IN(
SELECT CUSTNMBR FROM RM00101 WHERE  (RM00101.SALSTERR = @CustomerIdent)))
-- Now select what we require from above
select * from SOPWorkHist order by 3,2

This was fine. The next challenge was to automatically email this every month. To do this I simply set up a default parameter that calculated the dates for the previous month. If you live in the UK don’t fall into the trap of copy and paste. Many of the examples to get the first and last day of the month for reporting services expressions on the web are assuming US format dates.

For us I ensured the language setting property of the report was set to UK English.

For the parameters of the report, StartDate and EndDate had formulas entered as non-queried default values (you might like to check midnight boundary conditions here, not a worry for my requirement as orders only get entered during office hours):

StartDate:
= DateSerial(datevalue(Now().AddMonths(-1)).Year, datevalue(Now().AddMonths(-1)).Month ,1 )

and

EndDate:

=dateserial(datevalue(Now()).Year, datevalue(Now()).Month,1).AddMilliseconds(-1)

 

A default code was also set up for the CustomerIdent Parameter for this particular customer group.

This defaults the values using the reporting services expresssion to the first and last day of the previous month.

Finally the report had a schedule created for it that emails it every month on the first of that month to the recipients. The default values populate with the previous month’s first of the month and last day of the month, Job done.

Duplicate SOP Master number in Dynamics GP

The credit card fulfilment add in that I wrote charges cards using sage pay API, from the GP screens or our custom SOP Fulfilment software, when the goods are despatched on a sales order.

In order for the charges to be tracked as the sales document migrates from Sales order to Invoice and subsequent fulfilments I utilise the master number of the order. However it turns out that the master number can end up getting shared by more than one document.

Master Numbers – what are they?

Master numbers can be enabled in the SOP setup screen.

SOPSetup

When ever a new document is created from a source document, they are tied together with a common master number. The stored procedure:taSopGetMasterNumber in the company database serves us with the next master number from number in this setup screen when ever a new sales document is created without getting derived from another document.

You can use master numbers in SOP Document enquiry and reporting to see all related documents as the master number ties them all together.

Our Issue

Somehow we have found that some of our sales documents have ended up sharing master numbers with unrelated documents. We have seen this with the SOP number itself too but there it is more obvious.

To find the maser numbers that are causing a problem I put together the following script that will work on SQL server 2005+. Note that we don't use back order document types so only one sales order document exists per master number according to our business logic.

WHERE ORIGTYPE=0 should help identify them if you do use back order documents, but the query will not use the indexes on the tables and thus will take a long time to execute.

-- Script to identify duplicate Master Numbers 
--  on Dynamics GP SOP Documents
With WorkHistUnion (MSTRNUMB, SOPNUMBE )as
(SELECT MSTRNUMB,SOPNUMBE  FROM SOP10100 WHERE SOPTYPE=2
UNION ALL
SELECT MSTRNUMB,SOPNUMBE FROM SOP30200 WHERE SOPTYPE=2)
SELECT  MSTRNUMB, COUNT(MSTRNUMB)from WorkHistUnionGROUP 
BY MSTRNUMBHAVING COUNT(MSTRNUMB)>1ORDER BY MSTRNUM
 

Trying this on one of our GP9 companies I got (278 row(s) affected). So we have 278 instances of sales documents sharing master numbers with unrelated sales orders. Now I have introduced a check that looks for this senario and uses the customer CUSTNMBR as a key when charging cards.

Hopefully this should stop the wrong customer getting charged for a despatch of goods – bug squished!

Why do we get this situation? Dynamics GP does not lean very much on SQL server for database integrity. This is due to the heritage of the product, rooted in DBISAM, it has never used the database to ensure integrity. SQL server is quite capable through the correct transaction handling and constraint indexes of ensuring the master numbers are not shared, but sadly it has never been written into the database.There must be some holes in the application that allows this to happen in a similar way that our telesales team sometimes manage to get the same SOP number as each other.

Edit 2011

Some more web references have shown up since 2009 when I wrote this originally, the reason behind this happening can be found in these references too:

SOP Master Numbers not being assigned properly

SOP Master Numbers not being assigned properly (Dynamics community site)

Alternative Get SOP master number script (stored proc)

Editing Item Description with Dynamics GP Macros

There would seem to be a problem with the Dynamic GP macros. In our GP9 installations we cannot use macros to write product descriptions over seventy two characters in length, they get truncated. My guess is that the macro engine still thinks the maximum length of descriptions is the old seventy odd characters rather than the new GP9 length of one hundred and one characters.

Anyone else found this issue? It is a real pain for us as the item managers can’t import and update hundreds of descriptions without us using integration manager or a custom VSTO excel spreadsheet.

ESX VMWare “File not Found” starting machine

Experience is something you never have when you need it. Restarting our websites’ SQL server resulted in down time when the server would not start up. It reached 95% of the start up bar on the ESX task bar in VM Infrastructure Client but terminated the start up with a dialog box “File not found” and an OK button. I had not used anything in VMware Infrastructure client other than starting stopping machines and taking a snapshot, anything else was the VM guy’s role. However we had a server down and customers getting a bad experience so some clicking around and a few Google searches got it sorted out.

Seeking a solution

Using Goole I established there was a more comprehensive log files in the same directory as the VM machine. Reading the contents of these logs, I found that the failure was during loading one of the virtual disks, as it could not find the disk file (virtual disks are just files in the ESX server file system).

The log file showed that the file that could not be found had a different path to the others of that virtual machine, as the others were loading successfully.This was the clue as to what was wrong.

 
Jun 10 10:11:10.988: vmx| DISKLIB-VMFS : "/vmfs/volumes/4908a5f1-67541468-21fa-0016357ea69b/websqlserver01VM/websqlserver01VM-000009-delta.vmdk" : open successful (23) size = 32225215488, hd = 0. Type 8
Jun 10 10:11:10.990: vmx| DISKLIB-VMFS : "/vmfs/volumes/4908a5f1-67541468-21fa-0016357ea69b/websqlserver01VM/websqlserver01VM-000006-delta.vmdk" : open successful (23) size = 32225215488, hd = 0. Type 8
Jun 10 10:11:10.992: vmx| DISKLIB-VMFS : "/vmfs/volumes/4908a5f1-67541468-21fa-0016357ea69b/websqlserver01VM/websqlserver01VM-000003-delta.vmdk" : open successful (23) size = 32225215488, hd = 0. Type 8
Jun 10 10:11:10.994: vmx| DISKLIB-LINK  : "/vmfs/volumes/4901e93d-93a8aeed-12b7-0016357ea69b/websqlserver01VM/websqlserver01VM.vmdk" : failed to open (The system cannot find the file specified).  
J

The storage section of the infrastructure client showed the location for each data store, it showed that all the files were in one data store, however the file with issues was in another data store. Opening up that data store in the infrastructure client the folder for the file causing the error had be renamed to websqlserver01VM_old. This was different to what was listed in the log file.

Solution

Renaming the file back to original directory name then allowed the machine to boot up. Sometime while the machine had been running this file’s folder must have been renamed, only coming to light on reboot when it was needed again for start-up.

At least by having to jump in at the deep end I have a much better understanding of how the ESX server runs, and I got to know what is in the Infrastructure Client well by the end of the issue having scoured it for clues to my issues.

Quick Clues for places to look

Find out where the machine is located, this is listed under the resources in the summary of the virtual machine when the machine is selected in the tree view on in VMware Infrastructure Client.

Virtual Machine's data store locations

You can double click on the data store to open it up and see the files in that data store. Navigate to the machine sub folder of interest, there should be .log files in there, get the latest one, right click download to put it on your local machine for examination in note pad.datastoreContents

While you are here, for interest, have a look at the .vmx, .vmxf, .vmsd files, check the paths in there too for clues.

You can find and confirm the data store paths on the actual ESX server by clicking the ESX server of interest in the Hosts and Clusters tree on the left hand side, then select configuration and click on the data store of interest. Hover over the Location and the path will show as a mouse over tool tip.
Datastorelocation2 

On our server the virtual hard disks are broken up into 2GB files and if you have snapshots this may result in a lot more files than you see on this example. Each file has a dash and a number showing which disk and number it is part of.

I hope this helps someone else out who may be loosing orders by the hour, you really need your ESX administrator to help you if you can.