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.

---

Managing Stress in the Computer Industry - Five Steps to a Stress-free Life

It would be easy to think, like most people apparently... Read More

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

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

3 Reasons Why Medical Billing Software is Leading the Way

Since technology changes so quickly, it is hard to begin... Read More

Removing Incoming Email in MS Exchange, C# Example

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

Dont Choose Adobe When Working With PDF

While Adobe is the most known maker of PDF tools,... Read More

Linux ? Keyboard Or Mouse

Just stress testing one of the latest Linux distributions. Been... Read More

Passwords Used In Microsoft Word Documents

You would like to protect your documents, wouldn't you? Reasons... Read More

Dashboard Widgets for Windows

For a windows user like me, just can watch with... Read More

What is a Document Manager without Version History?

Document Manager and Version HistoryIn previous articles I have discussed... Read More

Corporate Accounting System: Microsoft Great Plains ? Overview

Corporate ERP/MRP selection might be tough one, especially considering very... Read More

Microsoft Great Plains Data Conversion ? Overview For Developer

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

25 Things Mapping Software Can Do For You

1. With mapping software you can create a report that... Read More

Online PowerPoint Presentation ? Convert PowerPoint to Flash

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

Design a Web Album Using Adobe Photoshop- Part 2

So let's begin crunching down these 300 images using Adobe... Read More

Spyware: What It Is and How to Combat It

Spyware is software or hardware installed on a computer without... Read More

Database Guru James F. Koopmann Reviews DBxtra Reporting and Query Tool

DBxtra is a powerful query and reporting tool that hides... Read More

Microsoft Great Plains Oil & Gas ? Implementation & Customization Highlights

Microsoft Great Plains serves the wide spectrum of horizontal markets.... Read More

Microsoft Great Plains Customization and Development ? Overview for Programmer

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

Anti-Virus Software

Anti-virus software is used to find, remove or fix files... Read More

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

Microsoft Business Solutions CRM is web-based CRM application, deploying all... 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

Lotus Domino/Notes - Microsoft Great Plains Tandem as ERP with Documents Workflow - Overview for IT

Lotus Domino/Notes ? Microsoft Great Plains tandem as ERP with... Read More

Navision Attain Database access via C/ODBC in ASP.NET Application

Navision Software was purchased by Microsoft and now it is... Read More

Microsoft Great Plains FA: Fixed Assets ? Overview For Consultant

Great Plains Fixed Assets Management module is a robust tool... Read More

What is Software Piracy?

There are several kinds of software piracy. The bottom line... Read More

Windows x: Basic Windows ?Security? Issues

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

Linux Vs. Windows

This article will not attempt to advocate the use of... Read More

Make or Break Factors - When Considering Estimating Software

Make-or-Break Factors in Success and ProfitabilityFor quick printers, estimating can... Read More

Interactive Mapping Brings Information to Life

What is Interactive Mapping?Interactive mapping is a visual display medium... Read More

How to Make Own CMS

Every day millions of new web documents emerge on the... Read More

Run Your Own Search Engine

Our hosted solution allow you to run your own search... Read More

Microsoft Great Plains Integration Manager ? Advanced Techniques

Great Plains Integration Manager scripting and translation - overview for... Read More

A Simple Computer Software Definition

What is Software?Software is a set of instruction written to... Read More