| |
Microsoft Great Plains SQL Reporting and data fixing
By Andrew Karasev
This is beginner level SQL
scripting article for DB Administrator, Programmer, IT Specialist
Our and Microsoft Business Solutions goal here is to educate database
administrator, programmer, software developer to enable them support Microsoft
Great Plains for their companies. In our opinion self support is the goal of
Microsoft to facilitate implementation of its products: Great Plains, Navision,
Solomon, Microsoft CRM. You can do it for your company, being aware on simple
data repair techniques and appealing to Microsoft Business Solutions
Techknowledge database. This will allow you to avoid expensive consultants
visits onsite. You only need the help from professional when you plan on complex
customization, interface or integration, then you can appeal to somebody who
specializes in these tasks and can do inexpensive nation-wide remote support for
you.
Let's look at simple data repair techniques.
1. How to delete the user:
If user, shuts down computer, before signing off Great Plains - she/he will get
the message that she/he is already signed. Then you need to delete the user from
the activity table. Run the query below against DYNAMICS database - it will
unlock the user:
delete ACTIVITY where USERID='MARIE'
2. How to unlock the batch:
From our experience - this is second FAQ we are hearing from Great Plains
administrator. Run this script against company database to unlock hanging batch:
update SY00500 set BCHSTTUS=0 where BACHNUMB = 'BATCH0023'
Sometimes you also have to run this script against DYNAMICS database (if first
script doesn't do the job)
delete SY00800 where BACHNUMB = 'BATCH0023'
3. Some Reporting Queries – Before we go there, we would like you to be
able to find the table with the data you are looking for. Launch Great Plains
and go to Tools->Resource Description->Tables. Find the table in the proper
series. If you are looking for the customers – it should be RM00101 – customer
master file.
Now lets query Customer Master table:
select CUSTNMBR, CUSTNAME from RM00101 where STATE='CA'
This query will return Customer Number, Customer Name for all the customers in
California
Next query will give you top 10 Inventory Items with the best Sales Results
select a.ITEMNMBR, a.ITEMDESC, b.SALESNUMBER from IV00101 a join (select top 10
ITEMNMBR, count(ITEMNMBR) as SALESNUMBER from SOP30300 where SOPTYPE=3 group by
ITEMNMBR order by count(ITEMNMBR) desc) b on a.ITEMNMBR=b.ITEMNMBR
Happy querying and supporting! if you want us to do the help you - give us a
call 1-866-528-0577!
help@albaspectrum.com
About the Author
Andrew Karasev is Chief Technology Officer in Alba Spectrum Technologies – USA
nationwide Microsoft Great Plains, Microsoft CRM, Microsoft RMS customization
company, based in Chicago, Los Angeles, San Francisco, San Diego, Boston, New
York, Houston, Dallas, Miami, Atlanta, Minneapolis, Seattle, Canada, UK,
Australia, Belgium and having locations in multiple states and internationally (
http://www.albaspectrum.com ), he is
Great Plains Dexterity, SQL, C#.Net, Crystal Reports and Microsoft CRM SDK
developer.
Article Source: http://www.simplysearch4it.com/article/1780.html
| If you wish to add the above article to your website or newsletters then please include the "Article Source: http://www.simplysearch4it.com/article/1780.html" as shown above and make it hyperlinked. |
| Some other articles by Andrew Karasev | SAP Business One Implementation for Multinational Companies with subsidiaries in Brazil
SAP B1 has over 200 installations in Brazil at this time and the number of new customers is growing. In this article we would like ...
SAP Business One in Illinois: implementation, upgrade, customization, integration
Chicago metropolitan area and downstate Illinois might be characterized as mid-west distribution place. This means that we see large number of diversified mid-size businesses, engaged in light assembly, shipping and receiving and distribution to their wholesale ...
SAP Business One Chicago Market Review, plus customization, integration & data conversion
SAP B1 is becoming more popular in the USA, increases number of implementation each year substantially. Originally designed by one of ...
Microsoft Dynamics GP Office Stack Integration advances –
Microsoft Great Plains Dynamics GP is on the way of Microsoft Dynamics or former Microsoft Project Green advances. Great Plains Dexterity is still a core of fat ...
eConnect and Microsoft Dexterity: Dynamics GP development tools comparison notes
This article is dedicated to Microsoft Great Plains eCommerce and web software development primarily, plus Microsoft Dynamics GP customization, integration, data conversion and reporting. As software programmer you should know where one customization realm ...
Microsoft Dynamics GP Dexterity Customization
Microsoft Great Plains software development, customizations, reporting. Microsoft Dexterity programming as reliable alternative to newer technologies and platforms, such as .Net, C# and VB MS Visual Studio development, eConnect, ...
|
|
| |
|
