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 😉

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

Status & Farben in CDS basierten Fiori Elements Anwendungen

Status & Farben in CDS basierten Fiori® Elements Anwendungen

In Fiori Elements Anwendungen können Statusicons/Statusfarben durch Annotations gesetzt werden. Natürlich auch im CDS View. Dadurch können positive, negative oder neutrale Informationen besonders hervorgehoben werden. Im nachfolgenden Beispiel wird eine textuelle Statusinformation sowohl durch die entsprechende Farbe als auch das passende Symbol hervorgehoben.

 

Umsetzung mit CDS View Annotations

Die Statusinformation muss in einem eigenen CDS Feld vorhanden sein und kann folgende Werte enthalten:

  • 0 Grau
  • 1 Rot
  • 2 Gelb/Orange
  • 3 Grün

Beispielsweise könnte ein Feld StatusColor im CDS View (meist der Interface View) wie folgt gesetzt werden:

Hier wird einfach anhand des Statustextes im Feld _status.Description ein neues Feld StatusColor mit dem entsprechenden Wert gesetzt.

Im Consumption View bzw. In der Metadata Extension ist die Verwendung dann denkbar einfach und durch die UI.lineItem Annotation criticality definierbar:

Durch die zusätzliche Annotation criticalityRepresentation: #WITHOUT_ICON kann die Ausgabe des Icons unterdrückt werden:

Das Ergebnis sieht dann wie folgt aus:

Eine ähnliche Vorgehensweis ist bei vielen anderen Fiori Elements Annotations möglich und z.B. gut über die Codecompletion auffindbar. Beispielsweise bei den Facets oder Feldgruppen.

Ich habe die Farben z.B. in einer Anwendung zur Darstellung von Datenbank Differenzen eingesetzt. Dadurch konnten kritische Abweichungen besonders gekennzeichnet werden.