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.

---

Start Developing Your Own Software

Learning how to program software seems like a daunting task... Read More

Free Software for Newbies and Web Developers

Here is some free software tools to help you build... Read More

HelpDesk & Asset Management Software - Choosing The Right One

If you are to buy a HelpDesk & Asset Management... Read More

Imagine if Everyone Working In Your Office Was In Synch?

Microsoft-Outlook is a pretty amazing program. So much more than... Read More

Microsoft CRM for Large Corporation ? Security

Microsoft Business Solutions CRM proved to be reliable solution in... Read More

Introducing GRML

Creating a new markup language.Introduction.General Reuse Markup Langauge, or GRML,... Read More

Free Program Removes Spyware not Detected by Premium Security Scan

What is Spyware?Spyware monitors your surfing habits and sends the... Read More

Microsoft Great Plains: Dexterity vs. eConnect ? FAQ

Microsoft Business Solutions Great Plains, former Great Plains Software Dynamics/eEnterprise... Read More

Adware and Spyware Blockers

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

Microsoft CRM Programming Secrets ? Tips For Developers

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

Microsoft Blues

With the advent of 'Service Pack 2' for Windows XP... Read More

OLAP, An Alternative Technology Over Spreadsheets

Are Spreadsheets Robbing your Enterprise of Competitive Advantage?'90% of "average"... Read More

Removing Incoming Email in MS Exchange, C# Example

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

Destination: Desktop for Google

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

10 Things You Could be Using Photoshop For, But Probably Arent

Most people don't use Photoshop to its fullest capabilities. Here... Read More

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

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

Internet Faxing Service Review

The Internet is reshaping every form of communications medium, and... Read More

Microsoft Great Plains Implementation for Midsize & Large Corporation: Lockbox Processing

Microsoft Great Plains is now targeting large and midsize businesses... 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

What is Fleet Maintenance Software?

Simply put, fleet maintenance allows companies to monitor and maintain... Read More

How to Evaluate Staffing Software

If you are in the market for new staffing software,... Read More

Introduction To ISDN, Part II

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

PHP On-The-Fly!

IntroductionPHP can be used for a lot of different things,... Read More

Great Plains Dexterity ? Microsoft Great Plains Customization Overview

Microsoft Business Solutions Great Plains, former Great Plains Software Dynamics... Read More

Great Plains Customization ? Programming Auto-apply in Accounts Receivable

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

Microsoft CRM: Data Conversion ? Import from Act!

Best Software Act! is very popular CRM for small and... Read More

Microsoft Great Plains Upgrade ? Things to Consider and FAQ

If you have Microsoft Great Plains and support it for... Read More

Microsoft CRM and No-Frills Cadillacs

In today's business world it's all but impossible to escape... Read More

How to Tell You Have Spyware, Ad-ware or Viruses

Usually, the easiest way to tell you have spyware is... Read More

Reduce TCO: The Java Database Way

TCO (Total Cost Ownership) is the buzzword in... Read More

Partitioning, Formatting and Reinstalling in Windows 98

Formatting and reinstalling windows 98 is very easy if you... Read More

How To Identify, Cure and Prevent Spyware/Adware Infections

Just when you thought you were Web savvy, one more... Read More

An Introduction to .NET Framework

.Net Framework is a platform or development environment to seamlessly... Read More