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.
Culver prom limo ..After seeing many people complain about their weak Internet security... Read More
Microsoft Business Solutions Great Plains is very popular ERP/MRP applications... Read More
Best Software Act! is very popular CRM for small and... Read More
Disclaimer: All the thoughts expressed are my views only! Your... Read More
When it comes to running an office, the SOHO entrepreneur... Read More
When Windows fails to boot it is normally caused by... Read More
Microsoft Business Solutions Navision is main ERP application for European,... Read More
Microsoft Great Plains is main Microsoft Business Solutions accounting package... Read More
Great Plains Integration Manager scripting and translation - overview for... Read More
TCO (Total Cost Ownership) is the buzzword in... Read More
When Great Plains Software introduced the first graphical accounting application... Read More
IntroductionSurprised, by the title? well, this is a tour of... Read More
Microsoft CRM is relatively new player on the now becoming... Read More
One of the main reasons business owners and entrepreneurs use... Read More
Microsoft Business Solutions Great Plains, former Great Plains Software Dynamics... Read More
Program Flow is what you think it is. How the... Read More
Traditionally we were considering functionally rich systems, such as SAP,... Read More
Microsoft Business Solutions CRM data conversion deserves FAQ type of... Read More
While Adobe is the most known maker of PDF tools,... Read More
Whether you need to close a sale, gather end-user feedback,... Read More
Document Manager and Version HistoryIn previous articles I have discussed... Read More
Anti-virus software is used to find, remove or fix files... Read More
As seeing large number of implementations ? in our case... Read More
You probably didn't casually invite, or extend a formal attendance... Read More
Does Microsoft Have any Real Competition? Copyright (c) 2003 Gregory... Read More
Wood Dale limo ..With many manufacturing shops heading over seas in favor of... Read More
Fleet Maintenance Management is a critical position in any company... Read More
According to a survey conducted by InfoTrends/CAP Ventures entitled "Content-Centric... Read More
In our opinion, traditional approach when you select ERP/MRP system... Read More
In the new era of internet marketing the problem of... Read More
Task Manager is a Windows system utility that displays thetasks... Read More
In the real world a "fire wall" is a fireproof... Read More
Microsoft Great Plains, Navision, Solomon and Axapta are Microsoft Business... Read More
Make-or-Break Factors in Success and ProfitabilityFor quick printers, estimating can... Read More
The adware and spyware definitions list is very long. But... Read More
Programming Help for BeginnersWe write programs to instruct computers. When... Read More
Not all of us have the luxury of working both... Read More
If you company is small or mid-size special products or... Read More
The Windows Indexing Service provides you with the ability to... Read More
Looks like Microsoft Great Plains becomes more and more popular,... Read More
Upgrading. Downtime. Maintenance. Hardware obsolescence. Implementation issues. The litany of... Read More
Although we don't know whether Microsoft ever envisioned such a... Read More
Microsoft Great Plains ? ERM from Microsoft Business Solutions and... Read More
Microsoft bought Navision, Denmark based software development company, along with... Read More
Anyone who has ever used Microsoft Word knows that it... Read More
Since technology changes so quickly, it is hard to begin... Read More
Find out why Spyware Removal from computers is important and... Read More
Healthcare facilities such as clinics, hospitals, and biomedical laboratories can... Read More
CD and DVD replication is a process that works by... Read More
There is many things more frustrating than surfing a website... Read More
Software |