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.

limo prices to midway Crystal Lake west of Randal .. Lockport Chicago limo O’Hare

In The News:

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 number of people affected by a UnitedHealth data breach in February 2024 was actually higher than previously reported and was the largest medical data breach in U.S. history.

A phishing campaign uses Google Calendar to schedule fake meeting invitations that appear legitimate, redirecting targets to phishing sites.

Toll road text scam: Fake messages claim unpaid fees, seek payment via fraudulent links. Kurt “CyberGuy" Knutsson says this scam is becoming increasingly sophisticated and widespread.

Kurt “CyberGuy" Knutsson says these steps ensure that your friends and family get to the exact moment you want them to see. Try it out and streamline your sharing experience.

Tech expert Kurt “CyberGuy" Knutsson offers tips on how to protect your identity: Be vigilant, monitor accounts, use smart security, know theft response.

Tech expert Kurt “CyberGuy" Knutsson says cybercriminals are exploiting iMessage phishing protection, tricking users to reactivate links.

It's hard to imagine life without in-flight Wi-Fi these days – but sky-high web surfing comes with its own set of privacy concerns. Here's how to protect yourself.

Over 400,000 cases of credit card fraud were reported in 2024 to the Federal Trade Commission. The CyberGuy explains how to protect against becoming another victim.

A nondescript metal box transforms into a fully functional electric motorcycle that blurs the line between vehicle and urban infrastructure.

Many apps unrelated to location still ask users for tracking permission, and Texas Attorney General Ken Paxton has filed a lawsuit seeking to end this alleged practice by Allstate.

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

Melody, the artificial intelligence-powered robot, is life-sized and represents a move toward creating robots that resemble people in appearance and interaction.

Here are five easy smartphone rules from the National Security Agency that you can follow to better protect your mobile device from hackers and scammers.

A U.S. robotics company has developed "Jennie," a robotic pet powered by artificial intelligence that's designed to comfort those facing mental health challenges.

Healthcare records for more than 184 million Americans were breached in 2024, but you can take steps to minimize that risk. Kurt the CyberGuy explains.

The Skyrider X1 from Rictor claims to be the "first amphibious flying passenger motorcycle," and it combines land and air travel in one sleek design.

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

Home inventory and restoration apps help itemize and track every item in your home in the event you have to make an insurance claim after a natural disaster.

The White House launched a new cybersecurity safety label, the U.S. Cyber Trust Mark, intended to help consumers make informed decisions on smart device safety.

Today's tech can help make life so much easier — if you know how to use it. Here are a few tips on the quickest and best ways to make your phone, laptop and other devices work for you.

Tech expert Kurt “CyberGuy" Knutsson offers Windows 11 restart fixes: Update drivers, run system scans and check hardware.

Sony and Honda's Afeela 1 EV launches with advanced tech and entertainment for $89,900. Tech expert Kurt “CyberGuy" Knutsson gives his takeaways.

With limited hard drive space, tech expert Kurt “CyberGuy" Knutsson offers these tips for storing large video files and photos.

Tech expert Kurt “CyberGuy" Knutsson says the $236,000 Yangwang U9 electric hypercar boasts 1,287 horsepower and can jump obstacles.