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.

---

SyncUp ? A File/Folder Synchronizer For Windows

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

Anti-Spyware Protection ? Holes in the Shining Armor

Looking at all the ads which promise to get rid... Read More

Software Piracy - Global Increase

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

Microsoft Great Plains SOP: Sales Order Processing

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

COSMIC: A Small Improvement on the Symons Method

The COSMIC FP (function point) software quality metric, is no... Read More

Why Stick With Email Clients Like Outlook?

Trying to figure out a stream in banning one email... Read More

Professional XP Style Icons in Software and Website Development

Using professional icons in your application or website can bring... Read More

Microsoft Great Plains: Interest Calculation Example ? Stored Procedure for Crystal Report

This is intermediate level SQL scripting article for DB Administrator,... Read More

Microsoft CRM and Great Plains Implementation: Freight Forwarding Business Automation Example

Microsoft Business Solutions offers several ERP applications: Great Plains, Navision,... Read More

Benefits of Shareware

Shareware has been fighting the stigma of being misunderstood for... Read More

Microsoft Great Plains Implementation ? Overview for IT Director/Controller

What is installation in the language of technology? Installation... Read More

Reloading Windows XP

If you have been running Windows XP for a couple... Read More

Popular Fleet Maintenance Programs

Some companies that are in need of fleet management may... Read More

Instant Messenger Clients

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

Microsoft Great Plains Customization and Development ? Overview for Programmer

When Great Plains Software was designing and developing Great Plains... Read More

Microsoft Navision Customization and Reporting ? Tips For Programmer/IT Specialist

C/SIDE (Client/Server Integrated Development Environment) - The core of... Read More

Microsoft Great Plains customization ? Freight Forwarding/Transportation industry example

Microsoft Business Solutions Great Plains version 8.5, 8.0, 7.5, Great... Read More

Microsoft Great Plains: exchange & brokerage ? implementation notes

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

Corel WordPerfect 7 Macro Programming Example

Case study: A secretary using Corel WordPerfect 7 is often... Read More

Secure File Transfer Using SSH Plus Additional Audit & Automation - FSA Reporting

In order to meet regulatory and corporate compliance requirements reporting... Read More

5 Easy File Management Tips in Microsoft Word

How many steps does it take you to locate and... Read More

Mapping Software: Putting Data Visualization on the Map

Mapping Software Improves Data VisualizationFrom the outset, it is important... Read More

Software Tools To Help Your Business Sell More

Sales are all about leverage, because there is only so... Read More

Microsoft Great Plains Integration with Legacy Systems ? Overview For Developer

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

eConnect: eCommerce Development for Microsoft Great Plains

Microsoft Business Solutions Great Plains has several options to enable... Read More

Navision Sales Module & Reporting: Jet Reports, C/ODBC, XBRL, Business Analytics ? highlights

Microsoft bought Navision, Denmark based software development company, along with... Read More

Microsoft Great Plains Security Setup - Overview For Consultant

Microsoft Business Solutions Great Plains is very good fit for... Read More

Falling in Love With More Than One Screensaver: The Fun Part

Having from five to ten and more favorite screensavers is... Read More

Microsoft CRM Data Import FAQ

Microsoft CRM has built-in conversion tool, however you should probably... Read More

Do You Want Your Own Fully Programmable ERP? - Part 1

We are in a transition phase in the Managerial Administration... Read More

Microsoft Great Plains Customization Tools ? Overview

Former Great Plains Software Dynamics/eEnterprise, and currently Microsoft Business Solutions... Read More

Microsoft C# vs. VB.Net

Hi, Guys,I believe a lot of programmers are trying to... Read More

C++ Tutorials: 3, Program Flow (If, Else, While, For)

Program Flow is what you think it is. How the... Read More