ABAP: select count on an internal table?

I recently came across interesting fragment of ABAP code. Actually it was causing a problem means ABAP dump.

DATA: it_tab    TYPE TABLE OF usr02,
      lt_tab2   TYPE TABLE OF usr02,
      lv_count  TYPE i,
      lv_tabnm  TYPE string.

SELECT *
  FROM usr02
  INTO  TABLE it_tab.

lv_tabnm = 'it_tab'.

IF sy-dbcnt NE 0.
  SELECT COUNT(*) FROM (lv_tabnm)
    INTO lv_count
    WHERE bname = 'DDIC'.
ENDIF.

Apparently developer never tested this piece of code. He or she just simply assumed that it is possible to supply internal table name into SELECT statement. In this case SELECT COUNT which was supposed to return no of lines in that internal table.

However SELECT COUNT on internal tables doesn't work. There are other possibilities how to count no of rows in internal table. You can only supply name of DDIC table into the text variable and SELECT COUNT will count no of rows in the database table.

If you do need to count no of rows in itab as whole w/o any WHERE condition there are following possibilities as far as I know:

1. Very old fashioned way via LOOP statement. It is not very efficient since iterating over every row of table:
   
   LOOP AT it_tab TRANSPORTING NO FIELDS WHERE bname = 'DDIC'.
     lv_count = lv_count + 1.
   ENDLOOP.

2. Via DESCCRIBE TABLE statement, just you cannot specify any conditions:

DESCRIBE TABLE it_tab LINES lv_count.

3. Via LINES function, again it is not possible to specify any conditions:

lv_count = LINES( it_tab ).

4. Usage of system variable SY-TFILL which with ABAP statements DESCRIBE TABLE, LOOP AT, and READ TABLE is filled with the row number of the internal table.

DESCRIBE TABLE result_package.

IF sy-tfill > 0.

5. One option to build efficient way how to count the rows with respect to some condition is following. By using DELETE statement we delete records that do not fulfill condition and then we use LINES function to count rows of table.

it_tab2 = it_tab.
DELETE it_tab2 WHERE NOT bname = 'DDIC'.
lv_count = LINES( it_tab2 ).

Conclusion is that you cannot use SELECT COUNT on internal tables. The statement like:

SELECT COUNT(*) FROM (lv_tabnm)

Would only work has if lv_tabnm is text variable and contains the name of a database (DDIC) table. By this way SELECT COUNT is executed on this database table.

- update 09/11/2013 –

Usage no.4 was added. I just want to stress out the reason why to know no of the internal tables is important. To determine number of rows of internal tables is very often used in ABAP programs. Due to the ABAP serves to process business data of high volumes there is always counting of rows in internal tables.

ABAP on HANA: How to check what tables are type of columnar?

This is a re-post of my SCN contribution related to ABAP on HANA topic.

ABAP on HANA: How to check what tables are type of columnar?

Useful class for coding of BW routines in Transformations

Recently I came across a coding in ABAP rule in transformation where simple method from class CL_RSAR_FUNCTION was used. It was method fiscper_calmonth. This simple method derives calendar month (0CALMONTH) as per given fiscal period (0FISCPER).

I had a look into other methods in same class and I found it very interesting. Method contains 55 methods in total in release BW 7.3 SP08. Some of them are very useful. Basic calendar and string function are there. For overview I’m providing full list of methods below. I’m sure I’ll use this class in my next BW developments.

ABORT_PACKAGE Function: Abort Package

ADD_TO_DATE Function: Add Days to Date

CALMONTH_FISCPER Function: Calendar Month ->Fiscal Period

CONDENSE Function: Condense

CONDENSE_NO_GAPS Function: Condense No Gaps

DATECONV Function: Date Conversion

DATE_DIFF Function: Date Difference

DATE_FISCPER Function: Date -> Fiscal Period

DATE_FISCPER3 Function: Date -> Fiscal Period 3

DATE_FISCYEAR Function: Date -> Fiscal Year

DATE_HALFYEAR Function: Date -> Half-Year

DATE_MONTH Function: Date -> Month

DATE_MONTH2 Function: Date -> Month 2

DATE_QUARTER Function: Date -> Quarter

DATE_QUARTER1 Function: Date -> Quarter1

DATE_WEEK Date Conversion in Calendar Week

DATE_WEEKDAY Function: Date -> Weekday (Textual)

DATE_WEEKDAY1 Function: Date -> Weekday (Technical)

DATE_YEAR Function: Date -> Calendar Year

FIRST_WORKINGDAY_MONTH Calculation of First Working Day in Month

FIRST_WORKINGDAY_YEAR Calculation of First Working Day in Month

FISCPER_CALMONTH Function: Fiscal Period -> Calendar Month

FISCPER_FISCYEAR Function: Fiscal Period -> Fiscal Year

IF Function: IF Statement

IS_INITIAL Function: Is Initial

LAST_WORKINGDAY_MONTH Calculation of Last Working Day in Month

LAST_WORKINGDAY_YEAR Calculation of Last Working Day in Year

LEFT Function: Left

L_TRIM Function: Left Trim

MAPPING Function: Field Mapping Transfer Structure

MONTH2_HALFYEAR Function: Month 2 -> Half-Year

MONTH2_QUARTER1 Function: Month 2-> Quarter1

MONTH_HALFYEAR Function: Month -> Half-Year

MONTH_QUARTER Function: Month -> Quarter

