Home
>
Software
>
Navision Attain C/ODBC Crystal Report ? Customization Example

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 Midway Glen Ellyn ..

In The News:

The Kimberley Kube trail-ready camper has a compact but spacious design and combines luxury, functionality and ruggedness for a weekend getaway.

Stay up to date on the latest AI technology advancements and learn about the challenges and opportunities AI presents now and for the future.

Emails instructing you to reset your password for an account may be legitimate, or they may be scams. Kurt "CyberGuy" Knutsson explains.

Northrop Grumman's Manta Ray uncrewed underwater vehicle aims to revolutionize undersea missions — it glides through the ocean without human assistance.

Learn how to work Google's calendar application to streamline and organize your daily tasks from technology expert Kim Komando.

If you do not want Facebook to have automatic access to your private photos, follow our tips to protect yourself. Kurt “CyberGuy" Knutsson shows you how.

Kurt “CyberGuy" Knutsson goes into detail about Apple’s recent iOS update that allows iPhone users to instantly translate spoken language simply by using the Action Button.

Safeguarding your digital life with a reliable physical backup isn't just a precaution, it's a necessity. Kurt “CyberGuy" Knutsson provides the essential backup checklist.

Kurt “CyberGuy" Knutsson reveals how a Redditor exposed false recycling claims at their apartment, highlighting a report that only 21% of U.S. recyclables are processed.

Kurt "CyberGuy" Knutsson offers a travel toolkit featuring five technology tools to help you with booking flights and hotels for your summer vacation.

The bubble behind the clock on your iPhone can appear in different colors. Kurt "CyberGuy" Knutsson explains what each of those colors mean.

Scammers are using the power of artificial intelligence to mimic voices of people and are using the fake voices to commit crimes, like kidnappings.

Stay up to date on the latest AI technology advancements and learn about the challenges and opportunities AI presents now and for the future.

Tech guru Kurt "CyberGuy" Knutsson explains the science behind the Invisibility Shield, a 6-foot shield that makes people become invisible.

The International Olympic Committee on Friday announced plans to use AI in various Olympic aspects, including athlete identification, training and judging.

Tech guru Kurt "CyberGuy" Knutsson explains an easy trick to avoid squinting while working or surfing the web by zooming in on your personal computer.

Streaming giant Roku has recently been targeted by a pair of cyberattacks, and the company confirmed over a half million Roku accounts were compromised.

The Land Aircraft Carrier combines an all-terrain, six-wheeled vehicle with a two-seat aircraft, which features electric vertical takeoff and landing.

The European Union has sent TikTok a "request for information" on the video sharing platform's newest app, TikTok Lite, under the Digital Services Act, with the aim to clean up social media.

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 is warning the public about a recent phishing scam via text that claims its targets owe money in Pennsylvania for unpaid road toll charges.

Your Apple Music settings may be revealing to your contacts what you're listening to. Kurt "CyberGuy" Knutsson explains the process to change those settings.

Learn how to secure social media accounts, implement legacy contacts, and create a digital estate plan to protect your online assets from 'ghost hackers' after your death.

A ported phone number scam is leading to more people having their identities stolen by sophisticated scammers. Kurt “CyberGuy" Knutsson tells you what you need to know.

The Great Pacific Garbage Patch, a vast marine debris vortex, is being tackled by The Ocean Cleanup’s innovative technologies. Kurt “CyberGuy" Knutsson explains.