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.

---

The Dirt on Screensavers

Remember back in the days where screensavers were the coolest... 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

Microsoft CRM Customization Secrets ? Second Edition

This article is for advanced Microsoft CRM SDK C# developers.... Read More

Does Microsoft have any real competition?

Does Microsoft Have any Real Competition? Copyright (c) 2003 Gregory... Read More

Microsoft CRM in Latin America: Implementation, Customization, Support ? Overview for Consultant

Microsoft Business Solutions CRM is present several years on the... Read More

Where to Find Free Fleet Maintenance Software

Costs of fleet maintenance software can vary widely. It is... Read More

International Support - Microsoft Great Plains VAR/Partner Selection ? Overview for V.P. IT

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

Daffodil DB: Web Database

What is a Web Database?A web database is a database... Read More

Causes of ERP Failures

ERP is the acronym of Enterprise Resource Planning. Multi-module ERP... Read More

Making The Decision To Automate Your Software Testing

Not every software testing project can or should be automated.... Read More

Microsoft Great Plains international implementation ? USA / Mexico ? overview for consultant

Microsoft Business Solutions Great Plains was purchased from Great Plains... Read More

Online PowerPoint Presentation ? Convert PowerPoint to Flash

Although we don't know whether Microsoft ever envisioned such a... Read More

Microsoft Great Plains version 8.5: Upgrade, Customization, VBA, Crystal Reports - Highlights

Microsoft Great Plains is one of the Microsoft Business Solutions... Read More

Reporting Tools for Microsoft Great Plains ? Overview For Developers

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

Why Java RDBMS?

It is a well known fact that Java as a... Read More

Is Your Family Protected?

When you think... Read More

Great Plains Customization Upgrade? Overview For CIO/IT Director

Around the same time Microsoft made its move with .Net... Read More

Microsoft CRM Implementation for Large Corporation ? overview

Microsoft Business Solutions CRM is now approaching the phase of... Read More

Crystal Reports for Microsoft Great Plains

Microsoft Business Solutions ? Great Plains is designed to meet... Read More

Introduction To ISDN, Part II

In the previous ISDN article, we looked at how and... Read More

Know-how in Microsoft Publisher

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

Implementing ERP for Large Publicly Traded Corporation ? Microsoft Great Plains

We would like first emphasize the change in the paradigm.... Read More

Linux Dual Boot-How To Format

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

Lotus Notes Domino and Microsoft CRM Integration

Well, even if the combination might look very unusual, we... Read More

Microsoft Great Plains Integration with Microsoft Access ? Overview for Developer

... Read More

A Time-Saving Programming Tactic that Doesn?t Work

Let's say that you have a software project that's under... Read More

Professional Software Icons For Your Standalone Application

User interfaces and accessibility are some of the most important... Read More

MySQL for Beginners ? How to Create a MySQL Database

Whether you are an experienced web programmer or a complete... Read More

Windows x: Basic Windows ?Security? Issues

Language development computer: Computer-based method for aiding language development seems... Read More

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

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

Microsoft Great Plains: exchange & brokerage ? implementation notes

If you company is small or mid-size special products or... Read More

Software Process Improvement -A Successful Journey

Background: For many organizations like ours, the interim target of... Read More

Snort for Network IDS

What is Snort?Snort is an open source network intrusion detection... Read More