SAP,Business,One,Reports,Desig computer SAP Business One Reports Design in Crystal via SQL Views and

----------------------------------------------------------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

Considering the developing on the market related to Business Object acquisition by SAP it is natural to expect better integration between SAP and CR.  If you are Business One customer with active annual contract you do not have to pay extra for CR licenses as you can download your free version licenses to work with SB1.  Let’s talk about report design on introduction level:1. If I need views export then do I have to design report for that?  Probably not as most of the views and internal reports are exportable to Excel directly from the user interface.  If there is something simple but it is not present in the interface you can add internal query and it is also exportable to Excel2. Crystal Report Wizard or the first steps in design.  It is good idea to start your discovery with Wizard.  It is good tool if what you need to do is to pull information from just one or maybe two linked tables.  We do not recommend you to begin complex project in wizard especially where you are not really sure how many tables do you need to join.  It is difficult to make tables links changes down the road when the fields from these tables are already on the design area3. SQL Stored Procedures and Views or professional report design.  Here we are talking about such reports as Customer Statement, Bill of Lading, Sales Commission based on complex formula and Project Profitability to give you few examples.  The results set should be tested prior to opening Crystal and beginning the design.  Stored Procedure is the most powerful way to pull data from the tables as it has the ability to create temporary tables, build SQL cursors and do other procedural logic comparing to the SQL View where you are restricted to aggregate statements such as Select.  When you are done with SQL data pulling portion and tested its results you are sure that your design phase is abstract from data selection and you do not have to redo design down the road.  If in the future you decide to change something like commission formula then there is no need to change report itself as you can alter the formula directly in SQL View or Stored Procedure4. Tables and Fields Names.  The simplest way is probably via user interface where you click on View menu and mark show required fields.  Now open the form with the fields that you need to identify and place cursor over the field.  Field and table name will be displayed in the information pad in the bottom left corner of the application window.  Second option to review tables diagram is in Software Development Kit5. Crystal Report import to B1.  Version 8.81 doesn’t require special add-on to call report generation.  It is now done directly in Administration -> Setup -> General -> Report and Layout Manager where you can import report and it will be integrated and available in the reporting section of Business One6. Dependency to the current version versus the future upgrade.  We would like to say that CR is pretty stable and version independent.  It is much better to do report versus SDK programming in Visual Studio web project and show data in .Net Grid.  Software Development Kit project at least should be recompiled with the introduction of the new version7. CR versus Microsoft SQL Server Reporting Services.  If you are publishing SAP BO data to the web in something like ecommerce store then SSRS is a good alternative as it is web based by its architecture and is very friendly to C# or VB Visual Studio programmer.  Please, note that the design recommendations are similar here as well and it is recommended to start with SQL View or Stored Procedure8. CR and international alphabets including the ones based on hieroglyphs such as Chinese, Japanese or Korean.  We should mention that Business One supports Unicode characters and is localized in the majority of the world regions including China, Brazil, Europe and Russian Federation.  So it is not a problem to deploy Business One and Crystal combination in multinational business environment9. Is Crystal a great tool for such financial statements as Profit and Loss or Balance Sheet?  Well, theoretically it is possible however General Ledger is complex structure with such concepts as open and historical years.  This means that the logic would be probably too complex to replicate it in custom SQL query.  Feel free to use integrated financial statements directly in user interface or use industry consolidated reporting tools in the case when you are consolidating SAP B1 automated branch to your headquarter BS or P&L via something like FRx or Microsoft Management Reporter10. Second Opinion.  We’ve seen numerous situations when design budget was eaten up but the results were not satisfying including such annoying things as row duplications and incorrect summaries.  General recommendation to the way out is to redeploy report on SQL Stored Procedure or View instead of trying to link tables and program numerous formulas and sub-reports. Another observation is that such issues are typically related to learning curve of the designer and software developer.  We do provide second opinion and recovery services11. Business One Technical Consulting.  This is small business ERP application and possible consulting firm is CPA services with somebody who is trained and certified in B1.  Functional consultant might be categories as having general computer proficiency but this guy is unlikely a programmer.  Technical consulting including Crystal Reports design with SQL Server coding and SDK programming requires dedication to the technology and might be not compatible with such titles as CPA or CMA.  It might be recommended to seek for the second opinion from nationwide technology consulting organization which is carrying large pool of CR designers and SDK software developers12. Please call us 1-866-304-3265, 1-269-605-4904 (for international customers, where our representatives pick up the phone in Naperville and St. Joseph, MI call centers).  [email protected].  We have local presence in Atlanta, Chicago, Southern California, South West Michigan, Houston and Dallas areas of Texas. We serve customers USA, Canada, Mexico and Brazil nationwide and internationally via web sessions and phone conferences (Skype is welcomed). Our consultants speak English, Spanish, Portuguese, Russian and Chinese.  One of our experiences is international Corporate ERP and Consolidated Financial reporting