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.

---

Destination: Desktop for Google

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

Instant Messenger Clients

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

Microsoft Great Plains Integration with Microsoft Access ? Overview for Developer

... Read More

How to Choose the Right Accounting Software for Your Business

With any good luck and a good amount of hard... Read More

Microsoft CRM ? Typical Customizations

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

What To Do When Windows Wont Boot

When Windows fails to boot it is normally caused by... Read More

Configure Windows Indexing Service for Performance

The Windows Indexing Service provides you with the ability to... Read More

The Religion And Philosophy Of Small Internet Business

I have always had a tendency to focus on the... Read More

Microsoft Great Plains Integration Manager ? Advanced Techniques

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

How to Make Own CMS

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

Microsoft Axapta, Navision or Great Plains: ERP Selection for Large Corporation

If you would like to pick something from Microsoft, or... Read More

Business Plan Software Review: Ultimate Business Planner?

One of the main reasons business owners and entrepreneurs use... Read More

The Secret of the Layer Styles Dialogue

When you double-click a layer in the Layer Palette, you... Read More

Importance of Coding Standards

Programming Help for BeginnersWe write programs to instruct computers. When... Read More

Software Companies: Generate New Revenue Streams and Decrease Costs with Custom e-Learning Content

It's no secret that software companies operate in a very... Read More

Editing Your Photos Using Microsoft Picture It Publishing Platinum 2002 - A Great Dinosaur

I started using PIP (Picture It Publishing) Platinum 2002 right... Read More

Microsoft Great Plains Dynamics on Pervasive SQL.2000 Data Repair ? Overview For DBAs

Some introduction into Great Plains Software products, now Microsoft Business... Read More

The XP Firewall Isnt Enough

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

Microsoft Great Plains: Annual Enhancement Program ? How To Be Re-Enrolled

Microsoft Business Solutions Great Plains is mid and even corporate... Read More

Microsoft Great Plains Implementation: Verticals - Wholesale Order Entry center ? overview

Microsoft Business Solutions Great Plains has substantial market share and... Read More

Microsoft Great Plains Nationwide Remote Support

ERP Consulting industry is on the way to serve clients... Read More

Collaboration Software: Index of Collaboration Software Technologies

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

Software For Your Hard-Drive

All your software is stored on a hard-drive. But how... Read More

Kick-Ass Performance For Your PC? Its Easy

Is your PC is slow and wimpy? Then you need... Read More

The Truth: Netzero 3G

We've all seen the ads on TV for Netzero 3G.... Read More

Great Plains Bill of Materials Setup & Customization for Process Manufacturing ? Overview

Microsoft Great Plains has full-featured Manufacturing suite of modules: Capacity... Read More

Microsoft Great Plains Customization Tools Evolution ? Overview for Consultant

When Great Plains Software introduced the first graphical accounting application... Read More

Microsoft Great Plains: exchange & brokerage ? implementation notes

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

Manufacturing Outsourcing: Microsoft Great Plains Implementation, Customization & Reporting

Manufacturing in the USA is far away down from mid... Read More

Microsoft Great Plains POP: Purchase Order Processing ? Overview For Consultants

Great Plains Purchase Order Processing (POP) module makes up one-third... Read More

Software Upgrades Arent Always the Best Move

When my daughter was getting into AOL instant messaging (AIM)... Read More

Know-how in Microsoft Publisher

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

Great Plains Sales Order Processing and Invoicing Modules ? Tips For Consultants

We'll give you non formal view, based on our consulting... Read More