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

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 Navision Database Selection: C/SIDE or MS SQL Server - Overview For IT Specialist

There are certain pluses and minuses in both cases and... Read More

The Importance of Timely Timesheets

Whether you are a small consultancy firm, a medium sized... Read More

Configure Windows Indexing Service for Performance

The Windows Indexing Service provides you with the ability to... Read More

Begun, The Browser Wars Have

As Mozilla Firefox nears 10% market share, with well over... Read More

It?s Back!! WordPerfect?s Amazing Comeback

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

Running a Program on a Remote Server Using SSH

How do you run a program on a remote server... Read More

Microsoft Great Plains Beverage Production & Distribution ? Implementation & Customization Highlight

Microsoft Business Solutions Great Plains has many years of successful... Read More

You Can Write Microsoft Office Program in About 60 Minutes

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

Linux Dual Boot-How To Format

Just the thought of a duel-boot scares many people away,... Read More

The Tools and Features of Adobe Photoshop

Adobe Photoshop is one of the world's leading graphics editing... Read More

IT Strategy for Midsize Business: Microsoft vs. Java, Great Plains & CRM

Midsize business or non-profit organization should decide if one-vendor solution... Read More

Microsoft Great Plains Implementation for Large Corporation ? Overview for VP IT

Microsoft Business Solutions Great Plains was historically designed to serve... Read More

IT Strategy for Large Corporation: ERP/MRP/CRM, Unix/Linux/Windows, Microsoft/Java

Combining Microsoft Business Solutions Great Plains ERP with non-Microsoft Business... Read More

eStore Advantage ? Extending Microsoft eConnect for MBS Great Plains

eStore Advantage allows front-office applications to communicate with back-office business... Read More

How To Create A Data Capture Procedure Checklist For Your Small Business CRM Software

Fortunately one of the most common reasons cited for the... Read More

How the Firefox: How to... Manual Helped Me

A few months back I really got sick of my... Read More

Microsoft Great Plains Integration with Legacy Systems ? Overview For Developer

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

Microsoft Great Plains Installation ? Overview for IT Director/Controller

Microsoft Great Plains is main mid-market application from Microsoft Business... Read More

A Guide To Purchasing Professional XP Icons Online And Enhancing Your Applications

Icons are used everywhere; right from software applications, to internet... Read More

Alien Intruders!

You probably didn't casually invite, or extend a formal attendance... Read More

Four Easy Ways To Prevent Spyware

How would you like to prevent spyware and adware from... Read More

Spyware Protection: A Must for Any Home Computer

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

Information Products: A Business Owners Best Friend

We live in a post-industrial age where information is the... Read More

Upgrading Great Plains Dexterity Customization ? switching to new technologies: SQL, Crystal, eConne

1. Replace Dexterity cursor with SQL Stored Procedure Dexterity... Read More

Your Computer May Be Infected, Heres How To Check (NOT about virus)

NOTE: Please take time to read on - it may... Read More

How to Build a Basic CSS Layout

Designing without tables by using CSS layouts is fast becoming... Read More

Selecting Corporate ERP: Microsoft Business Solutions Great Plains ? Estimation Parameters

As we could imagine, if you are reading this article... Read More

Software Piracy - Global Increase

Pirated software is on the increase and now accounts for... Read More

Recent Studies Show that 9 out of 10 PCs Are Infected with Spyware

Spyware and malware are large problems for Internet users today... Read More

Microsoft Great Plains - Microsoft RMS Integration ? overview

Microsoft Great Plains and Microsoft Retail Management System (Microsoft RMS)... Read More

Unwanted Files

A LOT OF UNWANTED FILES.When you uninstall an item of... Read More

Microsoft Small Business Manager eCommerce ? Overview

Microsoft Business Solutions Small Business Manager is scaled down Great... Read More

Removing Incoming Email in MS Exchange, C# Example

The purpose of one of our projects was MS Exchange... Read More