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 to Midway Beardstown .. Lockport Chicago limo O’Hare

In The News:

The FBI has issued a warning that scammers are impersonating doctors, police and banks using spoofed numbers as "smishing" texts surge nationwide.

Communities with unreliable internet service stand to gain after Amazon's Project Kuiper launched its first full batch of satellites into orbit April 28.

Tech expert Kurt “CyberGuy" Knutsson says unsubscribing from spam might actually backfire. Learn when to avoid it and stop junk effectively.

Tech expert Kurt “CyberGuy" Knutsson talks about how the first nonstop beating heart transplant, called zero ischemic time, at Taiwan hospital changes surgery.

Kurt “CyberGuy" Knutsson: FBI warns of "time-traveling" hackers.

Kurt “CyberGuy" Knutsson talks about a soft, vine-like robot called SPROUT that aids safe survivor rescues in collapsed buildings.

Health insurance giant Blue Shield of California confirmed it had been sharing private health data of 4.7 million users with Google for three years without even realizing it.

Delta and JetZero’s blended wing body aircraft marks a meaningful step toward a cleaner, quieter and more efficient future for air travel.

Recent reports show many common passwords can be cracked in literally seconds. Kurt the CyberGuy explains how to strengthen your passwords.

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

Iron, a robot that stands 5 feet, 8 inches tall and weighs 154 pounds, combines advanced artificial intelligence with human-like movement and exceptional vision.

Hertz, the rental car giant, recently confirmed that customer information was exposed through a cyberattack on one of its software vendors.

There are a number of features with AirPods you may or may not know about to take your listening experience to the next level. Kurt the CyberGuy explains.

These 35 Chrome extensions have privacy and security concerns. Tech expert Kurt “CyberGuy" Knutsson says to delete them now.

Tech expert Kurt “CyberGuy" Knutsson says 329,000 mph fusion rocket promises to be fast, disruptive and enable deep-space missions.

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 expert Kurt “CyberGuy" Knutsson says a new autonomous AI is a game changer that also raises privacy risks. Is your data safe?

Tech expert Kurt “CyberGuy" Knutsson says robots and drones are revolutionizing fruit farming with faster picking and smarter handling.

Landmark Admin revises May 2024 cyberattack scope to show twice as many people were affected. Kurt “CyberGuy" Knutsson gives tips to help stay safe from an insurance data breach.

Tech expert Kurt “CyberGuy" Knutsson talks about how Yamaha’s hydrogen outboard motor could revolutionize boating with zero emissions.

Tech expert Kurt “CyberGuy" Knutsson reveals how to memorialize or remove a deceased loved one’s Facebook account and protect their digital legacy from misuse or scams.

Tech expert Kurt “CyberGuy" Knutsson says an Apple Watch saved psychiatrist Amanda Faulkner by detecting deadly leukemia early.

Scammers and fraudsters are increasingly targeting the most vulnerable, especially nursing homes and the personal data of their residents. Kurt the CyberGuy has safety tips.

Infected USB flash drives can spread malware among multiple organizations in ways that can easily bypass traditional security systems.

With a fully automated warehouse system and AI-powered robots, Ocado's Hive picks, packs and delivers grocery store orders in just a few minutes.