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:


The Register

Green software blacked out Australian State
The Register
Something good is going to come out of last year's “Black System” in the Australian State of South Australia: the global wind power industry has learned how to do better modelling for systems under attack from repeated failures. South Australia last ...

and more »

TNW

3 software development trends you don't want to miss
TNW
This has led to more and more companies adopting team chat applications to assist in conducting key business operations, including the building and shipping software applications. Interacting with your team members using a chat application is only ...


New York Times

Software Engineer Starts Unlikely Business: A Weekly Newspaper
New York Times
The investment — by a software engineer who studied artificial intelligence, no less — seems like a quixotic one when so many newspapers are struggling and many readers prefer to catch up on town news on Facebook. But the engineer, Heeten Choxi, ...

and more »

Siemens' software donation to benefit Connecticut community college students
New Haven Register
The intial befeficiaries of Siemens' product lifecycle management software will be students in the Advanced Manufacturing Technology Centers at Three Rivers, Manchester, Asnuntuck and Gateway community colleges. Use of the software Siemens is making ...
Siemens Gives Community Colleges $315 Million Worth of Advanced Manufacturing SoftwareHartford Courant

all 4 news articles »

Brownwood Bulletin

Commissioners OK use of forfeiture funds for prosecutors' software
Brownwood Bulletin
Brown County Commissioners Court members on Monday authorized District Attorney Micheal Murray's office to spend just over $94,000 in forfeiture funds to buy software and equipment that will streamline the process of providing discovery to defense ...
Brown County DA getting new software to increase transparencyKTXS

all 2 news articles »

PCWorld

Switching from Mac to PC, Part 3: The software challenge | PCWorld
PCWorld
When you switch from Mac to PC, you'll be leaving some software behind. We've found some solid replacements.

and more »

GeekWire

How this legal software startup used its funding round to support legal aid under Trump budget cuts
GeekWire
But MetaJure, a Seattle startup that provides software to help lawyers manage their documents and email, is starting early. As early as the company's Series A funding round, in fact. MetaJure used its $2.6 million round as an opportunity for what its ...

and more »

Market Realist

How BBM Enterprise Could Impact BlackBerry's Software Business
Market Realist
By opening BBM Enterprise SDK (software development kit) to third-party developers, BlackBerry has also unlocked a new revenue stream in its Software and Services segment. The company could take a cut from the sales of third-party apps integrated with ...

and more »

Daily Mail

The 'magic carpet' software that helps fighter jet pilots make a perfect landing on a warship in ANY conditions
Daily Mail
The US Navy has revealed a radical new system allowing fighter pilots to land on deck safely. Called 'Magic Carpet' the software is being tested by F/A-18E/F Super Hornet and EA-18G Growler pilots across the Navy's fleet. It effectively acts as an ...

and more »

9 to 5 Mac

Apple rolling out macOS 10.12.4 software update with Night Shift for Mac
9 to 5 Mac
funny, that you mention case sensitivity. That is actually the biggest reason why I want APFS as soon as possible, because I'd really like a case sensitive filesystem for web development. I tried using HFS+ in it's case sensitive mode but a lot of ...

and more »
Google News

Microsoft Retail Management System (RMS) SQL Customization ? Overview for Programmer

Microsoft Retail Management System serves retail single store as well... Read More

Huddle Up; Groupware on Three

It could just be me, but my experiences with document... Read More

Reduce TCO: The Java Database Way

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

Great Plains Dexterity ? Microsoft Great Plains Customization Overview

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

Microsoft CRM: Data Conversion ? Import from Act!

Best Software Act! is very popular CRM for small and... Read More

Things You Can Do With Windows XP!

Did you ever give a thought to the number of... Read More

Spyware Definitions List

The adware and spyware definitions list is very long. But... Read More

Microsoft CRM ? Typical Customizations

Microsoft CRM was designed to be easily customizable. Microsoft CRM... Read More

Need of Document Management System (DMS)

Document Management or Enterprise Information Management is perhaps one of... Read More

The Opera Alternative

Security flaws have long plagued Internet Explorer (IE), the market-dominating... Read More

Microsoft Great Plains Nationwide Remote Support

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

Microsoft CRM Integration with Microsoft Retail Management System (RMS) ? Overview

Microsoft Client Relation Management system (Microsoft CRM) and Microsoft RMS... Read More

Microsoft Great Plains: Getting New Users Licenses ? Annual Service Plan FAQ

Microsoft Business Solutions Great Plains, Solomon, Navision, Axapta, Microsoft CRM... Read More

A Simple Computer Software Definition

What is Software?Software is a set of instruction written to... 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 Customization and Development ? Overview for Programmer

When Great Plains Software was designing and developing Great Plains... Read More

Microsoft CRM ? Consulting in the Post-Recession Time

New post-recession era has new features, which didn't exist in... Read More

Groupware: Answers the 5 Questions of Document Collaboration

Every organization which creates collaborative documents, whether they are budgets,... Read More

Microsoft Great Plains: Government & Non-Profit Organization ? Workflow Implementation

Usually workflow & messaging is realized in CRM and then... Read More

Microsoft Great Plains Customization Tools ? Overview

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

Software Review: Xsitepro Total Site Management Software

It's not very often I get excited about a software... Read More

RFID: Strengthen the Position for SAP; United States

SAP Inc., a global leader in client/server enterprise application software... Read More

Tripwire for Linux File Integrity

What is Tripwire?Tripwire is a form intrusion detection system (IDS)... Read More

Screenplay and Script Writing Software

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

History of Java

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