Maintain your old Siemens Hipath system

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.

In The News:

Firm apologises over 'whitewashed' Naomi Osaka advert
Wed, 23 Jan 2019 11:34:00 +0000
A sponsor of Japanese tennis star Naomi Osaka has apologised for whitening her face in one of its advertisements.

Concrete-filled bodies found are missing activist's aides
Wed, 23 Jan 2019 05:09:00 +0000
Two mutilated bodies washed up on the shore of the Mekong River in northeast Thailand have been identified as anti-government activists.

Nurse arrested after woman in vegetative state gave birth
Wed, 23 Jan 2019 15:19:00 +0000
A nurse has been arrested after a woman who has been in a vegetative state in the US for years gave birth at a long-term health care facility.

Bali bombings cleric must renounce radicalism before release
Wed, 23 Jan 2019 06:32:00 +0000
The alleged mastermind of the Bali bombings will not be released from prison early unless he renounces radicalism, Indonesia's president has said - backtracking from plans to free him imminently without conditions.

Pilot of missing plane carrying Cardiff footballer named
Wed, 23 Jan 2019 16:21:00 +0000
The private pilot flying the plane which has gone missing with footballer Emiliano Sala on board has been named as Dave Ibbotson.



tikatoshop.it

Erfahrungen mit Pallhuber Wein
Agen Bola SBOBET Terpercaya

Travel in comfort and at your leisure with CT Airlink Limousine & Car Service for top quality private transportation and exceptional customer service. We operate Sedans, SUVs & Vans for CT Car Services to covering all Connecticut airports including Car Service from CT to Newark Airport , Mohegan Casino Uncasville CT, Foxwoods Casino Mashantucket CT, Manhattan Cruise Terminal NYC, Brooklyn Cruise Terminal NYC and Bayonne Cruise Terminal NJ. CT Airlink hire licensed and friendly chauffeurs who have in-depth knowledge of the Areas.

Navision Attain Database access via C/ODBC in ASP.NET Application

Navision Software was purchased by Microsoft and now it is... Read More

Microsoft Great Plains & CRM in Transportation & Logistics ? Overview

Microsoft Business Solutions Great Plains and MS CRM (client relation... Read More

Microsoft Great Plains Remote Support - Overview

Microsoft Great Plains is now standard mid-market ERP application, serving... Read More

Microsoft Great Plains Upgrade ? Version 8.0 Overview for IT Director/Controller

If you have Microsoft Great Plains as main accounting and... Read More

Programming Environments And The Software Production Process

Introduction: The creating of a computer program involves a number... Read More

Microsoft CRM Modification ? Overview for IT Specialist

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

S is for Spying, Surveillance -- and for Software as Well

Words we choose to describe things and phenomena often show... Read More

Microsoft C# vs. VB.Net

Hi, Guys,I believe a lot of programmers are trying to... Read More

Introduction To ISDN, Part III: PAP

Introduction To ISDN, Part III: Configuring PPP PAP AuthenticationNow we... Read More

Two Reasons to Use Timesheet Software

This short paper will expand on two key reasons to... Read More

Ukraine IT Myths Dispersed

While Ukraine is becoming a new popular IT outsourcing destination,... Read More

Choose your Java Wisely

Java has come along a long way. Many would agree... Read More

How To Develop Software For Your Business

Software development is a risky business.Many software developers are barely... Read More

Navision Sales Module & Reporting: Jet Reports, C/ODBC, XBRL, Business Analytics ? highlights

Microsoft bought Navision, Denmark based software development company, along with... Read More

Microsoft Great Plains: If You are Orphan Client ? What to Do and FAQ

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

A Guide To Purchasing Professional XP Icons Online And Enhancing Your Applications

Icons are used everywhere; right from software applications, to internet... Read More

The Bluebird Project

The objective for Zandi Digital is to make available clever... Read More

Groupware: Avoid the Ad Hoc Shuffle

GroupwareEfforts are continually made to manage the unavoidable ad hoc... Read More

Importance of Coding Standards

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

Do You Know These Facts About Spyware ?

Imagine something that follows you home and sets itself up... Read More

Free PDF Publishing Software

In a previous article, I wrote about OpenOffice... Read More

Defining OLAP Solutions and Data Warehouse design

This tutorial covers OLAP solutions used by Data warehouses and... Read More

Microsoft Navision Integration with Microsoft RMS - Overview for IT Specialist

Microsoft Business Solutions Navision serves both European and American megamarkets.... Read More

String in Java

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

Screenshots Vista Windows

Features Additionally, Vista will include many other new features.Aero Vista... Read More