Released on = June 14, 2007, 3:58 pm

Press Release Author = Alba Spectrum Group

Industry = Computers

Press Release Summary = In this small article we will try to orient you on how to
create cross-product reports, based on SQL view or stored procedure, where data is
pulled from Microsoft Great Plains as well as Microsoft Business Portal tables.
This query architecture should open for you report designer or programmer the way to
create cross-product report

Press Release Body = Alba Spectrum Group, http://www.albaspectrum.com
help@albaspectrum.com 1-866-528-0577, 1-630-961-5918

In this small article we will try to orient you on how to create cross-product
reports, based on SQL view or stored procedure, where data is pulled from Microsoft
Great Plains as well as Microsoft Business Portal tables. This query architecture
should open for you report designer or programmer the way to create cross-product
report. Good example would be the following - life cycle of purchase request, which
originates in Business Portal Requisition Management module and then, when approved
is moved to Purchase Order in Microsoft Dynamics GP purchase order processing
module. The reporting tool selection in our opinion is the secondary question -
both popular tools: Crystal Reports and Microsoft SQL Server Reporting Services
(SRS) will do the job as soon as you could trust your view or stored procedure.
Let's try to create cross-product query in Requisition Management linking to GP
Purchase Order Processing
. ReqMgmtDocument table. This table stores all your purchase requests created,
submitted and approved in BP Requisition Management module, also you should be aware
that this table resides in DYNAMICS database and so it has CompanyID field. You
should compare company ID with GP SY01500 table, which also resides in Dynamics
database, but it is Microsoft Dexterity table and it is within GP architecture
realm.
. POP tables. In Microsoft Dynamics GP you should know the posting workflow. For
the most of transaction types GP is batch processing system, however Purchase Order
is an exception - it has statuses and PO lifecycle is the change of statuses.
However when you receive against purchase order - purchase receipt transaction is
batch oriented and you can post either individual transaction (in this case the
batch number will be your user id) or the whole butch upon the approval.
Tools->Resource Description->Tables in GOP workstation will give you the names of
the tables in POP, if you switch series to Purchasing. More likely you will need to
research the following tables: POP10100 - Purchase Order Work, POP30100 - Purchase
Order History (it goes to history when all the lines are received), POP10300 -
Purchase Receipt Work, POP30300 - Purchase Receipt History
. SQL View or Stored Procs creation. As you can mention, both products - Microsoft
Great Plains and Business Portal for GP reside in the same SQL server and even share
former Microsoft Great Plains databases: Dynamics and company DB. This makes view
or sp creation and easy task, as you don't really need to create something like
linked server and pass it as a parameter to the query

Andrew Karasev, Alba Spectrum Group, http://www.albaspectrum.com
help@albaspectrum.com 1-866-528-0577, 1-630-961-5918, serving Microsoft Dynamics GP
USA and Canada nationwide: Toronto, Montreal, Vancouver, Calgary, Windsor, New York,
San Francisco, Los Angeles, Miami, Orlando, Pittsburg, Toledo, Indianapolis,
Detroit, Philadelphia, Boston, Washington, Seattle, San Diego, Phoenix, Austin.
Local service is available in Chicago and Houston metros: Rosenberg, Katy, Richmond,
Dallas, Galveston, Sugar Land, Naperville, Wheaton, Plainfield, Aurora, Wheaton,
Downers Grove, Lisle, Oakbrook, Morris, Marseilles, Joliet, Montgomery, Oswego,
DeKalb. With reasonable travel we serve Springfield, Bloomington, Normal, Peoria,
LaSalle, Ottawa, Dixon




Web Site = http://www.albaspectrum.com

Contact Details =
Alba Spectrum Group, http://www.albaspectrum.com help@albaspectrum.com
1-866-528-0577, 1-630-961-5918