Navision Attain C/ODBC Crystal Report – Customization Instance

Microsoft Organization Solutions Navision is primary ERP software for European, Brazilian marketplaces additionally it serves vertical segments of the Usa industry, these types of as light production, POS, CRM. As our practical experience implies – Navision implementation needs additional customization and tuning in comparison to another mid-sector ERP option from MBS: Microsoft Excellent Plains. Our aim is to popularize Microsoft Business Options products and solutions and enlighten IT persons to do customize Navision in-house.

Currently, the primary matter of this short article is functioning with native C/Side Navision Attain databases through C/ODBC interface, in certain – Linked Server development in Microsoft SQL Server 2000 ecosystem and then Profits report design in Crystal Experiences ver. 10. Let us commence:

1. We will use Navision Achieve 3.6 with Navision Databases Server, Navision ApplicationServer and Navision Consumer. These ingredient are mounted on Home windows XP Professional. To enable C/ODBC interface you want to set up this element from Navision Attain CD.

2. Let us make ODBC DSN for relationship to Navision databases: Get started -> Manage Panel -> Administrative Instruments -> Details Sources (ODBC), then switch to Method DSN and click Incorporate. We will use C/ODBC 32-little bit driver. Give Facts Supply Name Navision, Link – go away it Local. As the database (Database button) decide on Application FilesNavision AttainClientdatabase.fdb (this is demo DB). Then press Enterprise button – in our circumstance we use CRONUS demo database. Vital disorders to provide appropriate link with C/Aspect databases are suitable selections for C/ODBC. Simply click Selections button and seem at the selections obtainable – we will fork out focus to Identifiers display – this is exactly where you set up identifiers to be transferred to client software. We will use these kinds: “a-z,A-Z,-9,_” to make MS SQL Server 2000 do the job correct with C/ODBC source. We are now performed with ODBC DSN. Lets setup Connected Server

3. Open up SQL Server Company Manager. Open up object browser on the left aspect, select Protection and Joined Servers. With proper click choose New Connected Server in the context menu. In the emerging dialog window, in the menu Provider Title find Microsoft OLE DB Company for ODBC Motorists. Let us title our Joined Server as NAVISION. In the Knowledge Source string variety in ODBC DSN identify – NAVISION in our case. Linked Server is all set. Choose tables listing and we now see Navision Attain tables.
4. Now we require to build small treatment for Revenue data extraction. Right here is the textual content of the treatment:
Create Treatment NavisionSalesReport AS

DBCC TRACEON(8765)

Decide on * FROM OPENQUERY(NAVISION, ‘SELECT * FROM Sales_Line sls, Shopper cust In which sls.Provide_to_Purchaser_No_ = cust.No_’)

RETURN

GO

To explain its textual content: TRACEON(8765) directive allows operating with the info final results of variable duration returned by C/ODBC driver. If we do not use this directive – we cannot pull the benefits from Navision tables – we will get problems, 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, Condition 1, Line 1
OLE DB supplier ‘MSDASQL’ returned an unpredicted info duration for the fastened-size column ‘[MSDASQL].Ship_to_Filter’. The envisioned data size is 250, although the returned data duration is 1.

OPENQUERY assertion opens connected server and transfers its SQL command, and in its change returns the result established. To check the technique type in this command in SQL Server Query Analyzer: EXEC NavisionSalesReport
5. Now – launch Crystal Studies, use conventional Wizard, make new OLE DB (ADO) connection with our MS SQL 2000 Server and pick NavisionSalesReport technique in the checklist. As the report fields we can use Description, Quantity, Line_Total and Discount_Sum fields. You can team by identify Title – this is customer name. Our report is now ready!
Pleased customizing, applying and modifying! If you want us to do the work – give us a call 1-866-528-0577 or 1-630-961-5918! [email protected]