We will show you how to create cross product reports using SQL views and stored procedures. The data for the report is taken from Microsoft Great Plains tables as well as Microsoft Business Portal. This query architecture is meant to show the report designer or programmer how to create cross product reports. A good example is the life cycle purchase request which starts in Business Portal Requisition Management and when approved moves to Microsoft Dynamics GP’s purchase order processing module. We believe that the reporting tool is secondary – Crystal Reports, Microsoft SQL Server Reporting Services and Microsoft SQL Server Analysis Services (SRS), will both do the job once you can trust your stored procedure or views. We will try to create a cross product query in Requisition Management that links to GP Order Processing www.think-tribe.com/.

ReqMgmtDocument is a table that stores all your purchase requests created, submitted and approved in BP Requisition Management module. This table holds all purchase requests submitted and approved by BP Requisition Management. It is also stored in DYNAMICS and has a CompanyID column. Comparing company ID with GP SY01500 is a good idea. It is also residing in Dynamics database. However, it is a Microsoft Dexterity and GP architecture table.

o POP Tables. Microsoft Dynamics GP has a posting workflow that you need to be familiar with. GP is a batch processing system for the majority of transaction types. Purchase Orders, however, are an exception – they have statuses. PO lifecycle refers to the changing of statuses. Purchase receipts are batch oriented. You may post an individual transaction or the whole batch after approval. The tables are listed under Tools->Resource Definition->Tables on GOP workstation if you change the series to Purchasing. Most likely, you’ll need to search for the following tables: Purchase Order Work POP10100, Purchase Order History POP30100 (the table becomes history when it receives all the lines), Purchase Receipt History POP10300, Purchase Receipt History POP30300.

SQL View or StoredProcs creation. Microsoft Great Plains, Business Portal for GP and Microsoft Great Plains share the same SQL server. They even use the old Microsoft Great Plains databases Dynamics DB and Company DB. This makes creating views or sps easy, as you don’t need to create linked server and then pass it to the query.