ODATA Service

Cadaxo SQL Cockpit 3.8 – OData Service Generation into Worklist

In Cadaxo SQL Cockpit Version 3.8 (Releaseinfos), it is now possible to generate an SAP® Gateway OData Service out of an ABAP Open-SQL Select Statement.

Due to the newly added /CADAXO/ODATA Template, which is from now on available in Select Template wizard, the user can generate the OData Service based on the Select Statement, which the user previously displayed in the Cockpit.

In the next steps, the User is guided through OData Template Wizard. In here some important information about the Service must be added. Like Project Name (SEGW) and name of generated Entities. Filter, Order by, Skip, Top and Count features are optional.

After all steps of the OData Template Wizard are done successfully, the OData Service is activated automatically.

Given ‚Project Name‘ can be opened in SAP Gateway Service Builder (T-Code SEGW) and generated Entity Type and Set can be extended if needed.

Lets try the generated Service in practice!

Thanks to the SAP Fiori® Tools Visual Studio Code Extension I am able to quickly generate new SAP Fiori® elements Application – Worklist.

Without any lines of code, my Service has been used as a Data source and following App has been generated. Filter options are working out-of-the box as well.

SQL Cockpit Result Comparison with toggle feature

SQL Cockpit Results Comparison with toggle feature (Cockpit version 3.7 onwards!)

Cadaxo’s SQL Cockpit already had a feature to compare outputs of the queries. This can be accessed as shown below

Clicking on the highlighted button #2 above will direct us to the following „Data Compare – Mapping“ wizard

After selecting desired mapping, click on Start comparison button.

As shown in the following screenshot, we now have a new button (v3.7 onwards) on the menubar, „Toggle ALV Output“

 

Upon clicking this button, entries from the first and second tables are displayed on the separate lines:

In this „row-based view“, entries are printed on the separate rows.

Blank rows correspond to the missing entries in the outputs results.

Clicking on the toggle button again will restore column-based default view from the previous image

Cadaxo SQL Cockpit Report Generation

Cadaxo SQL Cockpit Report Generation

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 :

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…

  1. create a selection screen field with an auto-populated value from the SQL where condition
  2. create a selection screen field without an auto-populated value from the SQL where condition
  3. 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 😉

LIKE & ESCAPE Feature in SQL Cockpit

LIKE & ESCAPE Feature in SQL Cockpit

Should wildcard characters be part of the search term? What should I do?

The solution is very easy in our SQL Cockpit, LIKE statement must be told that for example ‘%‘ is understood as a simple character instead of wildcard character. This is done by placing a special character in front of it and specifying this character as an „ESCAPE character“ at the end of command.

Example

User wants to select the table line(s) which in DOKTEXT field are containing the 100% word.

The database table contains the following entries.

Using the LIKE addition with the pattern will have the following result, because the percent sign represents zero, one, or multiple characters in SQL.

The first and last percent signs indicate that before and after the string any content is possible. The middle percent sign is connected with the escape character ‚\‘ and is therefore part of the search string. This specification ‚\%‘ is to be understood as a character, because of ESCAPE ‘\’ addition.

Links

https://help.sap.com/doc/abapdocu_740_index_htm/7.40/en-US/abenwhere_logexp_like.htm

https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-US/abenwhere_logexp_like.htm

https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.00/en-US/20fa17f375191014a4d8d8cbfddfe340.html?q=LIKE

Domain Text Feature in SQL Cockpit

I don’t see the domain description in the result SQL statement screen? What should I do?
The solution is very easy in our SQL Cockpit, there is an user setting with which the user can see not only the fixed value, but the description of the value itself. There is no need to manual check the description, in case it exists it is displayed in the result screen.

Example
The “Business partner category“ domain contains the following values entries in the Value Range tab. In a simple standard SQL statement selection the description it is not displayed as a part of the selection query, so the user should manually check the value.

Executing a simple select statement with multiple parameters (Partner, Type) from “Business Partner: General Data I“ table.

Second column values comes from the first data element domain. The name of the new field is a generated value. It can be used also with the Column Header: “Fieldtext“ and “Show alias in header“ functionality from the user Personal Settings, which allows the user to customize the result list header name.

Personal Settings
On the SQL Cockpit main screen the Personal Settings button is highlighted.

If the user wants to use the Domain Text functionality must tick the “Add Domain Text” checkbox. It is saved permanently (after the system log off as well), in case of it is not needed anymore should be unchecked after the use.

Links
https://help.sap.com/doc/abapdocu_751_index_htm/7.51/en-us/abenddic_domains.htm
https://help.sap.com/doc/abapdocu_751_index_htm/7.51/en-us/abenddic_domains_sema.htm

Code Completion und Elementinfo im SQL Cockpit

Heute habe ich beim Testen ein für mich völlig unbekanntes Feature im SQL Cockpit entdeckt. Und das soll was heißen, bin ich doch einer der Entwickler hinter dem Tool und glaubte eigentlich alle Features zu kennen.

Jedenfalls gibt es im SQL Cockpit eine sogenannte Elementinfo. In dieser sieht man einfach die Struktur zu einer Datenbanktabelle oder einem CDS View. Die Elementinfo wird automatisch ermittelt, sobald man mit dem Cursor für knapp eine Sekunde auf einer Tabelle oder einem CDS View stehen bleibt. Man kann z.B. Felder per Drag & Drop aus der Elementinfo in den Query-Editor ziehen. Ein sehr, sehr hilfreiches Feature. Soweit so gut:

Dann hab ich heute beim Testen die implementierte Code Completion verwendet um nach speziellen Tabellen zu suchen. Und dabei ist mir nun aufgefallen, dass unsere Elementinfo auch ermittelt wird, wenn man im Code Completion Popup eine der vorgeschlagene Tabellen durchscrollt.

Zuerst war ich etwas überrascht aber nach näherer Betrachtung unserer Implementierung war es aber schnell klar. Ohne zu wissen, haben wir beim Einbau der Elementinfo auch auf Events der Code Completion reagiert. War so nicht direkt geplant, dafür um so erfreulicher.

Weitere Infos zur Elementinfo

  • Angezeigt werden folgende Spalteninformationen:
    • Keyflag
    • Feldname
    • Type/Länge
    • Datenelement
    • Kurzbeschreibung
  • Die Auslieferungsklasse von Datenbanktabellen wird angezeigt
  • Navigationsmöglichkeit zum Datenelement
  • Mehrfachauswahl von Spalten für gemeinsames Drag & Drop / Doppelklick möglich
  • Anzeige von Datenbanktabellen oder CDS Views