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 1OLE 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 NavisionSalesReport5. 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.
|
|
|
Microsoft Crm In Latin America: Implementation, Customization, Support ? Overview For Consultant
Microsoft Business Solutions CRM is present several years on the US software market plus it makes a...(related: Software)
Great Plains Dexterity Development: Faq
Microsoft Business Solutions Great Plains is Dexterity-written application and currently we see increased interest for Great Plains customers to do in-house Dexterity development and customization. Dexterity itself is written on C programming language and its initial architecture was based on the Graphical and Database platform independence, which C programming language was believed to provide. Initially back in 1994 Great Plains Software Dynamics and Dynamics C/S+ were realized for Macintosh and Windows and pretty similar Dexterity DYNAMICS.DIC dictionary worked for Ctree/Faircomm and Microsoft SQL Server 6.5. In our days Microsoft Great Plains is available for Windows and MS SQL Server only (since version 8.0). In this small article we would like to give you highlights on the most typical entry level Dexterity development question...(related: Software)
Implementing Erp For Large Publicly Traded Corporation ? Microsoft Great Plains
We would like first emphasize the change in the paradigm. If you look back ? 20 years or even 10 years ? those days you saw ERP/MRP monster applications/systems winning time. Everyone wanted to ...(related: Software)
Cisco Certification: Introduction To Isdn, Part V
The major reason I recommend getting your hands on real Cisco equipment rather than a simulator is that real Cisco routers give you the chance to practice and learn show and debug commands.The knowledge you acquire from debugs is invaluable. Frankly, itâ??s this knowledge that puts you above the â??averageâ?? CCNA who doesnâ??t have that hands-on experience. Watching debugs in action also gives you a head start on the CCNP. Since 90 â?" 95% of CCNAs go on to pursue the CCNP, itâ??s a great idea to get started with debugs now.Donâ??t make the mistake of waiting until youâ??re studying for your CCNP and CCIE to start learning debugs and shows. The work you do for the CCNA is the foundation for everything youâ??ll do in ...(related: Software)
Great Plains Accounting Migration To Microsoft Great Plains - Overview For It Specialist
This is a short article, written in question/answer/FAQ style to give IT Specialist/developer/programmer balanced top level information on Great Plains Accounting migration to Microsoft Business Solutions Great Plains. If you have Great Plains Accounting as main accounting and ERP system you need to know some technical details on the migration to Great Plains and what is going on behind the scenes. As of right now it...(related: Software)
Five Steps To Rapid Development With Tierdeveloper 4.0
Follow the steps below to quickly design, generate, and deploy a great portion of your real-life N-Tier application.STEP 1: HAVE YOUR DATABASE READY1. Create your database in Microsoft SQL Server, Oracle, or DB2. You can design and create your own database. Or, you can use the sample database provided with TierDeveloper. Or, you can use one of the databases/schemas already provided with your database. They are:
An Introduction To .net Framework
.Net Framework is a platform or development environment to seamlessly create web-applications that are accessible through client machines from across the globe. These web-applications adopt open s...(related: Software)
Microsoft Great Plains Jewelry ? Implementation & Customization Overview
Each Industry and market niche has business specific and unique requirements to ERP/Accounting System. Microsoft Business Solutions Great Plains is ERP platform, ready for third party modules additions and customizations, plus being SQL Server-based system it opens you wide spectrum of integration options and tools. In this small article we'll give you Microsoft Great Plains implementation and setup highlights, typical for Jewelry retail network.? Fixed Assets. Jewelry retailer owns the network of jewelry retail outlets and each one has relatively expensive set of fixed assets: counters, holders, computers, etc. Co...(related: Software)
Getting Technical Support Help Online
Sometimes your PC will start acting strange for no apparent reason or a programwhich you use regularly will all of a sudden stop working as it should. Thiscan be a frustrating experience especially if you have no idea where to go forhelp. If this happens to you, don't panic! Here are some simple tips which mayturn your frown upside down.1. Don't assume that ...(related: Software)
Twelve Things You Should Know To Save On Computer Software
Do you want to get quality software at a reasonable price? Whether you work at home or just need good software for your personal computer, you are faced with an almost endless selection of computer software. Sorting through the options can sometimes be a major task. However, if you want to get good, quality softwar...(related: Software)
Microsoft Great Plains Inventory Control ? Overview For Consultant
Microsoft Business Solutions Great Plains is marketed for mid-size companies as well as Navision (which has very good positions in Europe and emerging markets where it can be easily localized). Great Plains Inventory control is pretty robust and here we would like to give you highlights on standard functionality as well as what could be added to its standard features.Historically Great Plains Dynamics / eEnterprise was designed with modules structure with very clear ERP workflow ? Inventory control had a...(related: Software)
New Sql Delta Version 3.1
COMMAND LINE FUNCTIONA powerful command line script processor has been introduced to SQL Delta 3.1. The script processor provides the user with a mechanism for controlling SQL Delta by remote control, allowing databases to be compared and changes scripted without any user intervention. The script language is standard XML and provides a variety of commands for configuring and controlling SQL Delta. In addition to the standard comparison and script functions the user can export reports in various formats including HTML format and then have those repo...(related: Software)
Destination: Desktop For Google
First we had the original Google search that evolved into the leader in its class. In fact, it became so popular ...(related: Software)
site-map - Copyright © 2008 | Contact Webmaster | All Rights Reserved. | Software