Tag Archives: stored

Crystal Reports for SQL Based ERP Systems SAP Organization One particular, Microsoft Dynamics

Crystal Reports for SQL Based ERP Systems SAP Organization One particular, Microsoft Dynamics

SQL Server opens new horizons in Crystal Reports design and deployment. Right here you possibly can separate the report presentation from data choice layer. Instead of linking tables directly in CR it is easy to now develop SQL View or Stored Process and test their outcomes prior to opening Crystal Reports Designer. Speaking about particular Corporate or Smaller Enterprise ERP platform – here it is advisable to know table structure in order to create SQL View. In this modest publication we would like to offer you two examples: SAP Business enterprise A single and Microsoft Dynamics. Let’s start with advantages of SQL View and Stored Procedure as report information pulling:

1. SQL View. Let’s assume that you don’t prefer to do choose statement test directly in Crystal and would like to keep away from such annoying problems as duplicate rows and incorrect Mcsa Exams summaries. Plus you’d like to use such constructions as SQL Union. And as well as that you just do not wish to develop the entire report and then potentially comprehend that it does not perform as expected and you would must redo tables linking and redesign fairly substantially the entire report. Then what you may determine to perform is make SQL View and test its outcomes in SQL Server Management Studio

2. Stored Procedure. CR is treating final Select statement or Union as its base. Stored Process parameters are translated into Crystal parameters. What are the positive aspects of Stored Process? Properly in SP you’re not restricted to just sophisticated joins in Select statement or Union. Here you can use parameters, temporary tables, data update (be cautious usually do not compromise data integrity!). It really is also probable to use Exec statement and develop Choose statement on the fly analyzing the set of parameters. You can also use SQL cursors specially in creating temporary tables. Stored Procedure could give you the advantage of much better performance comparing towards the view as here Crystal doesn’t must apply restrictions towards the total View selection and each of the job are accomplished in high efficiency SQL Server. Let’s now move on to SAP B1 tables and fields

3. SAP Organization 1. For anybody who is not doing long-term programming project in Computer software Development Kit then there’s no should install it and review tables diagram from there. We recommend you just login SB1 user workstation and in menu mark View -> System Information. Let’s now assume that your custom report will should 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 Process creation. SAP BO is three tiers Compact 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 information selection and try to steer clear of updates on the SQL Stored Process level

4. Microsoft Dynamics ERP and CRM Family. There are several products where direct pick from SQL Server database is OK. Among them GP (formerly known as Great Plains), NAV (former Navision), CCNA Certification SL (originally Solomon) and CRM. In the case of Dynamics AX Axapta direct access to SQL Server is not recommended and rather you should consider X++ code and such development tool as MorphX. Microsoft actually is trying deemphasize Crystal Reports and rather advise SQL Server Reporting Services (SSRS)

5. Financial Reporting where Crystal is not recommended. General Ledger is tightly connected with Accounting theory and double entry. When you really need to generate 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 ever decide to recreate P&L directly in Crystal you should expect to reproduce most of FRx enterprise logic and this is in our opinion not a feasible idea

http://endeavor30.articlealley.com/crystal-reports-for-sql-based-erp-systems-sap-organization-one-particular-microsoft-dynamics-2430171.html

Crystal Reports for SQL Based ERP Systems SAP Business One, Microsoft Dynamics

Crystal Reports for SQL Based ERP Systems SAP Business One, Microsoft Dynamics

SQL Server opens new horizons in Crystal Reports design and deployment. Here you can separate the report presentation from data selection layer. Instead of linking tables directly in CR you can now create SQL View or Stored Procedure and test their results prior to opening Crystal Reports Designer. 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. Let’s begin with advantages of SQL View and Stored Procedure as report data pulling:

1.SQL View. Let’s 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 doesn’t 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 Studio

2.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 doesn’t have to apply restrictions to the total View selection and all the job are done in high performance SQL Server. Let’s now move on to SAP B1 tables and fields

3.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. Let’s 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 level

4.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 idea

6.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) help@efaru.com. 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

Andrew Karasev is Great Plains Certified Master, MVP, help@efaru.com 1-866-304-3265, 1-269-605-4904. He is also the initiator of eFaru project http://www.efaru.com and founder of Alba Spectrum information space

http://akarasev.articlealley.com/crystal-reports-for-sql-based-erp-systems-sap-business-one-microsoft-dynamics-2390906.html