ERP software implementation projects and software development projects share common attribute: costs scale with time and effort spent.Executive overviewERP software implementation projects and software development projects share common attribute: costs scale with time and effort spent. Project managers seek areas where cost can be cut to increase efficiency. The process of software development and custom developments at clients are key areas software developers and consultants spend a lot of time on working with the database underlying the SAP system. Creating custom business reports , optimizing queries for performance , importing data from external sources , testing new developments for correctness are daily tasks that add a lot to the overall costs of a project.The traditional time consuming solutions are writing a separate report in ABAP for each task every time , maybe using platform-dependent third party “black-box” solutions or accessing the database directly , bypassing authorization and exposing the database to the hazard of inconsistency.Providing an innovative solution to eliminate these difficulties reduces the time spent on all related activities , making the job of consultants and developers easier and reducing the total cost of the project.IntroductionThe OpenSQL Editor is a powerful tool that helps SAP consultants , ABAP developers and basis administrators to work with the database of an SAP system. It provides an intuitive way to build ad-hoc reports and statistics with simple OpenSQL commands. No further ABAP programming is required.The results of the queries can be easily visualized using charts and diagrams , and can be exported to MS Excel for further analysis. This export functionality provides a way to supply business users with live data from your SAP system for detailed analytics.Importing data from external sources like MS Access or MS Excel can be done with a few clicks.Testing and code maintenance efforts can be greatly reduced , development speed and code quality can be increased , since all OpenSQL commands can be tested , evaluated and analyzed with the OpenSQL Editor. Since this can be done during development (before the Module Integration Test or Acceptance Test) , code flaws can be discovered earlier , so correcting them sooner saves much time and cost.Product HighlightsAny OpenSQL command can be built , checked and executed easily. User-friendly features help to construct OpenSQL commands as comfortably as possible. There is no need to know every database table where data resides , since the application hierarchy can be browsed intuitively searching for tables , and the Linked Query Assistant automatically offers related tables to be included in the query.The Dynamic Value Ranges feature reduces the need of constructing the filter criteria with manual typing. The Field Selection Wizard eliminates the need of manually listing the fields to be used in a command.In case of database queries , the result is displayed immediately either in a list or in a hierarchical tree structure as desired. The result can be further filtered and sorted , totals and subtotals and other mathematical calculations can be added without any coding. When formatting the result is finished , it can be printed , exported to spreadsheets , or charts can be generated based on it.Data manipulation is easy as 1-2-3 with the use of the in-place editor or with the use of OpenSQL mass update commands. A well-thought locking concept is enforced to ensure the consistency of the database in case of concurrent data modifications. The program module that handles database modifications can be transported separately to the SAP systems , so it is possible to transport a “read-only” version to a productive system.The capabilities of the OpenSQL language can be extended with the Loop-at Workbench function. With the appropriate authorization , users can execute ABAP code on the result of any select command. Complex , custom logic of modifying the database records can be implemented without the hassle of data declaration , displaying the results on the screen , ensuring a proper locking concept etc.A universal and strikingly simple feature makes importing data from external sources into SAP tables possible. Any data placed on the clipboard from an appropriate data provider (MS Excel , MS Access , flat files etc…) can be imported , verified and saved to the database. The field mapping can be easily done using the Field Selection Wizard or simply moving the columns of the result set using drag and drop.Extracted data can be saved to a file or a table dynamically created for backup purposes , and be restored later at any time. This makes testing easier , since it eliminates the need of backing up a whole system or importing the data over and over again.Proper authorizations can be set up to ensure data security using the authorization objects shipped by SAP and the ones shipped with the OpenSQL Editor. All commands can be organized into folders and can be searched based on many search criteria.Features in detailExecution of OpenSQL commands – Any number of select , update and delete OpenSQL commands can be edited at once. In case of executing a select command , the result is displayed in an ALV Grid or in an ALV Tree as requested. The hierarchy levels and sort conditions of the hierarchical tree can be changed at any time without re-executing the select command. Accessing pooled and clustered tables is supported.Robustness – All syntax variations are accepted , without the use of local variables. Group functions , subqueries (nested to any level) , inner and outer joins , related select constructs (“Select for all entries in…” command) are all supported. Universal data import – Data can be imported to any table from practically any source (MS Excel , MS Access , flat files etc.) with the trivial use of the clipboard. Data can be reviewed and adjusted before saving to the database. The mapping of fields is trivial and easygoing using the Field Selection Wizard or simply moving the columns of the result set using drag and drop.Format the data – Common mathematical calculations can be added to the result set without coding , and without accessing the database. Totals , subtotals , averages , minimum and maximum values can be calculated for any numerical column of the result set. Sorting and filtering the result is also possible without typing and stressing the database.Use the data – The result of a select command can be printed , exported to a spreadsheet and many other formats , and can be used as the basis for creating charts. The result set of a select command is editable – If the select command fulfills certain conditions (selects from one table , includes all key fields , contains no group functions) , the result ALV Grid can be switched into edit mode , and all the records may be changed or deleted , new ones can be added.A locking mechanism ensures that database records cannot be modified if they are already locked. This locking concept is not only working in the OpenSQL Editor , but checks all locks that were placed on the database record by other programs. When the editing of the result set is finished , all the changes can be saved to the database with a push of a single button. An automatic input help/input check may be switched on and off at any time.Commands can be organized into folders within the My Repository – Command properties may be set for each command (short and long description , link to Business Object or Application Component , search terms..). Commands can be searched by any of these properties.Dynamic value ranges – An easy and quick way of filtering the affected records of a command , eliminates the need of manually typing in the filter conditions.Linked Query Assistant – A feature that helps to develop commands with inner and outer joins. The Linked Query Assistant reads the data dictionary to explore the relations between database tables. With a few clicks , a join or a subquery may be implemented automatically , without typing anything.Field Selection Wizard – An efficient feature that provides a way to select the fields used in a command without manual typing. It reads the table definition from the Data Dictionary and offers the fields with their descriptions in a list for selection.BenefitsSoftware development time reduced – Writing programs that access the database in any form becomes much easier since all commands accessing database can be developed , tested , and analyzed with many user-friendly features.Software testing time reduced – Test data can be created quickly in any table without using the relevant transactions which can be very time-consuming. Since any data can be modified , special test scenarios can be set up rapidly. Any problem in a productive system can be replicated in a test system without having to wait for a complete database copy.Quick ad-hoc reports , charts – Any requirement of an ad-hoc report can be fulfilled in much shorter time. Many development steps are done automatically , developers don’t have to hassle with programming the list or tree output , the selection screen , data declaration and creation of charts and diagrams.Boost business analytics – Most business users have the skill of using MS Excel for analytics , they just simply need the data from the SAP system extracted. This is just a matter of a few clicks with the OpenSQL Editor and your business users can instantly start analyzing up-to-date business data.Disposable reports eliminated – There is no need to create a new report in the Repository each time a report or chart is required.Maintenance efforts reduced – Defects can be found earlier with the positive effects on testing described above. Moreover , existing programs and data models can be comprehended quicker and easier with inspecting the OpenSQL commands and table relations in the OpenSQL Editor instead of the ABAP Debugger.Import of data to SAP systems simplified – For sources with a modest amount of records (typically stored in Excel sheets and Access databases) the OpenSQL Editor offers a strikingly simple and intuitive way to import that into an SAP system.Less manual typing – Constructing commands that access the database can be speeded up significantly with the use of several user-friendly features.Improved code quality – An OpenSQL command can be thoroughly tested with minimal effort opposed to the typical awkward way of executing the given report and using the ABAP Debugger to inspect the result of the relevant commands.Quicker TTM (time to market) – Develop your products faster than your rivals due to the labor and cost savings the OpenSQL Editor offers.Job satisfaction – Providing a tool that eliminates most of the monotonic and tiring work from your employee’s day to day activities will have a good effect on team morale and general job satisfaction.Requirements and installationThe OpenSQL Editor is entirely written in ABAP , so it is transparent (not a black-box development) and deeply integrated into the SAP system. No interfaces needed , no platform-dependency , no separate IT team to maintain. It does not expose the SAP system to any access from outside.Installation is a process of few minutes , since it only consists of importing one transport with the TMS (Transport Management System). The only additional effort is to set up the authorizations for the users. Due to the intuitive user interface and extensive documentation no consulting or implementation project is needed.Hovitaga OpenSQL Editor runs on SAP 4.6C but some features require SAP Netweaver7.00 (aka. 2004s) or above.