| Home | Free Articles for Your Site | Submit an Article | Advertise | Link to Us | Search | Contact Us |


    SEARCH ARTICLES
    Custom Search




vertical line

Microsoft Great Plains SQL Reporting and data fixing

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

Submitted by:

Andrew Karasev

Andrew Karasev is Chief Technology Officer in Alba Spectrum Technologies USA nationwide Microsoft Great Plains, Microsoft CRM, Microsoft RMS customization company, serving clients 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.

help@albaspectrum.com





ARTICLE CATEGORIES

Auto and Trucks
Business and Finance
Computers and Internet
Education
Family
Finances
Food and Drink
Gadgets and Gizmos
Health
Hobbies
Home Improvement
Humor
Kids and Teens
Legal
Marketing
Men
Music and Movies
Online Business
Parenting
Pets and Animals
Politics and Government
Recreation and Sports
Relationships
Religion and Faith
Self Improvement
Site Promotion
Travel and Leisure
Web Development
Women
Writing



http://www.articlesurfing.org/computers_and_internet/microsoft_great_plains_sql_reporting_and_data_fixing.html
Copyright © 1995-2009 Photius Coutsoukis (All Rights Reserved).