Pages

Sunday, 14 August 2011

Making Reporting Easy With Excel

With advancement in Microsoft Office Tools, Dynamics ERPs always have new features to enjoy, In my previous posts i have discussed how Excel reports could help non technical users to extract easy information in form of Excel File from MS Dynamics GP.


Technical Users can just write SQL Queries and will be able to get results via Excel File, in this method files are automatically/manually refreshed.

To Enter SQL Query, Extract one excel file from MS DYNAMICS GP UNDER EXCEL REPORTS in order to get default connection settings. Once you get the data from default report make a backup of that report and use it every time for new reports.

To Enter SQL Query,

Within Excel  > Data Tab > Connections > [Select the Connection] Properties > Definition Tab > Enter Query in Command Text




Click ok, Click Refresh All.

Some Basic Queries you can work with,

  • SOP Transaction [ Both from Work and History Table ], this query helps you track where document exist and what's the status of the document.


SELECT 'Work' DataType,SOP10100.SOPNUMBE,
SOP10100.SOPTYPE,
SOP10100.DOCDATE,
SOP10100.CUSTNMBR,
SOP10100.CUSTNAME,
SOP10100.BACHNUMB,
SOP10100.CSTPONBR,
SOP10100.MSTRNUMB,
SOP10100.CNTCPRSN,
SOP10100.SLPRSNID,
SOP10200.ITEMNMBR,
SOP10200.ITEMDESC,
SOP10200.UNITPRCE,
SOP10200.XTNDPRCE,
SOP10200.QUANTITY,
SOP10200.LOCNCODE
FROM SOP10100
INNER JOIN SOP10200 ON SOP10100.SOPNUMBE = SOP10200.SOPNUMBE
AND sop10100.soptype = sop10200.soptype
UNION ALL
SELECT 'History' DataType ,SOP30200.SOPNUMBE,
SOP30200.SOPTYPE,
SOP30200.DOCDATE,
SOP30200.CUSTNMBR,
SOP30200.CUSTNAME,
SOP30200.BACHNUMB,
SOP30200.CSTPONBR,
SOP30200.MSTRNUMB,
SOP30200.CNTCPRSN,
SOP30200.SLPRSNID,
SOP30300.ITEMNMBR,
SOP30300.ITEMDESC,
SOP30300.UNITPRCE,
SOP30300.XTNDPRCE,
SOP30300.QUANTITY,
SOP30300.LOCNCODE
FROM SOP30200
INNER JOIN SOP30300 ON SOP30200.SOPNUMBE = SOP30300.SOPNUMBE
AND SOP30200.soptype = SOP30300.soptype



  • Excel Based Dash Board
With some basic Excel Skills you can design you own customize dashboards, look at this example.

Drop you email to get this dashboard :)


1 comment: