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.
Bradley bmw rental chicago .. Lockport Chicago limo O’HareThis tutorial covers OLAP solutions used by Data warehouses and... Read More
The purpose of one of our projects was MS Exchange... Read More
As Mozilla Firefox nears 10% market share, with well over... Read More
Midsize business or non-profit organization should decide if one-vendor solution... Read More
Traditionally we were considering functionally rich systems, such as SAP,... Read More
Case study: A secretary using Corel WordPerfect 7 is often... Read More
Find out why Spyware Removal from computers is important and... Read More
Some companies that are in need of fleet management may... Read More
Microsoft Great Plains and Microsoft CRM become more and more... Read More
Using professional icons in your application or website can bring... Read More
IntroductionSurprised, by the title? well, this is a tour of... Read More
If you're online using a dialup Internet connection, you'll probably... Read More
Bad News - the Threat is Bigger than it SeemedHow... Read More
If you have been running Windows XP for a couple... Read More
The Windows Indexing Service provides you with the ability to... Read More
IBM Lotus Notes with Domino email server is traditional document... Read More
Did you ever want to erase everything on your computer?... Read More
If you have Microsoft Great Plains and support it... Read More
Microsoft Business Solutions Great Plains is very popular ERP/MRP applications... Read More
With any good luck and a good amount of hard... Read More
How many steps does it take you to locate and... Read More
The fact that a software tester is a most infamous... Read More
New post-recession era has new features, which didn't exist in... Read More
Lotus Domino/Notes ? Microsoft Great Plains tandem as ERP with... Read More
What is Spyware?Spyware monitors your surfing habits and sends the... Read More
prom limo McHenry ..Microsoft CRM is CRM answer from Microsoft Business Solutions. If... Read More
While several preventive maintenance software manufacturers offer free trials for... Read More
Hi, Guys,I believe a lot of programmers are trying to... Read More
I have always regretted how Microsoft price gouges and rips... Read More
Not every software testing project can or should be automated.... Read More
Did you ever want to erase everything on your computer?... Read More
The intuitive algorithm.Roger Penrose considered it impossible. Thinking could never... Read More
Bar charts, bar graphs, and any other chart or graph... Read More
Microsoft-Outlook is a pretty amazing program. So much more than... Read More
Microsoft Great Plains serves the wide spectrum of horizontal markets.... Read More
Learning how to program software seems like a daunting task... Read More
In our case ? we serve Microsoft Business Solutions ERP... Read More
Microsoft Great Plains might be considered as ERP platform to... Read More
If you are to buy a HelpDesk & Asset Management... Read More
Our company, Novaprof Inc., developed unique software - DB Integration.... Read More
Shareware has been fighting the stigma of being misunderstood for... Read More
We all take the computer for granted. I mean, all... Read More
MSN messenger is a pretty cool invention. I mean I'm... Read More
This tutorial covers OLAP solutions used by Data warehouses and... Read More
DBxtra is a powerful query and reporting tool that hides... Read More
If you are software developer or database administrator - we... Read More
As we could imagine, if you are reading this article... Read More
... Read More
Enabling Chinese input is quick and easy, there are only... Read More
Most people understand that the "hardware" part of their computer... Read More
Software |