Microsoft Business Solutions Navision is the leading ERP application for the European and Brazilian markets, as well as serving the US market verticals such as light manufacturing, POS, CRM. As our experience indicates, implementing Navision requires more customization and tuning compared to another mid-market ERP solution from MBS: Microsoft Great Plains. Our goal is to popularize Microsoft Business Solutions products and inform IT people to customize Navision internally.
Today, the main topic of this article is working with the native C/SIDE Navision Attain database through the C/ODBC interface, in particular: creating linked servers in the Microsoft SQL Server 2000 environment and then designing sales reports in Crystal Reports ver. 10. Let’s get started:
1. We will be using Navision Attain 3.6 with Navision Database Server, Navision ApplicationServer and Navision Client. These components are installed in Windows XP Professional. To enable the C/ODBC interface, you must install this component from the Navision Attain CD.
2. Let’s create the ODBC DSN for the connection to the Navision database: Start -> Control Panel -> Administrative Tools -> Data Sources (ODBC), then switch to System DSN and click Add. We will use the 32-bit C/ODBC driver. Provide the name of the Navision data source, Connection – leave it Local. For Database (Database button), select Program FilesNavision AttainClientdatabase.fdb (this is the demo database). Then press the Company button – in our case we use the CRONUS demo database. The important conditions for providing a proper connection to the C/SIDE database are correct choices for C/ODBC. Click the Options button and note the available options; we will pay attention to the Identifiers screen; this is where you configure the identifiers to pass to the client application. We will use these types: “az,AZ,0-9,_” to make MS SQL Server 2000 work correctly with C/ODBC source. Now we are done with ODBC DSN. Let’s set up the linked server
3. Open SQL Server Enterprise Manager. Open the navigator object on the left side, select Security and Linked Servers. Right-click, select New Linked Server from the context menu. In the pop-up dialog, from the Provider Name menu, select Microsoft OLE DB Provider for ODBC Drivers. Let’s call our Linked Server NAVISION. In the data source string, write the ODBC DSN name: NAVISION in our case. The linked server is ready. Select the table list and now we see the Navision Attain tables.
4. Now we need to create a small procedure for extracting the Sales data. This is the text of the procedure:
CREATE PROCEDURE NavisionSalesReport AS
DBCC TRACE(8765)
SELECT * FROM OPENQUERY(NAVISION, ‘SELECT * FROM Sales_Line sls, Customer cust WHERE sls.Sell_to_Customer_No_ = cust.No_’)
RETURN
GO
To clarify your text: The TRACEON(8765) directive allows you to work with variable-length data results returned by the C/ODBC driver. If we do not use this directive, we cannot extract the results from the Navision tables, we will get errors like this:
OLE DB error tracing [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 unexpected data length for fixed-length column ‘[MSDASQL].Send_to_filter’. The expected data length is 250, while the returned data length is 1.
The OPENQUERY statement opens the linked server and passes your SQL command, and in turn returns the result set. To test the type of procedure in this command in SQL Server Query Analyzer: EXEC NavisionSalesReport
5. Now start Crystal Reports, use the standard wizard, create a new OLE DB (ADO) connection to our MS SQL 2000 server and select the NavisionSalesReport procedure from the list. As report fields, we can use the Description, Quantity, Line_amount and Discount_amount fields. You can group by name Name: This is the name of the customer. Our report is ready!
Happy customizing, implementing, and modding! If you want us to do the job, give us a call at 1-866-528-0577 or 1-630-961-5918! [email protected]