Navision Attain C / ODBC Crystal Report – Customization Example

Microsoft Business Solutions Navision is the leading ERP application for 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 MBS mid-market ERP solution – Microsoft Great Plains. Our goal is to popularize Microsoft Business Solutions products and inform IT staff 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 Microsoft SQL Server 2000 environment and then designing reports from sales 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 in 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 Files Navision Attain Client database.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 options for C / ODBC. Click the Options button and 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, _” so that MS SQL Server 2000 works 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 window, from the Provider Name menu, select Microsoft OLE DB Provider for ODBC Drivers. We name our linked server as NAVISION. In the data source string, write the name ODBC DSN – 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 the extraction of sales data. 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 GO

To clarify your wording: The TRACEON directive (8765) allows you to work with the results of variable length data returned by the C / ODBC driver. If we don’t use this directive, we can’t extract the results from Navision tables, we’ll get errors, like this:

OLE DB Bug Tracking [Non-interface error: Unexpected data length returned for the column: ProviderName=’MSDASQL’, TableName='[MSDASQL]’, ColumnName =’ Ship_to_Filter ‘, ExpectedLength =’ 250 ‘, ReturnsLength =’ 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 length of the data is 250, while the length of the returned data is 1.

The OPENQUERY statement opens the linked server and passes its SQL command, and in turn returns the result set. To test the procedure, type 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 customization, implementation and modification! If you want us to do the job, call us at 1-866-528-0577 or 1-630-961-5918. [email protected]