Microsoft Business Solutions Navision is main ERP application for European, Brazilian markets plus it serves vertical segments of the USA market, such as light manufacturing, POS, CRM. As our experience indicates ? Navision implementation requires more customization and tuning in comparison to another mid-market ERP solution from MBS: Microsoft Great Plains. Our goal is to popularize Microsoft Business Solutions products and enlighten IT people to do customize Navision in-house.
Today, the main topic of this article is working with native C/SIDE Navision Attain database via C/ODBC interface, in particular ? Linked Server creation in Microsoft SQL Server 2000 environment and then Sales report design in Crystal Reports ver. 10. Let's begin:
1. We will use Navision Attain 3.6 with Navision Database Server, Navision ApplicationServer and Navision Client. These component are installed on Windows XP Professional. To enable C/ODBC interface you need to install this component from Navision Attain CD.
2. Let's create ODBC DSN for connection to Navision database: Start -> Control Panel -> Administrative Tools -> Data Sources (ODBC), then switch to System DSN and click Add. We will use C/ODBC 32-bit driver. Give Data Source Name Navision, Connection ? leave it Local. As the database (Database button) select Program FilesNavision AttainClientdatabase.fdb (this is demo DB). Then press Company button ? in our case we use CRONUS demo database. Important conditions to provide proper connection with C/SIDE database are correct options for C/ODBC. Click Options button and look at the options available ? we will pay attention to Identifiers screen ? this is where you setup identifiers to be transferred to client application. We will use these types: "a-z,A-Z,0-9,_" to make MS SQL Server 2000 work correct with C/ODBC source. We are now done with ODBC DSN. Lets setup Linked Server
3. Open SQL Server Enterprise Manager. Open object browser on the left side, select Security and Linked Servers. With right click select New Linked Server in the context menu. In the emerging dialog window, in the menu Provider Name select Microsoft OLE DB Provider for ODBC Drivers. Let's name our Linked Server as NAVISION. In the Data Source string type in ODBC DSN name ? NAVISION in our case. Linked Server is ready. Select tables list and we now see Navision Attain tables. 4. Now we need to create small procedure for Sales data extraction. Here is the text of the procedure: CREATE PROCEDURE NavisionSalesReport AS
DBCC TRACEON(8765)
SELECT * FROM OPENQUERY(NAVISION, 'SELECT * FROM Sales_Line sls, Customer cust WHERE sls.Sell_to_Customer_No_ = cust.No_')
RETURN
GO
To clarify its text: TRACEON(8765) directive allows working with the data results of variable length returned by C/ODBC driver. If we don't use this directive ? we can't pull the results from Navision tables ? we will get errors, like this:
OLE DB error trace [Non-interface error: Unexpected data length returned for the column: ProviderName='MSDASQL', TableName='[MSDASQL]', ColumnName='Ship_to_Filter', ExpectedLength='250', ReturnedLength='1']. Server: Msg 7347, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' returned an unexpected data length for the fixed-length column '[MSDASQL].Ship_to_Filter'. The expected data length is 250, while the returned data length is 1.
OPENQUERY statement opens linked server and transfers its SQL command, and in its turn returns the result set. To test the procedure type in this command in SQL Server Query Analyzer: EXEC NavisionSalesReport 5. Now ? launch Crystal Reports, use standard Wizard, create new OLE DB (ADO) connection with our MS SQL 2000 Server and select NavisionSalesReport procedure in the list. As the report fields we can use Description, Quantity, Line_Amount and Discount_Amount fields. You can group by name Name ? this is customer name. Our report is now ready! Happy customizing, implementing and modifying! If you want us to do the job - give us a call 1-866-528-0577 or 1-630-961-5918! help@albaspectrum.com
Boris Makushkin is Lead Software Developer in Alba Spectrum Technologies ? USA nationwide Microsoft CRM, Microsoft Great Plains customization company, serving Chicago, Boston, San Francisco, San Diego, Los Angeles, Houston, Dallas, Atlanta, Miami, Montreal, Toronto, Vancouver, Moscow, Europe and internationally (http://www.albaspectrum.com), he is Microsoft CRM SDK, Navision, C#, VB.Net, SQL, Oracle, Unix developer.
Microsoft PowerPoint has dramatically changed the way in which academic... Read More
Microsoft Business Solutions Great Plains version 8.5, 8.0, 7.5, Great... Read More
We are in a transition phase in the Managerial Administration... Read More
While Adobe is the most known maker of PDF tools,... Read More
Our hosted solution allow you to run your own search... Read More
Microsoft Great Plains is now targeting large and midsize businesses... Read More
Microsoft Business Solutions ? Great Plains is designed to meet... Read More
Collaboration SoftwareCollaboration Software, also known as group collaboration software or... Read More
Microsoft Business Solutions Great Plains is marketed for mid-size companies... Read More
Microsoft CRM is new player on the CRM software... Read More
People often ask me: What image file formats will Photoshop... Read More
Before being able to choose a secure Internet communication system,... Read More
Handling character strings in Java is supported through two final... Read More
Microsoft has never released a service pack for Windows98 SE,... Read More
Many Webmasters have never bothered to view their website's server... Read More
The COSMIC FP (function point) software quality metric, is no... Read More
Usually workflow & messaging is realized in CRM and then... Read More
I have recently created my first Php program. I wanted... Read More
Microsoft Business Solutions CRM is now approaching the phase of... Read More
SAP Inc., a global leader in client/server enterprise application software... Read More
One day, you suddenly realize that your computer started to... Read More
The term "document management" and "paperless office" is the subject... Read More
ERP is the acronym of Enterprise Resource Planning. Multi-module ERP... Read More
Writing software manuals is boring, isn't it? We often think:... Read More
If you have Microsoft Great Plains and support it for... Read More
If you have Microsoft Great Plains as main accounting and... Read More
The intuitive algorithm.Roger Penrose considered it impossible. Thinking could never... Read More
What is a Web Database?A web database is a database... Read More
With so many Microsoft Windows related viruses, errors, and other... Read More
Microsoft Business Solutions Great Plains has substantial market share and... Read More
Itâ??s easy to understand why you might be drawn to... Read More
Shareware is software that you can try before you buy;... Read More
Icons are used everywhere; right from software applications, to internet... Read More
Microsoft CRM is now on the scene and it is... Read More
Spyware and Adware infest over 90 percent of computers in... Read More
If you would like to pick something from Microsoft, or... Read More
In 2004 Oracle, Inc. made its new step toward J2EE... Read More
Hi, Guys,I believe a lot of programmers are trying to... Read More
Once upon a time not so long ago, there was... Read More
The world of small business accounting software can be a... Read More
Program Flow is what you think it is. How the... Read More
Microsoft Great Plains fits to majority of horizontals and retail... Read More
Just when you thought you were Web savvy, one more... Read More
The first topic we are going to discuss... Read More
The title of "software engineer" has got to be among... Read More
User interfaces and accessibility are some of the most important... Read More
Looks like Microsoft Great Plains becomes more and more... Read More
Microsoft Great Plains is now targeting large and midsize businesses... Read More
Microsoft Business Solutions CRM data conversion deserves FAQ type of... Read More
Microsoft Great Plains might be considered as ERP platform to... Read More
Configuring PPP PAP AuthenticationNow we know how the ISDN link... Read More
Viruses and spyware usually show up on your computer one... Read More
As you probably know, when Microsoft purchased Great Plains Software... Read More
Introduction: The creating of a computer program involves a number... Read More
Rapid Application Development (RAD) is a software development methodology. In... Read More
Siebel is traditional CRM market leader, however and mostly due... Read More
Creating a new markup language.Introduction.General Reuse Markup Langauge, or GRML,... Read More
Sales are all about leverage, because there is only so... Read More
All your software is stored on a hard-drive. But how... Read More
If you've been using MySQL database to store your important... Read More
What is Software?Software is a set of instruction written to... Read More
People often ask me: What image file formats will Photoshop... Read More
Microsoft PowerPoint has dramatically changed the way in which academic... Read More
The research in the field of Natural Language Processing usually... Read More
Itâ??s easy to understand why you might be drawn to... Read More
Microsoft has never released a service pack for Windows98 SE,... Read More
Software |