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! [email protected]

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.

In The News:

This RSS feed URL is deprecated, please update. New URLs can be found in the footers at https://news.google.com/news

Forbes

Hard Questions Raised When A Software 'Glitch' Takes Down An Airliner
Forbes
Defects in software code – strings of numbers and letters – can do it too. Late last month, a Lion Air Boeing 737 Max 8 jetliner crashed into the Java Sea off Indonesia, killing all 189 passengers and crew, due to what investigators described as a ...


Opinion: How old-school manufacturers can reinvent themselves and beat back the software giants
MarketWatch
The software giants start with enormous technological momentum. Nearly all innovation in physical goods now requires a heavy dose of software. The Internet of Things, cloud computing, big data analytics and artificial intelligence are coming together ...


Business Insider

Wall Street thinks these 5 public software companies could get acquired in mega-deals amid the market downturn
Business Insider
"While the list of public software company acquisitions has been relatively short," Berg wrote, "the aggregate valuations have been at a premium level that we consider to be at the upper end of historical ranges." Now with the stock market in a ...

and more »

Forbes

The Good Software Company With The Scary Stock
Forbes
The first story is about publicly traded Autodesk, which sells nifty software for the design of parts and buildings. Engineers are addicted. Does that make it a guaranteed annuity? Consider these addictive products of the past: Netscape, Novell's ...


Harvard Business Review

How Software Is Helping Big Companies Dominate
Harvard Business Review
Even outside of the tech sector, the employment of more software developers is associated with a greater increase in industry concentration, and this relationship appears to be causal. Similarly, researchers at the OECD have found that markups — a ...


Forbes

Software Composition Analysis Startups: Investors Are Looking For These Three Qualities
Forbes
Given these impressive injections of capital into the software composition analysis market, some young entrepreneurs may see an opportunity to break into SCA with their own solutions. However, with all the competition from established players, these ...


Forbes

How A Mysterious Tech Billionaire Created Two Fortunes—And A Global Software Sweatshop
Forbes
rom an office suite on the 26th floor of the iconic Frost Bank Tower in Austin, Texas, a little-known recruiting firm called Crossover is searching the globe for software engineers. Crossover is looking for anyone who can commit to a 40- or 50-hour ...


AppleInsider

Black Friday 2018: Here are the best software deals for Mac and iOS users
AppleInsider
Black Friday 2018: Here are the best software deals for Mac and iOS users. By: Roger Fingas. Wednesday, November 21, 2018 9:43 AM. People typically think of physical goods when it comes to Black Friday, but it can also be a good time to grab Mac ...


Coingape

Bitcoin Abc New Software Release, Transactions Immutable After 10 Confirmations
Coingape
The market is in deep red since the bitcoin cash split. Many crypto enthusiasts are not in favor of BCH fork which resulted into Bitcoin ABC and Bitcoin Cash SV (Satoshi's Vision). One such crypto enthusiast, named Bob McElrath took Twitter ...

and more »

Economic Times

Facebook says outage was due to a server software problem
Economic Times
WASHINGTON D.C. [USA]: Facebook said Tuesday users had trouble accessing the social network and its other applications such as Instagram for a chunk of the day due to a server software problem. It was the second instance in the past two weeks in which ...

and more »
Google News

Lotus Notes Domino and Web: Application Development ? Tips for Programmers

Beginning with Domino version R4 it has integration with the... Read More

Microsoft eCommerce Web-development: Great Plains eConnect .Net ? Highlights for Programmer

In our small article we'll consider Microsoft Business Solutions Great... Read More

Microsoft RMS ? Great Plains Integration ? Overview For IT Specialist

Microsoft Great Plains and Microsoft Retail Management System (Microsoft RMS)... Read More

How to Evaluate Staffing Software

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

CROOK: A Methodology for the Refinement of Forward-Error Correction

Table of Contents1) Introduction 2) Related Work 3) Framework 4)... Read More

Story Development Software: Good or Evil?

In the early days of the personal computer, we're talking... Read More

String in Java

Handling character strings in Java is supported through two final... Read More

Software: What Suits Me?

Almost all new and major brand of PCs come with... 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 customization ? Freight Forwarding/Transportation industry example

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

Microsoft Great Plains Multicurrency ? Overview For Implementation Consultant

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

Microsoft Great Plains Nationwide Remote Support

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

Does Microsoft have any real competition?

Does Microsoft Have any Real Competition? Copyright (c) 2003 Gregory... Read More

netstat - Linux Command to Display Networking Information

In linux, one of great commands for finding out information... Read More

Professional Software Icons For Your Standalone Application

User interfaces and accessibility are some of the most important... Read More

Microsoft C# vs. VB.Net

Hi, Guys,I believe a lot of programmers are trying to... 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

Is Your Small Business Ready For A CRM Software Solution?

I have yet to see a business that, sometimes in... Read More

Exactly What Does FTP Stand for and What Can it do for Me?

FTP stands for "file transfer protocol". FTP is basically a... Read More

Windows Screensavers Explained

In this article you will find some background information about... Read More

Corporate ERP Selection: Microsoft Great Plains

In our opinion, traditional approach when you select ERP/MRP system... Read More

SQL Administrator Skills Required to Support Microsoft Great Plains

Microsoft Great Plains is becoming more and more popular and... Read More

Microsoft Great Plains - Typical Problems And Fixes ? Overview For IT Administrators

How to delete the user? This is the first problem... Read More

Spyware Statistics -- Whats New in May 2005?

Although statistics often is blamed for various deadly sins --... Read More

Microsoft Great Plains: Customization Upgrade & Recovery ? Visual Studio VB 6.0

Microsoft Great Plains, former Great Plains Software Dynamics, eEnterprise has... Read More