Software Information |
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 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.
MORE RESOURCES: Unable to open RSS Feed $XMLfilename with error HTTP ERROR: 404, exiting |
RELATED ARTICLES
Microsoft CRM Conversation Gateway: VoIP - Implementation & Customization Microsoft CRM is winning market share step-by-step from such the traditional CRM providers as Siebel, Saleslogix. In this article we'll show Microsoft Business Solutions CRM potential in the VoIP direction. Think Of This Think of this, first we had the HAM Radio, then the bbs and now the Internet. Linux and the various other x systems are the next evolutionary step toward enhancing Global 2-way communications. Microsoft Great Plains Installation - Overview for IT Director/Controller Microsoft Great Plains is main mid-market application from Microsoft Business Solutions. This is short article, written in question/answer/FAQ style to give IT Director/Controller balanced top level information on Microsoft Business Solutions Great Plains implementation. Start Developing Your Own Software Learning how to program software seems like a daunting task fit only for the geeks and nerds who lock themselves away in dark rooms staring at their monitors for days on end as they pour over thick manuals written largely in zeros and ones. Fortunately the reality is that it's not as complicated to make basic applications as people may think. Microsoft Great Plains Dynamics on Pervasive SQL.2000 Data Repair - Overview For DBAs Some introduction into Great Plains Software products, now Microsoft Business Solutions. Microsoft Great Plains becomes more and more popular, partly because of Microsoft muscles behind it. Microsoft Navision Customization and Reporting - Tips For Programmer/IT Specialist C/SIDE (Client/Server Integrated Development Environment) - The core of Navision is the C/SIDE. C/SIDE is the foundation for all the business management functionality of Navision. Guide to Software Marketing IntroThis concise article will tell you in plain English how you can promote your software easily and effectively.Submit your softwareI recommend submitting your software to upload. Separate Anti-Keylogging Protection: Who Needs it Most? If there still are few unprotected computers left, I haven't seen any.Every emerging threat causes means of defense to appear. Making The Decision To Automate Your Software Testing Not every software testing project can or should be automated. Before your department accepts a new test automation project, you should establish a process by which projects are reviewed and either accepted or rejected. Microsoft CRM or Siebel Siebel is traditional CRM market leader, however and mostly due to recession 200-2003, Siebel lost sizable portion of CRM market to new tiger, such as Microsoft CRM. Microsoft CRM s recent (2001) CRM answer from Microsoft and attempt top get market share from traditional vendors: Siebel, Oracle, Onyx. Five Tips For A Great Software Demo Whether you need to close a sale, gather end-user feedback, show progress to your customer, or simply explain how your product works, sooner or later, you will need to demo your software product.Over the years, I've had the opportunity to perform hundreds of demos to audiences of various sizes. SQL scripts for Project Accounting: Microsoft Great Plains series - overview for developer Microsoft Business Solutions Great Plains has Project Accounting module where you can budget the project, assign it to customer contract and then log expenses, timesheets, inventory spending, and finally bill the customer against the project or contract. Microsoft Great Plains Project Accounting does excellent job, but there are cases when you need developer or MS SQL DB Admin touches. Great Plains Dynamics/eEnterprise Upgrade - Things to Consider and FAQ If you have Great Plains Dynamics/eEnterprise (version 6.0 or earlier) and support it for your company then you need to know some technical details about Great Plains version upgrade and what is going on behind the scenes, which options do you have in case of Dexterity, VBA, SQL customization, additional complexity comes with migration from ctree/Pervasive to MS SQL/MSDE. 5 Mac Security Tips You Can't Live Without So, you've bought a new Macintosh, and now you may be wondering how to make it safer. There are several things that you can do which will protect your Mac from viruses and hackers. Dont Choose Adobe When Working With PDF While Adobe is the most known maker of PDF tools, your business will be much better off, if you select third party tools to work with PDF documents.ABC Amber PDF converterPriced at eleven dollars, this tool may very well be the cheapest PDF software on the planet. Troubleshoot Windows with Task Manager Task Manager is a Windows system utility that displays thetasks or processes currently running on your computer. Toopen Task Manager, press Ctrl+Alt+Del. eConnect: eCommerce Development for Microsoft Great Plains Microsoft Business Solutions Great Plains has several options to enable web ordering. Traditionally Great Plains Dynamics/eEnterprise had eOrder - this is ASP pages based ordering application, enabling you to place or retrieve your Sales Order Processing (SOP) Sales Orders over the web. Lotus Domino Implementation and Development: Infrastructure - Present and Future Domino server is a buffer between the operation system and the application. On the other hand Domino server - consists of the services which support development processing and executing of dynamic, secure applications for Internet, Intranet, or Extranet. What is Shareware? Shareware is software that you can try before you buy; shareware is a kind of marketing method for software. Software developers post trial versions of their software on websites. Microsoft Great Plains: Getting New Users Licenses - Annual Service Plan FAQ Microsoft Business Solutions Great Plains, Solomon, Navision, Axapta, Microsoft CRM require existing customer to be on active service contract: enhancement program. Minimal maintenance plan gives you free new software version (however upgrade itself, if it is done by your MBS partner is not free). |
home | site map | contact us |