One of the most useful SAP Notes for BW

Great note for those tuning BW parameters

912367 - Composite Note: RSADMIN Parameter

Two very important SAP Notes for BW Authorizations

These two notes can solve a lot of problems, or at least give a good start ...

150315 - BW-Authorizations for Remote-User in BW and OLTP

161292 - Authorization templates for user roles and tasks 

BPC lite optimize zero elimination

Just a quick visual to add zero elimination to the light optimize package in BPC 7.5 NW, see SAP Note 1396761

Organize package list

Modify package Lite Optimize

Modify package Lite Optimize

Add task

BW dataflow examples for HR masterdata

It happens quite often, source data comes from a single infotype, but users want informations coming from different subtypes moved to different infoobjects on the same row (i.e. one row per employee per time period), this usually requires some ABAP but there is a trick.
You can use infosets to differentiate logically between rows of the infotype, see below:

This example is quite specific for time dependend masterdata attributes (think 0EMPLOYEE).
It's like having the ability to run different transformations from the same source to the same target according to different values of one column.

Something like inverted rule groups, which are great at turning data in a long row into data in different columns:

An example of rule group usage:

PERNR LGA01 BET01 LGA02 BET02 etc

turned into:

PERNR LGA01 BET01
PERNR LGA02 BET02

which can fit nicely into an infocube.

Texts in the output of an APD

There seems to be interest in outputting not only the query results in APD but also some texts, which is currently not supported by simply having the query output contain texts as anyone would expect.
But, there is an easy solution, see figure below

Here you are.
Sending it out to a file doesn't change the other steps.

Calculations with key date

A bit of follow up to the previous post ... we'd like to have the key date at hand for performing some calculations, unfortunately we didn't find an easy way so with some micro abap we managed to get that variable into another variable (??) that can be used.
Coding for variables (CMOD on BW side, EXIT_SAPLRRS0_001, include ZXRSRU01 etc.) follows:
Wish there was a simpler way ...

Code:

case i_vnam.
 when 'YV_DAY_2_VAR'.
    if i_step = 2.
      loop at i_t_var_range into loc_var_range where vnam = 'YV_DAY'.
 clear: l_s_range. 
       l_s_range-low = loc_var_range-low.
       l_s_range-sign = 'I'.
       l_s_range-opt = 'EQ'.
 append l_s_range to e_t_range.
      endloop.
    endif.

Prompts in SAP BO OLAP universe (query key date)

Having recently struggled with a key date prompt in a BO universe built on a BEx query ... I'm posting here some samples and screenshots of the outcome as I didn't find/couldn't understand documentation on this ....

The universe wizard in my case didn't generate something usable out of the box, so I had to tweak it a bit

First one, how to show it as a datepicker (sounds strange ...)

The corresponding code for the prompt is:

<filter key="[YV_DAY]"><condition operatorcondition="Equal"><constant tech_name=" @Prompt('Data Cardine (formato YYYYMMDD)','D',,mono,free)"></condition></filter>

Then if you want it to be a simple, no frills, single value prompt



<filter key="[YV_DAY]"><condition operatorcondition="Equal"><constant tech_name="@Prompt('Data Cardine (formato YYYYMMDD)','A',,mono,free)"></condition></filter>

And if you want the prompt to “forget” the previous value when the query is refreshed:

<filter key="[YV_DAY]"><condition operatorcondition="Equal"><constant tech_name="@Prompt('Data Cardine (formato YYYYMMDD)','A','Data\LovData CardineBase',mono,free, not_persistent)"></condition></filter>
Also see note 1407631 and keep up with the updates on BW and BO side, IMHO it’s not a mature solution yet.

Note 1142664 - MDX: Composite SAP note about performance improvements

Note 838800 - Composite SAP Note for consulting notes about MDX/OLAP BAPIs

Note 1156101 - MDX: Composite SAP note for incorrect data

Another useful one

Note 1567394 - MDX: Consulting note for sort sequence

Filtering on hierarchy values in DTP

Having spent some time unsuccessfully googleing for a way to use a hierarchy as filter in a DTP I've come up with a (rather inelegant) solution myself.
Posting it here in hope it's helpful.

Code:

DATA: l_idx like sy-tabix.

DATA: lw_hier_id TYPE rshi_s_rshiedirkey.
DATA: lt_nodes_a_leaves TYPE rshi_t_hienode.
DATA: lw_nodes_a_leaves LIKE LINE OF lt_nodes_a_leaves.

DATA: l_hier_name TYPE rshienm.
DATA: lw_hier_type TYPE rshiedir.

          read table l_t_range with key
               fieldname = '/BIC/ZFIELD_NAME'.
          l_idx = sy-tabix.
*....

l_hier_name = 'ZH_HIER_NAME'.

SELECT SINGLE * FROM rshiedir INTO lw_hier_type
                              WHERE hienm = l_hier_name
                                AND objvers = 'A'.

IF sy-subrc = 0.
  lw_hier_id-hieid = lw_hier_type-hieid.
  lw_hier_id-objvers = 'A'.


CALL FUNCTION 'RSSH_HIERARCHY_READ'
 EXPORTING
  i_rshiedirkey           = lw_hier_id
 IMPORTING
   e_t_rsnodes             = lt_nodes_a_leaves
 EXCEPTIONS
  invalid_hierarchy       = 1
  name_error              = 2
  iobj_not_found          = 3
  OTHERS                  = 4
          .
IF sy-subrc <> 0.

DELETE lt_nodes_a_leaves WHERE iobjnm = '0HIER_NODE'.

Loop at lt_nodes_a_leaves INTO lw_nodes_a_leaves.

       l_t_range-fieldname = '/BIC/ZFIELD_NAME'.
       l_t_range-sign = 'I'.
       l_t_range-option = 'EQ'.
* substring is due to compounding, generally unneeded
       l_t_range-low = lw_nodes_a_leaves-nodename+2(4).

       append l_t_range.

endloop.

ENDIF.

ENDIF.


*          if l_idx <> 0.
*            modify l_t_range index l_idx.
*          else.
*            append l_t_range.
*          endif.
          p_subrc = 0.

OK, this is all SAP BW jargon ;-)

SAP BW, IF for your BEx formulas

Have you ever felt the need for an IF in your BEx formulas? Didn't find it? That's because SAP expects you to know about boolean math ...
See, we are subtracting two quantities and we want our result to be shown as 0 if the actual result is negative, the trick is very simple, look at this example:

( ( 'GR quantity' - 'PO quantity' ) > 0 ) * ( 'GR quantity' - 'PO quantity' )

The first block will evaluate to 1 when GR quantity is greater than PO quantity and to 0 when it's smaller, so the result will be something like:

GR quantity: 6
PO quantity: 4

(( 6 - 1) > 0) * (6 - 4) will turn into 1 (true) * (6 - 4) = 2

The other case:

GR quantity: 4
PO quantity: 7

(( 4 - 7) > 0) * (4 - 7) will turn into 0 (false) * (4 - 7) = 0