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.

shuttle from O'Hare Brocton .. Lockport Chicago limo O’Hare
In The News:

Unitree, a Chinese robotics company that developed a backflipping robot, has now introduced a humanoid robot capable of doing a side flip.
Stay up to date on the latest AI technology advancements and learn about the challenges and opportunities AI presents now and for the future.
According to the FBI, cybercriminals are sending extortion letters, threatening to release victims' sensitive information unless a ransom is paid.
FireSat is a satellite project designed to detect and track wildfires early, aiming to detect a fire that's the size of a classroom within 20 minutes.
Tech expert Kurt “CyberGuy" Knutsson reports that researchers have uncovered a Chrome vulnerability used in a cyber espionage campaign.
Preserving voicemails securely on Android: Tech expert Kurt “CyberGuy" Knutsson reveals easy methods to keep memories alive forever.
This phishing kit bypasses 2FA via session hijacking and real-time credential theft. Kurt “CyberGuy" Knutsson offers four ways to stay safe from Astaroth phishing attacks.
Tech expert Kurt “CyberGuy" Knutsson discusses Joby Aviation and Virgin Atlantic planning to launch 200-mph U.K. air taxis linking airports and cities.
Fake Apple virus warnings, security alerts and messages are tactics used to prompt you to call a number or click on a malicious link. The CyberGuy explains how to protect your devices.
The combination of artificial intelligence and neuroscience allows a paralyzed man to manipulate a robotic arm by using his brain to imagine movements.
With the help of artificial intelligence, sophisticated fraud techniques emerging today are virtually undetectable to the untrained eye.
Stay up to date on the latest AI technology advancements and learn about the challenges and opportunities AI presents
If you need to free up space on your Mac, consider deleting duplicate photos in your image library. Apple makes it easy to do, and the CyberGuy explains the process.
Microsoft is discontinuing Skype in May after 22 years of service. Kurt the CyberGuy addresses other options for internet-based phone and video service.
Tron 1, a Chinese company's two-legged robot, is versatile and can walk, roll and pivot, even on rough terrain. Tron 1 stands 33 inches tall and weighs 44 pounds.
Hackers are tricking people into installing password-stealing malware by making them press keyboard shortcuts under the pretense of proving they're not bots.
Saving the voices of loved ones can be a comforting way to keep alive memories. Kurt "the CyberGuy" Knutsson explains how to preserve voicemail messages.
Stay up to date on the latest AI technology advancements and learn about the challenges and opportunities AI presents now and for the future.
The FBI warns that some free online document converters load malware onto people's computers, making their private information vulnerable to attack.
Toyota's compact electric FT-Me is designed to tackle challenges of city living while offering an accessible and eco-friendly transportation solution.
Kurt “CyberGuy" Knutsson says 23andMe's bankruptcy raises data privacy fears: Opt out, download and delete data now.
UBTech and Zeekr unite with AI robot swarms to make car manufacturing faster and smarter. Tech expert Kurt “CyberGuy" Knutsson explains how the process works.
Tech expert Kurt “CyberGuy" Knutsson says Apple's Passwords app had a three-month phishing flaw from iOS 18 to 18.2 patch.
Tech expert Kurt “CyberGuy" Knutsson discusses how this super-small electric mini-truck takes a big step toward production.
Artificial intelligence-generated images and videos that use someone’s likeness typically target women without their consent. The CyberGuy unpacks how to protect loved ones.

Microsoft Great Plains international implementation ? USA / Mexico ? overview for consultant

Microsoft Business Solutions Great Plains was purchased from Great Plains... Read More

Start Developing Your Own Software

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

Microsoft CRM for Large Corporation ? Security

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

Microsoft Great Plains on Ctree or Pervasive SQL ? What to Do ? Tips for IT Manager

As you probably know, when Microsoft purchased Great Plains Software... Read More

Five Reasons for Using an O/R Mapping Tool

So, why should you use any O/R mapping tool? I... Read More

Screenplay and Script Writing Software

When it comes to screenplay software each screenwriter needs to... Read More

Internet Relay Chat - A Basic Introduction

What is IRC?IRC is Internet Relay Chat. It is a... Read More

Hubris - Definition: Microsofts Passport

Before September of 1995, Microsoft ignored the Internet because their... Read More

What You Should Know About Installing Screensavers

Do you remember that frustrating feeling when you find an... Read More

Microsoft CRM Customization ? Programming Closed Email Activity

Microsoft CRM is CRM answer from Microsoft and attempt to... Read More

Microsoft Great Plains Security Setup - Overview For Consultant

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

Five Steps to Rapid Development with TierDeveloper 4.0

Follow the steps below to quickly design, generate, and deploy... Read More

Free Preventive Maintenance Software

While several preventive maintenance software manufacturers offer free trials for... Read More

Microsoft CRM Implementation - US Market Lessons

Microsoft CRM is CRM answer from Microsoft Business Solutions. If... Read More

Reduce TCO: The Java Database Way

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

Destination: Desktop for Google

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

Great Plains Dexterity: Customizations & Source Code Programming

Great Plains Software Dynamics, Dynamics C/S+, eEnterprise were written on... Read More

COSMIC: A Small Improvement on the Symons Method

The COSMIC FP (function point) software quality metric, is no... Read More

Lotus Notes Domino and Microsoft CRM Integration

Well, even if the combination might look very unusual, we... Read More

Microsoft Great Plains Customization Recovery & Upgrade for Large Corporation

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

ERP Remote Support: Microsoft Great Plains Analysis ? Pluses & Minuses

Former Great Plains Software Dynamics/eEnterprise and currently Microsoft Business Solutions... Read More

XML Parser and Their Types

XML parser is a software module to read documents and... Read More

Corporate ERP: Standard vs. Rich functionality ? Microsoft Great Plains

Traditionally we were considering functionally rich systems, such as SAP,... Read More

Who Is Minding Your Sensitive Data?

Stealing company information used to be the specialty of spies... Read More

OS 101: Suggestions for Choosing an Operating System

An operating system (abbreviated OS) is essentially the path through... Read More

Chicago charter limousine service Hickory Hills ..