Maintain your old Siemens Hipath system

Navision Attain C/ODBC Crystal Report ? Customization Example

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


SELECT * FROM OPENQUERY(NAVISION, 'SELECT * FROM Sales_Line sls, Customer cust WHERE sls.Sell_to_Customer_No_ = cust.No_')


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!

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 (, he is Microsoft CRM SDK, Navision, C#, VB.Net, SQL, Oracle, Unix developer.

In The News:

Warning: simplexml_load_file() [function.simplexml-load-file]: parser error : Document is empty in /home/infob/public_html/includes/rss.php on line 2

Warning: Invalid argument supplied for foreach() in /home/infob/public_html/includes/rss.php on line 9

Erfahrungen mit Pallhuber Wein
Agen Bola SBOBET Terpercaya

Travel in comfort and at your leisure with CT Airlink Limousine & Car Service for top quality private transportation and exceptional customer service. We operate Sedans, SUVs & Vans for CT Car Services to covering all Connecticut airports including Car Service from CT to Newark Airport , Mohegan Casino Uncasville CT, Foxwoods Casino Mashantucket CT, Manhattan Cruise Terminal NYC, Brooklyn Cruise Terminal NYC and Bayonne Cruise Terminal NJ. CT Airlink hire licensed and friendly chauffeurs who have in-depth knowledge of the Areas.

Microsoft Great Plains Implementation in Russia ? Overview for Consultant

Microsoft Business Solutions Great Plains is very popular ERP platform... Read More

Know-how in Microsoft Publisher

For those who still don't know, Microsoft Publisher helps computer... Read More

Free Software: How Not To Get More Than You Bargained For!

I completed an experiment recently. I wanted to find out... Read More

Instant Messenger Clients

If you have been using the Internet for any amount... Read More

Microsoft Great Plains: Manufacturing or Bill of Materials - Overview for IT Specialist

Microsoft Great Plains is main Microsoft Business Solutions product, targeted... Read More

XML Parser and Their Types

XML parser is a software module to read documents and... Read More

New SQL Delta Version 3.1

COMMAND LINE FUNCTIONA powerful command line script processor has been... Read More

Microsoft Axapta, Navision or Great Plains: ERP Selection for Large Corporation

If you would like to pick something from Microsoft, or... Read More

Microsoft Great Plains GL: General Ledger ? Overview For Consultant

Microsoft Business Solutions Great Plains is marketed for mid-size companies... Read More

It?s Back!! WordPerfect?s Amazing Comeback

Have you noticed WordPerfect is gearing up for a comeback... Read More

Are You Waiting for Microsoft Longhorn Operating System

I love new technology. I am still ready to wait... Read More

Google Brings the Earth to Your Desktop

Google Inc. has launched a new software package that allows... Read More

Software Development in 2005 - Back to the Future

2005 ? Back to the Future.What does the future hold?... Read More

You Can Write Microsoft Office Program in About 60 Minutes

Microsoft Office program is a programming tool called Visual Basic... Read More

Microsoft Great Plains Reporting ? Overview for Developer

Looks like Microsoft Great Plains becomes more and more... Read More

Is Your Small Business Ready For A CRM Software Solution?

I have yet to see a business that, sometimes in... Read More

Microsoft Great Plains Integrations - Tips for Developer

In this short FAQ style article we would like to... Read More

Create A Flash Presentation For Free With Open Office

The intentions of this short tutorial are not to teach... Read More

10 Things You Could be Using Photoshop For, But Probably Arent

Most people don't use Photoshop to its fullest capabilities. Here... Read More

C++ Tutorial 2, Input and Variables

This is the tutorial where we really get into programming.... Read More

Great Plains Dexterity: Customizations & Source Code Programming

Great Plains Software Dynamics, Dynamics C/S+, eEnterprise were written on... Read More

Story Development Software: Good or Evil?

In the early days of the personal computer, we're talking... Read More

Spyware Protection: A Must for Any Home Computer

Now that spyware is the single most dangerous threat to... Read More

Spyware, Adware, etc. -- Terms and Common Sense

When reading an article where some term is used often,... Read More

Freight Forwarding ERP: Microsoft Business Solutions Great Plains customization & setup ? overview

Microsoft Great Plains may be recommended for international freight forwarding... Read More