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

TechCrunch

Former software engineer accuses Uber of “degrading conduct” toward women in new lawsuit
TechCrunch
A former Uber software engineer filed a lawsuit against the company today in the Superior Court of California, accusing it of retaliating against her for after she reported sexual harassment and discrimination. The complaint by Ingrid Avendaño, who ...

and more »

CNET

Amazon's new Marketplace Appstore connects sellers to software
CNET
Amazon is creating another app store, but it's not for consumers. Instead, the online retail giant will for the first time put its seal of approval on a bunch of third-party apps intended for professional sellers with its new Marketplace Appstore. It ...

and more »

Financial Times

RIB Software: the unicorn rainy-day fund
Financial Times
RIB Software is a German-listed technology company with grand aspirations. What SAP's enterprise resource planning software has done to manufacturing, it hopes to do to the construction industry. Digitisation of planing, design and project management ...


ZDNet

Software fault caused Telstra mobile outage
ZDNet
Telstra has said its Monday morning mobile outage was caused by a software fault, which it said "triggered multiple elements across the network to fail". "Starting from approximately 10:00am yesterday morning, some key network equipment failed, causing ...
Mystery software fault behind Telstra's latest network outageARNnet
Telstra outage: 'Software fault' blamed for problem | Herald SunHerald Sun
Telstra blames software failures for major outageThe Australian
NEWS.com.au -Computerworld Australia
all 27 news articles »

Slate Magazine

Facial Recognition Software Will Not Stop School Shootings
Slate Magazine
The upstate New York district of Lockport is introducing facial recognition and tracking software to its school security systems, the same kind of software used in airports and casinos. Individual students won't be programmed into the system unless ...

and more »

Business Insider

A former Uber software engineer is suing the company for ignoring her claims that she was sexually harassed
Business Insider
A former software engineer at Uber Technologies Inc sued the ride-hailing service on Monday, claiming that she was subjected to sexual harassment during her employment but that her complaints were ignored, the law firm representing the plaintiff said.


The Register

Apple tells app makers to strip VoIP toolkit from iOS software in China
The Register
Developers of iOS apps distributed in China have started to receive notifications from Apple that they are required to remove CallKit, a software UI framework for integrating VoIP calling services, from their apps. A post in an Apple developer forum ...
Apple cracking down on CallKit apps in China App Store due to government regulation9to5Mac

all 31 news articles »

SlashGear

It Only Took Six Years, But Tesla Is No Longer Screwing Up Basic Software Licenses
Gizmodo
Tesla's super smart cars, specifically the sporty Model S sedan and Model X SUV, incorporate a lot of open source software, from Linux, the open source operating system, to BusyBox, a collection of tools that are useful when working with Linux and ...
Tesla releases some of its software to comply with open source rulesSlashGear
Tesla Starts Open Sourcing Some Software Code After Facing CriticismFossbytes

all 6 news articles »

Houston Business Journal

BMC Software reportedly considering another sale
Houston Business Journal
Houston-based BMC Software Inc. might get new owners. People familiar with the matter told Bloomberg that BMC is working with Goldman Sachs Group Inc. and Credit Suisse Group AG to weigh a sale. However, a deal might not happen. KKR & Co. (NYSE: ...


WSMV Nashville

Outdated GPS software led to delay in emergency response time
WSMV Nashville
Since the incident on April 24, Williams said Maury Regional Medical Center has updated the Garmin devices in every single ambulance. She said they also alerted Garmin about not receiving the proper software update. The I-Team reached out to Garmin but ...

Google News

Microsoft Small Business Manager Customization Options - Overview

Microsoft Business Solutions Small Business Manager is Great Plains Dexterity... Read More

10 Programming Tips

(1) Avoid using the same variable again and again for... Read More

The True Meaning of Freeware

The vast majority of us will have, at some point,... Read More

Importance of Coding Standards

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

Microsoft Great Plains - Microsoft RMS Integration ? overview

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

ERP for Large Corporation ? New Selling Strategy

Our opinion is based on our Microsoft Business Solutions Great... Read More

Accounts Payable: A Powerful Document Management and Workflow Solution

Accounts payable is just one area of office management where... Read More

CRM 101: Customer Relationship Management for Beginners

Customer Relationship Management, abbreviated "CRM," is the term for a... Read More

Manufacturing Solutions for Microsoft Great Plains ? Overview for Consultant

Microsoft Business Solutions Great Plains has full-featured manufacturing set of... Read More

Software Process Improvement -A Successful Journey

Background: For many organizations like ours, the interim target of... Read More

eCommerce development for Microsoft Great Plains: tools and highlights for programmer

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

SyncUp ? A File/Folder Synchronizer For Windows

SyncUp, a file synchronizer is designed to assist the home... Read More

Microsoft Great Plains RW: Report Writer

Microsoft Business Solutions Great Plains is marketed for mid-size companies... Read More

My Experience - Making a Vision into Reality

Disclaimer: All the thoughts expressed are my views only! Your... 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 Security Setup - Overview For Consultant

Microsoft Business Solutions Great Plains is very good fit for... Read More

An Easy Way to Deal with Email Viruses and Worms

If you feel intimidated when someone tries to teach you... Read More

Free Microsoft Word Online Training Tutorial Resources

Microsoft Word is one of the most popular office applications... Read More

Save Your Resources - Combine Your IM Clients

There are so many different programs that clutter up your... Read More

Manufacturing Outsourcing: Microsoft Great Plains Implementation, Customization & Reporting

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

Benefits of Shareware

Shareware has been fighting the stigma of being misunderstood for... Read More

Mozilla Vs. Firefox

For those who are unclear on the differences between the... Read More

Microsoft Great Plains Customization Recovery & Upgrade for Large Corporation

At the end of XX century, in the late 1990th... Read More

Microsoft CRM Modification ? Overview for IT Specialist

Microsoft CRM is now on the scene and it is... Read More

Great Plains Dexterity Programming ? Overview For Developer

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