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:

North Korea 'agrees to close missile test site'
Wed, 19 Sep 2018 03:55:00 +0100
North Korea dictator Kim Jong Un has vowed to permanently dismantle a missile test site and a launch pad after talks with South Korea.

US doctor and girlfriend charged with drug rapes
Wed, 19 Sep 2018 07:13:00 +0100
A doctor who appeared on a dating show has been charged with drugging and sexually assaulting two women with his girlfriend - and police suspect there may be many more victims.

Brett Kavanaugh accuser 'faces death threats'
Mon, 17 Sep 2018 18:04:00 +0100
A woman who accused US Supreme Court nominee Brett Kavanaugh of sexual assault says she has faced death threats and has called on the FBI to investigate her allegations.

Merkel removes spy chief to quell racism row
Tue, 18 Sep 2018 21:16:00 +0100
Germany's leader Angela Merkel has removed her spy chief to end a row over immigration which threatened to destroy her fragile coalition.

Russia blames Israel after Syria shoots down plane
Tue, 18 Sep 2018 06:27:00 +0100
Russia has blamed Israel and threatened retaliation after one of its military planes was shot down by Syrian missiles in a "friendly-fire" incident.



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.

Benefits of Shareware

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

Cisco Certification: Introduction To ISDN, Part V

The major reason I recommend getting your hands on real... Read More

A Symons Mark II Function Point Counting Example

I provide, here clear explanations and a count of function... Read More

Looking for an Alternative to Microsoft Office? You Should Be!

Now is the time to look at an alternative to... Read More

Backing Up And Restoring Your MySQL Database

If you've been using MySQL database to store your important... Read More

Navision Attain C/ODBC Crystal Report ? Customization Example

Microsoft Business Solutions Navision is main ERP application for European,... Read More

Microsoft Great Plains eCommerce ? Stored Procedures Approach

Since Version 8.0 Microsoft Business Solutions Great Plains & Great... 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 Update - 5 Reasons to Update Service Pack 2

Microsoft released Service Pack 2 (SP2) for the Windows XP... Read More

Intro to UNIX Shells

A UNIX Shell is in simplest terms, a command line... Read More

6 Easy Steps for a Smoother CD/DVD Order

It's all about turn times in the eMedia industry! The... Read More

What is Preventive Maintenance Software?

Preventive Maintenance (PM) is defined as scheduled work done on... Read More

Linux for Home Users

Hey Guys! Don't raise your eyebrows or fear by hearing... Read More

Software Piracy

SOFTWARE PIRACY We regularly hear reports... Read More

Microsoft Navision Customization Upgrade ? Tips For Programmer/IT Specialist

Currently Microsoft Business Solutions is on the way of creating... Read More

Microsoft Great Plains SOP: Sales Order Processing

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

QuarkXpress Tips: How to Move Content Between Print and Web Layouts

Since its release in 1987, QuarkXpress had made an immediate... Read More

Understanding XML Server

XML Server can be a Web Server that stores the... Read More

Linux Dual Boot-How To Format

Just the thought of a duel-boot scares many people away,... Read More

Increase Office Efficiency With One Simple Tool

When you need a phone number, you do a quick... Read More

Business Planning Software

Once a business idea is selected, it is highly recommended... Read More

History of Java

The java programming language is becoming more and more popular... Read More

Microsoft CRM and No-Frills Cadillacs

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

Microsoft Navision Database Selection: C/SIDE or MS SQL Server - Overview For IT Specialist

There are certain pluses and minuses in both cases and... Read More

A Simple Computer Software Definition

What is Software?Software is a set of instruction written to... Read More