Dynamically generating Excel files

Price list download

Recently the need to provide a link to download prices from our corporate website arose. Generally I like to keep things simple, so the obvious thing to do was to squirt the data into a csv file in to the response stream to the browser. However there was a desire to include other elements for Microsoft Excel users, like the “=HYPERLINK(“http://www.timwappat.info/”,”Tims site”)” type formulas.

I checked out so see the discussions on Stackoverflow, the view I derived from there was that SpreadsheetML was the way forward as a download format. I also kept in the csv for those users with older software.

SpreadsheetML

This is the XML format used by excel. The excel document is broken up into multiple xml files that normalise the data and make it possible for millions of cells to be held in a sensible manner. The collection of files are then zipped up and given the xlsx file extension. It is this archive that becomes the spreadsheet excel “file”.

Microsoft have released a SDK that allows developers to work with the xml files. The SDK can be downloaded from here: Open XML Format SDK 2.0

By referencing the .dll files in the SDK in your Visual Studio 2008+ project, the open XML namespaces become available to you. A good introduction is here: Dive into SpreadsheetML (Part 1 of 2)

Structure of XML using Microsoft XML Notepad:

Excel XML structure

You can see from the screen shot the structure drilling down to an individual text cell.

Create the template

To create a template file to make things a little easier. This is done by creating an Excel document that is formatted and contains everything you need bar the data you wish to manipulate. This all could be done by using code, but easier to just use Excel.

Creating Documents by Using the Open XML Format SDK 2.0 (Part 1 of 3)

The above link is useful to get a introduction to basic manipulation of the documents. Once installed the SDK also has a help file containing some good examples of basic and medium level interactions with documents.

OpenXMLSDKHelp

Populate the template document

Microsoft are promoting LINQ by using it as the data technology stack behind the OPEN XML examples they provide. The code is quite readable, working much as you would expect.
There is not much online regarding the creating of formulas in sheets, but it turned out quite easy as you can see in the code. One issue that seems to affect a lot of people is how to get any formula you enter to recalculate on opening the sheet. This turns out to be done by ensuring the VALUE of the formula cell is null/nothing. If a value already exists then the sheet will use that cached value else a refresh of the formula occurs.

Private Function CreateFormulaCell(ByVal Header As String, ByVal value As String, ByVal Index As Integer) As Cell
    Dim newCell As Cell = New Cell(New CellFormula(value), Nothing)
    newCell.CellReference = Header & Index
    Return newCell
End Function

Product Categories and TSQL

I’ve just spent a few hours this morning working on a drop down menu for the website. It works a little like the Microsoft Vista file browser address bar. Any part of the breadcrumb type trail can be clicked on to drop down the other categories at that level.

Since I’ve upgraded the website SQL server earlier in the year from 2000 to 2008 version of SQL server I now can utilise Common Table Expressions to get the solution (CTE). Used in a recursive manner you can do all sorts that needed cursors before – great stuff!

Here was my resulting script before packaging into a  stored procedure:

 

-- Script to get all the categories from a given category up over 
-- includes a mark against the path we are on and other categories at that level.
-- T.WAPPAT 20th July 2009
---
DECLARE @CatID Integer
SET @CatID=22141
WITH CategoryParents(CatID, ParentCatID, SortInLevel, 
                            CatLangCode, CatDesc, InPath,  Depth)
AS
(
--Anchor member
SELECT    CategoriesSibs.CatID as CatID, CategoriesSibs.ParentCatID,
        CategoriesSibs.SortInLevel, CategoriesDesc.CatLangCode, 
        CategoriesDesc.CatDesc, 
        CASE CategoriesSibs.CatID WHEN @CatID 
            THEN 1 ELSE 0 END as InPath,
        0 as Depth
FROM  MA_Categories AS Categories WITH (NOLOCK)   
      JOIN 
      MA_Categories as CategoriesChild WITH (NOLOCK) 
        ON CategoriesChild.ParentCatID=Categories.CatID
      JOIN 
      MA_Categories as CategoriesSibs WITH (NOLOCK) 
        ON CategoriesSibs.ParentCatID=Categories.CatID
      JOIN
      MA_CategoryDescs AS CategoriesDesc WITH (NOLOCK) 
        ON CategoriesDesc.CatID = CategoriesSibs.CatID 
WHERE CategoriesChild.CatID=@CatID
UNION ALL
--Recursive member
select  Categories.CatID, Categories.ParentCatID, 
        CategoriesSibs.SortInLevel, CategoriesDesc.CatLangCode,
        CategoriesDesc.CatDesc, 
        CASE CategoriesSibs.CatID WHEN CategoryPArents.ParentCatID 
            THEN 1 ELSE 0 END as InPath,
        Depth+1 as Depth
FROM         CategoryParents AS CategoryParents 
             JOIN 
             MA_Categories AS Categories WITH (NOLOCK) 
                ON CategoryParents.ParentCatID=Categories.CatID  
             JOIN
             MA_Categories as CategoriesSibs WITH (NOLOCK) 
                ON CategoriesSibs.ParentCatID=Categories.ParentCatID
                JOIN
             MA_CategoryDescs AS CategoriesDesc WITH (NOLOCK) 
                ON CategoriesDesc.CatID = CategoriesSibs.CatID  
)
-- execute CTE
select  distinct * from CategoryParents  ORDER BY Depth, SortinLevel

 

You may find this useful to hack around for your own purposes. The following are table definitions.

CREATE TABLE [dbo].[MA_Categories](
    [CatID] [int] NULL,
    [ParentCatID] [int] NULL,
    [SortInLevel] [int] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[MA_CategoryDescs](
    [CatID] [int] NULL,
    [CatLangCode] [varchar](3) NULL,
    [CatDesc] [varchar](200) NULL
) ON [PRIMARY]

Selling inventory items from SOP in another GP company

WarehousePicToday we started thinking about ways that we could sell items from one company's inventory using another company's SOP module in Dynamics GP.

Problem outlined

We have two mail order companies selling, for example pet care items and a DIY store selling tools. We have a desire to offer some of the overlapping items to both companies putting sections in the websites and printed catalogues of items from the other company. All the items are held in the same physical real warehouse but are held in the respective company’s inventory module in GP. Both companies are on the same instance of SQL server.

Example: Some DIY customers might like to buy a rabbit hutch as it is sort of on the edge of that stores proposition. The pet store might want to offer small tool kits to help with fitting cat flaps etc.

Ideas

We have thought of creating a inventory enquiry screen in SOP document entry of GP that looks at a union of the two inventories to bring back stock levels and details. Thus the sales staff can see the stock no matter which company it is in.

To raise a sales order for a customer users  could enter a non-inventoried item into SOP as a drop ship order against the other company as the supplier.

We could then use eConnect to create a purchase order and sales order in the other company for the items that were ordered. This integration would have to also handle if items are cancelled or quantities changed on the order -hmmm.

Fulfilment

We have a custom solution in our warehouse that I wrote that handles fulfilment. This could produce a consolidated pick list across the two companies for orders that have been created this way. Users could fulfil both the drop ship sales order and the normal sales order when the consolidated pick list is fulfilled. Thus to the customer the fact that two different companies are fulfilling the order would be transparent.

What do you think?

Have you done something similar before, or got other ideas/products that would help, if so leave a comment…

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.