Creation of a generic address report at embedded People Analytics

[FrankErle]

Summary

The format of addresses in EC is highly country specific. This provides a high level of flexibility when showing addresses in the employee profile. However, this can also lead to challenges in reporting. In particular it is difficult to show addresses of employees for different countries in a simple list report. The blogpost explains how such a simple address report could be realized but explains also the challenges and limitations which are related to that.

Introduction

The format of addresses is highly country specific. E.g. in many countries the first address line has the street with the house number while the 2nd address line contains information about ZIP code and city. Stair/Floor/Apartment and the 3rd address line has something like district. However, in other countries it is different. In fact, there are small deviations from country to country, while there are even legal regulations. For example:

For Germany                                 For USA                                         For Australia
ALEX MÜLLER                                BEN ZIMMERMANN                       CHANDLER J GILBERT
Himmelstraße 123                          PO BOX 752                                   2089 Baker St
98760 Dresden                               Philadelphia 10011                        Sydney NSW 2108
Sachsen                                          Pennsylvania                                  New South Wales
Germany                                         USA                                                Australia

In SFSF Employee Central exists a comprehensive configuration capability for this:

• For each country the respective address fields can be maintained. This happens under the Admin Centre activity “Manage Business Configuration”. Note that there are 25 address line fields available (address1, …. Address 25).
• Furthermore, it is possible to maintain a set of address type, e.g. ‘business’, ‘home’, ‘vacation’ and others (the ‘address type’ itself is configurable). This means, each employee could theoretically have address information in multiple countries; e.g. business address in Switzerland and home address in Germany.

Figure 1   Two examples of the configuration of country specific address objects (left: USA, right: Russia)

This highly flexible address configuration is a challenge in reporting. Even if one company is only located in a single country, it is at least possible that the addresses of the employees cover multiple country specific address formats.

Challenges in Address Reporting

1. One way to get rid of that is to join many country-specific address objects as indicated in Figure 2. However, imagine you join just 20 country specific address objects (out of existing 80 existing country specific address objects) with 5 fields each, then you can a list output of more than 100 fields which is of not easy to handle.

Note that the original EC database object “Address” which connects the “Biographical Information” and the country specific Address objects is so-to-say the parent address objects; which, however, does not have any of the fields address1, …. Address 25.

Figure 2   Schematic illustration of the parent-child-relationship of the generic address object and the respective country specific children

2. Alternatively, for small customers it might be an acceptable approach to have one address report for each country (under the prerequisite that the respective employees maintain addresses only in this single address format). Example: An US company has only legal entities in USA and the employees maintain only US specific addresses.

3. However, for multinational companies which would like to have address lists for larger parts of their whole workforce in a single list output, such a workaround is not feasible. Here, a more comprehensive approach is to “merge” all country specific address objects into a single object “Address”.

Employee	Address Type	Address Line 1	Address Line 2	Address Line 3	State	Country
ALEX MÜLLER	Business	Himmelstraße 123	98760 Dresden		Sachsen	Germany
CLAIRE TONKIN	Home	PO BOX 752	Philadelphia 10011		Pennsylvania	USA
CHANDLER J GILBERT	Business	2089 Baker St	Sydney NSW 2108		New South Wales	Australia
….

The big advantage of this approach is to avoid the usage of all the country specific address objects in the query. However, note that the downside of the approach is that it is not possible to know which information comes e.g., in “Address Line 1” for a particular employee, i.e., if it’s e.g. the street with the house number or the ZIP code in combination with the city.

In the following, the approach (3) is described more in detail; including the challenges which are associated with it.

Usage of object Address

In both, (1) Canvas Reporting and (2) Story Reporting, the fields address1 … address25 of object “address” are generated if the respective field is configured at least for a single country. For example,

• If address18 is configured for Russia (in fact, this is the case in the SAP’s default configuration, it’s the so-called Russian “Region Type”), then a field “address18” is also generated in object “Address Information”
• If address19 is configured for no country, then also no field “address19” is generated in “Address Information”

So far, so good. However, the challenge comes with fields of the country specific address objects which have a picklist. An important example is the field “State” which is typically configured with a picklist like e.g. USA, India, Australia and almost all European countries. However, there are also countries where no picklist for the state is defined, e.g. countries as Gabon and Congo.

