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.

---

Great Plains Dexterity Programming ? Overview For Developer

Looks like Microsoft Great Plains becomes more and more popular,... 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

What to Do if All Screensavers Fun is Grayed Out?

Finally, you have some time to personalize your desktop with... Read More

Microsoft Great Plains RW: Report Writer

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

Linux Secrets

The first thing that you will notice about Linux Red... Read More

RSS: Get Notified When Your Favorite Websites Are Updated

RSS (Really Simple Syndication) is a way for a site... Read More

How to Tell You Have Spyware, Ad-ware or Viruses

Usually, the easiest way to tell you have spyware is... Read More

Microsoft Great Plains implementation: Restaurants Supply Chain Management Example

Microsoft Great Plains serves majority of US based horizontal and... Read More

PHP On-The-Fly!

IntroductionPHP can be used for a lot of different things,... Read More

Linux Dual Boot-How To Format

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

How Do I Get Rid Of That Darn Spyware?

Ad-Aware and Spybot are probably the two most well known... Read More

Brief Notes About Mainframe Software Pricing

We don't think about mainframe software pricing anymore, we just... Read More

Microsoft CRM Implementation & Remote Support

We would like to give you pluses and minuses of... Read More

How To Make Good Use of Spreadsheets

Most computer users use spreadsheets software such as Microsoft Excel... Read More

Software Engineering: An Introduction

Software Engineering is the Systematic Approach for analysis design implementation... Read More

Windows Screensavers Explained

In this article you will find some background information about... Read More

SyncUp ? A File/Folder Synchronizer For Windows

SyncUp, a file synchronizer is designed to assist the home... Read More

Microsoft Business Solutions Customization Options - Overview for Programmer

Several years ago Microsoft purchased Great Plains Software, then Navision... Read More

SQL: Querying Microsoft Great Plains ? Overview for Database Administrator/Developer

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

Reporting for Microsoft Great Plains/Dynamics/eEnterprise: RW ? ReportWriter ? Tips for Developer

Microsoft Business Solutions Great Plains is written in Great Plains... Read More

Should I Use Windows Update?

Should one use Windows Update?This topic has good and valid... Read More

Quick Summary of Basic and Common Linux Commands

There are many commands that are used in linux on... Read More

Crystal Reports - Geterogeneus databases: SQL Server, Oracle, Unidata, Btrieve ? Tips For Developer

Crystal Reports is the most flexible tool on the market... Read More

.NET :Solving the Multiple Inheritance Issue Under .NET Platform

.NET platform does not support multiple inheritance. Do not confuse... Read More

Microsoft CRM Messaging through Lotus Domino eMail Server - Balanced Solution

Microsoft CRM and IBM Lotus Notes Domino seem to be... Read More

Dreamweaver vs FrontPage

There are two major WYSIWYG(What You See Is What You... Read More

When is a Software Engineer Not a Software Engineer?

The title of "software engineer" has got to be among... Read More

Call Alert Notifications - Free Answering Machine Software for PCs

If you're online using a dialup Internet connection, you'll probably... Read More

Free Software - Powerful Alternatives to Budget-Busting Software

When you buy a computer, it most likely comes with... Read More

The Bluebird Project

The objective for Zandi Digital is to make available clever... Read More

Lotus Notes Domino and Microsoft CRM Integration

Well, even if the combination might look very unusual, we... 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

Microsoft CRM Integration with IBM Lotus Notes Domino ? Machinery Dealership Example

IBM Lotus Notes with Domino email server is traditional document... Read More