One important (and cool) feature of SQL Cockpit is an ability to auto-generate ABAP reports directly from the SQL query entered in the cockpit.
We illustrate below, one example of how this may be utilised
As you can see from the code snippet and screenshot below, suppose we want to generate a report output that executes a relatively complex select query :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
SELECT but000~partner, but000~type, but000~bpkind, but000~bu_group, but000~bpext, but000~bu_sort1, but000~bu_sort2, but000~source, but000~xdele, but000~xblck, but020~xdfadr, but020~adext, but020~guid, but020~move_addr, but020~date_from, but020~address_guid, but020~addr_valid_from, but020~addr_valid_to, but020~addr_move_date, adrc~addrnumber, adrc~nation, adrc~title, adrc~name1, adrc~name2, adrc~name3, adrc~name4, adrc~name_text, adrc~name_co, adrc~city1, adrc~city2, adrc~city_code, adrc~cityp_code FROM but000 INNER JOIN but020 ON but000~partner = but020~partner LEFT OUTER JOIN adrc ON but020~addrnumber = adrc~addrnumber WHERE but000~partner IN &PARTNER& AND but000~type = '1' AND but000~bpkind = '' and but020~addr_valid_to = '99991231235959' AND but020~nation = '' AND adrc~title = '' AND adrc~name1 = '' AND adrc~city1 = 'Wien' . |
If you want to generate SAP report that executes this query, it would be a tedious and repetitive work to create a report manually in ABAP. Report generation feature of SQL Cockpit makes this work much simpler and quicker as illustrated below (We have implemented customer requirements with many more fields and complex select query that pulls data from upto 10 database tables!).
After executing the SQL query, just click on the highlighted „Template“ icon and „Select Template“ textbox appears
Choose an ‚advanced‘ option in the screenshot above.
We are taken to the Report Template Wizard
Click on the Continue button
Enter the report name, description, and other optional information. Note that „Tabular Selection Blocks“ checkbox to generate multiple tabs on the selection screen is selected. Continue to the next window
As shown below, we get a list of selection screen field options, (also auto-generated from the WHERE condition of the SQL query 🙂 )
As shown above, we have three options…
- create a selection screen field with an auto-populated value from the SQL where condition
- create a selection screen field without an auto-populated value from the SQL where condition
- Select query of a generated report will contain hard-coded value from the SQL where condition
Click on Continue button again after making your selections from one of these options
Finally, a generate report option is presented along with a list of specified report parameters in the previous step as seen below
Click on the Generate Report button
Select package as applicable and we are done.
Presto!
A generated report can be viewed / executed in SE38 like any other custom report.
Note the selection screen as seen below (with different selection screen tabs for fields from different tables)
Here is a screenshot from the select query (that was also auto-generated, of course 🙂 )
As highlighted in the screenshot, one of the where condition was created with a hard-coded values from the select query („Wien“) as per the selection in the report generation template!
Conclusion: we can auto-generate complex SAP reports using SQL Cockpit in less than 15 minutes! We can even customize special features based on individual client requirements.
This saves a lot of a lot of repetitive work for programmers and power users 😉
