Microsoft Business Solutions Navision is the leading ERP application for the European and Brazilian markets, as well as serving vertical segments of the US market 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 empower 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: the creation of linked servers in the Microsoft SQL Server 2000 environment, and then the design of sales reports in Crystal Reports ver. 10. Let’s get started:
1. We will use Navision Attain 3.6 with Navision Database Server, Navision ApplicationServer and Navision Client. These components are installed on Windows XP Professional. To enable the C/ODBC interface, you must install this component from the 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 the 32-bit C/ODBC driver. Provide the name of the Navision data source, Connection – leave it Local. As the 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. Important conditions for providing a proper connection to the C/SIDE database are correct choices for C/ODBC. Click the Options button and take a look at the available options; we will pay attention to the Identifiers screen; this is where you configure the identifiers to be passed to the client application. We will use these types: “az,AZ,0-9,_” to make MS SQL Server 2000 work correctly with the C/ODBC source. Now we are done with ODBC DSN. Let’s configure the linked server
3. Open SQL Server Enterprise Manager. Open the object browser 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, type the ODBC DSN name: NAVISION in our case. The linked server is ready. Select the list of tables and now we see the Navision Attain tables.
4. Now we need to create a small procedure for the Sales data extraction. 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
WE 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 don’t use this directive, we can’t extract the results from 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 an 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 procedure type 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 work, give us a call at 1-866-528-0577 or 1-630-961-5918! [email protected]