Crystal,Reports,for,SQL,Based, computer Crystal Reports for SQL Based ERP Systems: SAP Business One,
----------------------------------------------------------Permission is granted for the below article to forward,reprint, distribute, use for ezine, newsletter, website,offer as free bonus or part of a product for sale as longas no changes a Gone are those times when the companies and the organisations didn't need a hi-tech system to handle them. Owing to the considerable increase in the business sector and thus, an enormous increase in the complexity of the organisational struc
Talking about specific Corporate or Small Business ERP platform here you need to know table structure in order to produce SQL View. In this small publication we would like to give you two examples: SAP Business One and Microsoft Dynamics. Lets begin with advantages of SQL View and Stored Procedure as report data pulling:1. SQL View. Lets assume that you do not want to do select statement test directly in Crystal and would like to avoid such annoying issues as duplicate rows and incorrect summaries. Plus you would like to use such constructions as SQL Union. And in addition to that you do not want to create the whole report and then potentially realize that it doesnt work as expected and you would have to redo tables linking and redesign pretty much the whole report. Then what you may decide to do is create SQL View and test its results in SQL Server Management Studio2. Stored Procedure. CR is treating final Select statement or Union as its base. Stored Procedure parameters are translated into Crystal parameters. What are the advantages of Stored Procedure? Well in SP you are not restricted to just sophisticated joins in Select statement or Union. Here you can use parameters, temporary tables, data update (be cautious do not compromise data integrity!). It is also possible to use Exec statement and build Select statement on the fly analyzing the set of parameters. You can also use SQL cursors especially in building temporary tables. Stored Procedure could give you the advantage of better performance comparing to the view as here Crystal doesnt have to apply restrictions to the total View selection and all the job are done in high performance SQL Server. Lets now move on to SAP B1 tables and fields3. SAP Business One. If you are not doing long term programming project in Software Development Kit then there is no need to install it and review tables diagram from there. We recommend you just login SB1 user workstation and in menu mark View -> System Information. Lets now assume that your custom report will need to pull the fields from Sales Invoice. Open Sales invoice form and place the cursor over the field in question. Table and field name information will be displayed in the bottom left corner of the user interface. Some comments before you begin your SQL View or Stored Procedure creation. SAP BO is three tiers Small Business ERP application. This means that direct data update via custom SQL statements will be detected by Watch Dog service and transmitted to SAP. Consider just data selection and try to avoid updates on the SQL Stored Procedure level4. Microsoft Dynamics ERP and CRM Family. There are several products where direct select from SQL Server database is OK. Among them GP (formerly known as Great Plains), NAV (former Navision), SL (originally Solomon) and CRM. In the case of Dynamics AX Axapta direct access to SQL Server is not recommended and instead you should consider X++ code and such development tool as MorphX. Microsoft actually is trying deemphasize Crystal Reports and rather recommend SQL Server Reporting Services (SSRS)5. Financial Reporting where Crystal is not recommended. General Ledger is tightly connected with Accounting theory and double entry. If you need to produce Balance Sheet, Profit and Loss Statement or Statement of Cash flow including consolidated versions please consider such tools as Microsoft Management Reporter or its predecessor FRx. If you decide to recreate P&L directly in Crystal you should expect to reproduce most of FRx business logic and this is in our opinion not a feasible idea6. Please call us 1-866-304-3265, 1-269-605-4904 (for international customers, where our representative pick up the phone in St. Joseph, MI call center) [email protected]. We have local presence in Chicagoland, Southern California, South West Michigan, Houston and Dallas areas of Texas. We serve customers USA, Canada, Mexico, Brazil nationwide and internationally via web sessions and phone conferences (Skype is welcomed). Our consultants speak English, Spanish, Portuguese, Russian, Chinese. Our core expertise is in International Business
Crystal,Reports,for,SQL,Based,