Canvas Reporting

There are different (1) generic (= country independent) address objects and (2) country specific child address objects per address type (e.g. address type = ‘home’, ‘business’, ‘mailing’, etc.) as indicated in figure 3.

Figure 3   Report schema of an address report in canvas reporting

Note that the address objects are generated if there exists at least a single employee for the respective address type. For example,

• If there is just a single employee e.g. with address type “Benefits” for country “Spain”, then an address object “Addresses (Benefits)” with one child object “homeAddess (Spain)” is generated.
• If address type “Mailing” is maintained as value for picklist “address type”; however, there is no single employee with maintained data for address type “Mailing”, then also no address object “Addresses (Mailing)” is generated.

The field ‘state’ of the generic (= country independent) address object filled either with picklist label of the country specific ‘state’ (when a picklist is maintained for the field ‘state’ of the respective country specific address object) or with the string (when string is used for the field ‘state’ of the respective country specific address object).

For illustration see the example in Figure 4: While the field ‘state’ of country specific address object for Oman is configured as string, ‘state’ of the country specific address object for USA is configured as picklist. Nevertheless, the field ‘state’ from the generic address objects (“State (Mailing, label)” and “State (Benefits, label)”) also reports the expected label.

Figure 4   Sample output of an address report in canvas reporting

The prerequisite that this is working fine is that the field 'state' is configured as "Enabled = Yes" (see Figure 5). However, it doesn't matter if field 'state' is configured as data type = picklist or string; in both cases the reporting will work properly. 

Figure 5   Configuration of (generic) address object homeAddress

Story Reporting

The concept at story reporting is slightly different. A learning from canvas reporting was that the big amount of objects (i.e. a generic address object per address type and for each of them also country specific children) was causing confusion. Therefore it was a conscious decision in story reporting to  have only one (generic) address object (see Figure 6 for illustration).

Figure 6   Report schema of an address report in story reporting

Up to 2405 we were facing the problem that the field ‘state’ of the (generic) address object was only reported with the option ID of the picklist. From patch 13 (2405) this shortcoming was corrected, i.e. irrespective if a picklist is used as data type for the field ‘state’ of the country specific address or just a string, the field ‘state’ of (generic) address object is filled with the label.

See the example in Figure 7: While the field ‘state’ of country specific address object for Gabun (GAB) and Congo (COG) is configured as string (shown in red), ‘state’ of the country specific address object for USA and India (IND) is configured as picklist (shown in violet). However, after implementation of patch 13 (2405), the field ‘state’ from the generic address object (column “State (Home Address)”) also reports the expected label.

Figure 7   Sample output of an address report in story reporting

There are the following prerequisites that this works properly:

1. It is required that the field 'state' of the (generic) address object is enabled and configured as “picklist”. It is just required that a picklist is assigned, however, the respective picklist values are not used; i.e. the assigned picklist can exist without picklist values.
2. There is currently the limitation that the ‘state’ of the (generic) address object tables is not shown properly in tables of type “non-aggregated list”-table (and also not in the query preview). This means it’s mandatory to use tables of type “Cross—tab” when showing the address data in a table format.
3. When (1) and (2) are fulfilled, it is possible to create new stories with field 'state' of the (generic) address object. To enable the proper working of the field 'state' of the (generic) address object for existing stories, it is mandatory to trigger a change of the query (it is sufficient just to navigate to the query and to save the query when navigating back to the story). In addition, it is required (1) to convert the table into a list table and afterwards back into a cross-tab or (2) to convert the story to ‘Optimized Design Experience/Optimized View Mode’.

It is important to mention that the solution for the generic address object (= usage of country independent parent address object) described in this blogpost only works for "state" and not for address fields like "county", "province", "province-alt1" or other fields. As customer specific use cases it is even possible to assign a picklist to "city" or "zip-code" for particular countries. If such a customizing is done, only picklist IDs are provided for such fields in the generic address object. Then, it will be required to use the respective country specific address objects.

Furthermore, it is important to emphasize that existing canvas and story reports will continue to work as before when the patch is implemented. In particular, the existing canvas and story reports will continue to work also when the data type of the (generic) address object from string to picklist is changed.