MONTH_QUARTER1 Function: Month -> Quarter1

MONTH_YEAR Function: Month -> Calendar Year

NEGATIVE Function: Reverse +/- Sign

QUARTER1_HALFYEAR Function: Quarter 1-> Half-Year

QUARTER_HALFYEAR Function: Quarter -> Half-Year

QUARTER_YEAR Function: Quarter -> Calendar Year

REPLACE_ALL Function: Replace All

REPLACE_FIRST Function: Replace First

RIGHT Function: Right

R_TRIM Function: Right Trim

SHIFT_LEFT Function: Shift Left

SHIFT_RIGHT Function: Shift Right

SKIP_RECORD Function: Skip Record

SKIP_RECORD_AS_ERROR Function: Skip Record (With Error Message in Monitor)

STR_LEN Function: strlen

UNIT_CONVERSION Function: Unit Conversion

WEEK_FIRST_DAY Calendar Week Conversion in First Day of Week Date

WORKINGDAY_MONTH Function: Date -> Working Day of Corresponding Month

WORKINGDAY_YEAR Function: Date -> Working Day of Corresponding Year

AMOUNT_DAYS_OF_MONTH Number of Days in Month

GREATER_OF_TWO_MONTH Which of 2 Months Has More Days Within Period

SAP Fiori

It is been approximately more than a month ago when on SAPphirenow (14-16 May 2013) also SAP Fiori was announced. SAP Fiori is a collection of apps with a simple and easy to use experience for broadly and frequently used SAP software functions that work seamlessly across different devices (desktop, tablet, or mobile). The Fiori is considered as “Consumer grade UI”. Meaning a new, modern and design thinking driven user interface (UI). Fiori currently has 25 apps which cover frequently used functionality with SAP software, e.g. create sales orders, entering time to timesheets, approve requests, etc.

Fiori targets to cover 80 percent of SAP users and 45 percent of SAP system functionalities usage. From technical perspective Fiori is based on SAP UI5 (also introduced quite recently). SAPUI5 is then based on HTML5 based standards. Fiori is optimized for the Google Chrome web browser. Fiori’s back end connectivity is provided by SAP’s oData (Open Data Protocol). Backend functionality is exposed by Gateway (NetWeaver Gateway (formerly called "Project Gateway)) services. Most likely Fiori apps can be ported to any version of ECC 6. Notice that Fiori doesn’t require Sybase Mobile platform called Sybase Unwired Platform (toolkit formally known as SUP).

The pricing of the Fiori? Seems only $150 per user for those above mentioned 25 apps.

To see demos of the Fiori check out: https://experience.sap.com/fiori

See documentation check out: http://help.sap.com/fiori

SCN resources of Fiori: http://scn.sap.com/docs/DOC-41598

PS: Curios about the name? Actually Fiori means "flowers" in Italian.

Design Thinking at SAP

These days the term of design thinking became very popular buzz word. All companies are pretending to be following the pattern of design thinking. So what is it in nut shell?

Methodology commonly referred to as design thinking is a proven and repeatable problem-solving protocol that any business or profession can employ to achieve extraordinary results (from Wikipedia). It is discipline that uses designer's sensibility and methods to match what user's need and what is feasible to achieve with technologies. Basically within the design thinking has three essences: Human, business and technology. 

You may wonder what is the topic of design thinking doing on my SAP related Blog? Actually design thinking is actually used at SAP to transition SAP as software company to cloud company. It means that classic SAP which is knows (or even is famous) because of on-premise ERP systems now wants to turn it-self to SaaS model delivery of software. 

Well in SAP case it may also be just a buzzword. However it seems they consider this initiative seriously. One of SAP’s founders Hasso Plattner is involved in so called Hasso Plattner Institute of Design which is design school based in Stanford University. The school was founded by David Kelley who is well known as buddy of Steve Jobs. David’s company Ideo was behind of scenes of many famous Apple products. 

In closing I would just point that transition from purely on-premise software company to the cloud company will not be easy. SAP really needs to change its mindset. In today’s world where almost no companies do mind to store their data into the cloud it will be extreme competition with pure cloud companies. Similarly we can see how other companies not only SAP are struggling with that transition. As an example I mention SAP’s archrival – Oracle. Last week they reported not very promising result in 2^nd consecutive quarter. Well maybe SAP is better positioned for that transition with their HANA offerings. But that’s something which only future will tell us.

SAP resources dedicated to design thinking:

SCN’s area

Design Thinking with SAP

User Experience@SAP

BEx Query Designer: Usage of NODIM function in formulas

Data function in BW BEx Query Designer NODIM is used to remove unit or currencies from key figures. It is useful while you need to calculate over key figures with different units or currencies.

Unless you employ using the NODIM you will get messages like MIXED VALUES or similar depending on customizing (SPRO - > BW - > Reporting-relevant Settings - > General Reporting Settings - > Display of Numerical Values in the Business Explorer).

How you use the function within BEx Query Designer’s formulas?

If you put it like this:

It is not correct. While you run report you get message:

Element  is not correctly defined check the query definition

Diagnosis

Additional elements are added to the item Variance that do not have the same dimension, for example, quantities and amounts or amounts and ratios.

Procedure

If you the addition is still meaningful, you can hide the dimensionality of one or both partial outputs with the operator 'NODIM'. The system then understands this partial output as a dimensionless number.

Correct way of using NODIM function is: