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.

---

Implementing Microsoft CRM: setup and configuration ? notes for IT specialist

Microsoft Business Solutions CRM is web-based CRM application, deploying all... Read More

Groupware Finds Home in American Business: Collaboration Tool Analysis

What is Groupware?Have you ever had to manage document collaboration... Read More

Microsoft CRM: Implementation, Customization, Support ? Consultant Overview

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

Who Is Minding Your Sensitive Data?

Stealing company information used to be the specialty of spies... Read More

Adware, What Is It?

Adware is a type of Spyware program that displays some... Read More

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

We are in a transition phase in the Managerial Administration... 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

The XP Firewall Isnt Enough

You might think you don't need a firewall... Read More

Separate Anti-Keylogging Protection: Who Needs it Most?

If there still are few unprotected computers left, I haven't... Read More

The Bluebird Project

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

Adware and Spyware Blockers

The most important things you can do for your computer... Read More

6 Easy Steps for a Smoother CD/DVD Order

It's all about turn times in the eMedia industry! The... Read More

Corporate ERP: Standard vs. Rich functionality ? Microsoft Great Plains

Traditionally we were considering functionally rich systems, such as SAP,... Read More

Basic Tips and tricks for Windows XP

Running Applications in Compatibility Mode With Windows XP, you can... Read More

Snort for Network IDS

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

Windows x: Basic Windows ?Security? Issues

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

Pros and Cons of Using FREE Software in Your Business

Itâ??s easy to understand why you might be drawn to... Read More

What You Should Know About Installing Screensavers

Do you remember that frustrating feeling when you find an... Read More

FreeDOS

Before September 1995, Microsoft Windows was an MS-DOS program. DOS... Read More

Assertion in Java

Assertion facility is added in J2SE 1.4. In order to... Read More

Benefits of Integrating Online Chat Software with CRM

Customer Relationship Management (CRM) is a strategy and processes used... Read More

Destination: Desktop for Google

First we had the original Google search that evolved into... Read More

Microsoft Great Plains Implementation in Russia ? Overview for Consultant

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

Google Brings the Earth to Your Desktop

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

Corel WordPerfect 7 Macro Programming Example

Case study: A secretary using Corel WordPerfect 7 is often... 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

Collaboration Software: Index of Collaboration Software Technologies

Collaboration SoftwareCollaboration Software, also known as group collaboration software or... Read More

Microsoft CRM ? Typical Customizations

Microsoft CRM was designed to be easily customizable. Microsoft CRM... Read More

Internet Security Threats: Who Can Read Your Email?

Before being able to choose a secure Internet communication system,... Read More

Microsoft Great Plains Multicurrency ? Overview For Implementation Consultant

When you first think about multicurrency ? you probably have... Read More

Microsoft and Webmasters

Does Microsoft care for WebmastersIt's always been a problem with... Read More

Daffodil DB: Web Database

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

The Top 5 Wrong Reasons For Not Hiring Testers

Considering whether or not your software company should hire a... Read More