1 2 3 7 Previous Next

SAP Business Explorer (SAP BEx)

96 Posts
Tobias Meyer

BEx vs. Excel Average

Posted by Tobias Meyer May 31, 2016

Hi

 

here is a problem, which I had recently at a client and I want to share this with you. I also posted this post on my blog (www.reyemsaibot.com).

 

When I was recently creating a new query at a customer, I have found a strange phenomenon. The query should show the average ticket number of a department. First you see the explanation and then I provide the solution of the problem. The query should show the individual, cumulative and average monthly values, as you can see in the screenshot.

 

bex analyzer wrong avg.png

 

As you can see, there are no values for the months of May, June, August, November and December, so the cumulative values remain the same as the previous month. Unfortunately the calculation of the average has a problem, when a column has no value (for example May). In comparison the average value with the Excel formula AVERAGE.

 

BEx Query Average vs. Excel Average.png

 

As you can see in the screenshot above, the key figures differ between the Excel formula and the result from the Bex Analyzer. According to the Excel formula the month of May has an average of 43, according to the Bex Analyzer it has an average of 16. This is a large deviation. As you can see this error runs through all months after one column has no value.

The interesting thing about the query is, if you have a department that has values from January to December, the average of the Bex Analyzer works without problems.

 

bex vs excel average over a whole year.png

 

The line Avg. no. tickets is identical to the line Excel formula AVG. That means the query definition is correct and working. But if there are months where no values have been posted, the average calculation of the Bex Analyzer failed.

 

The following settings have been made to the key figures:

 

• No of tickets cumulated: the key figure is only cumulative, otherwise no further calculations.

 

selection cumulated.png

 

• Avg. no tickets: Aggregation on average with reference characteristic to Calendar Year/Month (0CALMONTH) and calculating the individual values and results as average or moving average.

 

formula aggregation.png

bex formula calculations.png

 

The question arises to me now is, why it works correctly when the entire year have posted values, but not when there are months without value. The cumulative value is also correctly calculated and doesn't show an error. Only the average value is incorrect. Unfortunately, that was a problem for my client, since the report was intended of the controlling department and this department, you surely know, love Excel ;)

 

So why does the Bex Analyzer interpret a blank line at cumulative values as 0, but failed if you want to calculate an average.

 

So that the controlling department get the same result in the Bex Analyzer like with the Excel formula, we needed a solution. After a little research I found this SCN article, which says insert a new formula and add to this formula a 0. So nothing complicated, just a simple solution.

 

formula general.png

 

As you can see on the screenshot below, the row Avg no of tickets + 0 has now another value than the row avg no of tickets. The cumulative value is identical.

 

BEx AVG with 0 fixed.png

 

Now the comparison with the Excel formula AVERAGE:

 

bex analyzer with fixed 0 and excel avg.png

 

And as you can see now, the row avg. no of tickets and the row Excel formula AVG are identical. So I only have to hide the unnecessary rows and the query can be used. I hope this may help someone when you walk into the same issue.

I can see many requesting how to check the queries inside a workbook during the last year. The steps are quite simple, and I will sumarize these steps here.

 

 

If you want to check which workbooks have an specific query as dataprovider, you need to know the query technical name.
As example, I will use my query with technical name ZEDD_TEST3.

 

 

Step 1 - Access table (SE16) -  RSRPREPDIR 

1.png

OBJVERS = A

COMPID =  ZEDD_TEST3

 

Execute(F8)

 

2.png

 

Step 2 - Access table (SE16) RSRWORKBOOK

 

Basically, RSRWORKBOOK.GENUNIID = RSRPREPDIR.GENUNIID

 

 

 

So, for my case:

 

3.png

 

GENUNIID = 4UBUGC2QE23K7REMTHZQ5W3E9

OBJVERS  = A

 

 

Execute(F8)

 

WB.png

 

We can see that I have only one workbook with my query.


Workbook ID is 3EGGEJQPR1MX5CKP6AFH0Q8DB

 

 

If you need to know the workbook title, access table RSRWBINDEXT and use the workbook id on the proper field.

 

4.png

 

 

All steps mentioned are also valid if you have to check which queries an specific workbook is used.

For this case, you have to do the reverse path:

 

  1. Table RSRWORKBOOK - Search for your workbook, on version A.
  2. Copy all GENUNIID
  3. Go to table RSRPREPDIR and paste the GENUNIID there.

 

 


Future works: It is possible to consider a creation of a REPORT to check automatically.

We all know this FM FIRST_AND_LAST_DAY_IN_YEAR_GET to get the first and last day of a fiscal year, however if you want to know non-fiscal year’s (normal calendar year) first and last day of a logistic/factory calendar, there is no direct FM which gives the result.

 

Knowing that the calendar year starts from January and end on December, generally, when doing Yearly/YTD calculations, we define first day as: 0101 and last day as: 3112 and then concatenate with respective years accordingly in the user exit.

 

But, not all factory calendar years starts on 1st of January and ends on 31st December ?

 

If we take the SAP standard calendar (Germany – 01), its every Week starts from Monday and ends on Sunday (including weekend’s), so the Start and End date of every year changes accordingly.

 

This requirement rises particularly when your client/business is following with specific dates each year.

 

Path


Goto t.code : CMOD --> project--> EXIT_SAPLRRS0_001 --> INCLUDE ZXRSRU01


Here is the code


DATA: lv_from TYPE datum,
      lv_from1
TYPE datum,
      lv_to  
TYPE datum,

            lv_firstday TYPE  scdatum ,
      lv_lastday 
TYPE scdatum,

            lv_firstday1 TYPE  scdatum ,
      lv_lastday1 
TYPE scdatum,
      lv_year    
TYPE I,
      Cweek_last 
LIKE  SCAL-WEEK,
      Pweek_last 
LIKE  SCAL-WEEK,

      lr_range TYPE rsr_s_rangesid.


IF i_step = 1 AND
( i_vnam EQ 'ZCYEAR' OR   "Current Year
i_vnam
EQ 'ZPYEAR' ).   "Previous year

lr_range-sign = 'I'.
lr_range
-opt  = 'BT'.


*** First date of current year ***

CASE i_vnam.
WHEN 'ZCYEAR'.
lv_year 
= sy-datum(4) - 1 . " Last Year


"Since, there is no FM available to get first week of year, I choose last week of last year to get First day of current year

CALL FUNCTION 'TIME_GET_LAST_WEEK'
EXPORTING
IF_YEAR          
= lv_year
IMPORTING
EF_WEEK          
= Cweek_last. " Last week of Last year

CALL FUNCTION 'WEEK_GET_FIRST_DAY' "
EXPORTING
WEEK              
= Cweek_last
IMPORTING
DATE               = lv_firstday1. " First day of last week(Last Year)

lv_from
= lv_firstday1 + 7. " First date of current year

*** LAST date of current year***


lv_year
= sy-datum(4) . "Current year
CALL FUNCTION 'TIME_GET_LAST_WEEK'
EXPORTING
IF_YEAR          
= lv_year
IMPORTING
EF_WEEK          
= Cweek_last. " Last week of current year

CALL FUNCTION 'WEEK_GET_FIRST_DAY'
EXPORTING
WEEK              
= Cweek_last
IMPORTING
DATE    = lv_firstday. "First day of last week (current year)


lv_to = lv_firstday + 6. "Last date of current year


*** First date of previous year ***

WHEN 'ZPYEAR'.


lv_year 
= sy-datum(4) - 2 . "Before last year

CALL FUNCTION 'TIME_GET_LAST_WEEK'
EXPORTING
IF_YEAR          
= lv_year
IMPORTING
EF_WEEK          
= Pweek_last. "Before last year last week (BLY)

CALL FUNCTION 'WEEK_GET_FIRST_DAY'
EXPORTING
WEEK              
= Pweek_last
IMPORTING
DATE               = lv_firstday. "First day of last week (BLY)

lv_from
= lv_firstday + 7. "First date of last year


**** LAST day of previous year***


lv_to
= lv_firstday1 + 6. "Last date of last year

 

WHEN OTHERS.
ENDCASE.

lr_range-low  = lv_from.
lr_range
-high = lv_to.


APPEND lr_range TO e_t_range.


ENDIF.


Note: you can use the same code with slight modification for other weeks as well(ex: Sunday - Saturday ..etc)

Query Results

 

Bex query layout.JPG

Variable

 

calyear variable.JPG

Selection


current year selection screen.JPG


you can repeat both current year variable and selection definition for previous year as shown in the above images.


RSRT result


RSRT output.JPG

Current Year


First date 04.01.2016 - Monday

Last date 01.01.2017 - Sunday


Previous Year


First date 29.12.2014 - Monday

Last date 03.01.2016 - Sunday



if you want to calculate Year-To-Date, then replace last date in the code with current date(current year) / last year same date (for previous year).


(or) you can change it as per your requirement (ex: to current week)



PS Note: I am sure there might be various methods to achieve this, please do share your inputs too.

 

I figured out this way and thought to share, hope it helps. Thank you

Usage of BExAnalyzer 3.5 Macros and VBA Function:

 

In 3.5 BExAnalyzer you can use SAPBExSetVariable to set the variable to a query that you would like to see the result once the query is refresh in 3.5 BExAnalyzer

 

Following macro are typically used to refresh a single Data Provider with in a workbook.

 

  • Run("SAPBEX.xla!SAPBExSetVariables", lRange)

 

lRange contains the Range address of the Variable Values (Structure RRX_VAR: VNAM, VARTYP, VPARSEL, SIGN, OPT, LOW, LOW_EXT, HIGH, etc...)

 

  • Run("SAPBEX.xla!SAPBEXSetVariable", False, lRange)

 

The second parameter define if all Data Provider are to refereshed or if only the Data Provider located within the provider Range lRange.

 

This is the flow of BExAnalyzer 3.5 VBA Function SPABExSetVariable. However, the support of 3.5 BExAnalyzer is over and you may would like to use the function in 7X BExAnalyzer.

 


Usage of VBA Function SAPBExSetVariable in BExanalyzer 7X

 

When the workbook is upgraded to BExAnalyzer 7X the macro are automatically adapted to the new VBA Code of 7X BExanalyzer and it will look like

 

  • Run("BEXAnalyzer.xla!SAPBExSetVariable", lRange)
  • Run("BEXAnalyzer.xla!SAPBExSetVariable", False, lRange)

 

And you have also implemented the SAP Note "1849135 : Single Data Provider Refresh Functionality NEW"

 


Issues in current flow:

However, you may find that this SAPBExSetVariable is not working as required in BExAnalyzer 7X especially in case of

  • Executing this macro more than once with different values for variable.
  • Current flow also modifies the variable value for query for which you did not request for SAPBExSetVariable.

 

There are some more issue with the flow of SAPBExSetVariable and refresh.

 

Improvement / Enhancement in BExAnalyzer 7X for SAPBExSetVariable


SAPBExSetVariable is typically 3.5 BExAnalyzer supported function and this feature can work only if you would have already applied a note “1849135 - BExAnalyzer: Single Data Provider Refresh Functionality NEW”.

This is because 3.5 BExAnalyzer refresh single query at a time, however the 7.x architecture is very different then 3.5 Refresh. Therefore this feature SAPBExSetvariable can only work if the single data provider new and the note 1849135 is implement correctly.


In this note 184915, there is an information mentioned that the “The Flag "Allow Refresh Function for individual Queries" in the Workbook Settings Dialog in the Tab "General" does not need to be set for the macros to work.”


There were some inconsistency in flow of processing the single data provider refresh new and SAPBExSetVariable and so in new supported SAPBExSetVariable this Flag is required and mandatory to be set to make SAPBExSetVariable to work along with the new correction mentioned in the SAP Note 2265155 - VBA Function SAPBExSetVariable not working as required in BExAnalyzer 7X.



Usage of SAPBExSetVariable in BExAnalyzer 7X:


To use SAPBExSetvariable in 7.x you have make following changes in your excel range, as of now the you were only mentioning about the variable and its information as per the range address of the Variable Values (Structure RRX_VAR: VNAM, VARTYP, VPARSEL, SIGN, OPT, LOW, LOW_EXT, HIGH etc.).


The new changes here is you also have to maintain Data provider name as shown below in your excel range.


 

DATA_PROVIDER<DATA PROVIDER NAME><DATA PROVIDER NAME>
VARIABLE NAME11IIBT<VALUE><VALUE>
VARIABLE NAME21IIBT<VALUE><VALUE>
VARIABLE NAME31PIEQ<VALUE><VALUE>


So as earlier you were only mentioning about the variable information in your excel sheet cell but now with new changes you also have mentioned the data provider name. As recomendation it will be more earier if you can mentione the dataprovide name first.


Now you also need to do some modification in your VBA Code


BExAnalyzer 7X VBA Macro code example:


strVBA = Run("BExAnalyzer.xla!SapBEXsetVariables", Sheets("<Sheet Name>").Range("<Range Address>"))

 

'<Sheet Name> where the data provider exit to be refreshed.

 

Set shtRefresh = ThisWorkbook.Worksheets("<Sheet Name>")

 

strVBA = Run("BExAnalyzer.xla!SAPBEXrefresh", False, Sheets(shtRefresh.Name).Range(<Range Address>))




Prerequisite to enable SAPBExSetVariable flow in BExAnalyzer 7x


  • Please implement the notes 1832908, 1849135
  • Note 2265155 - VBA Function SAPBExSetVariable not working as required in BExAnalyzer 7X
  • Transaction RS_FRONTEND_INIT, Set parameter ANA_SINGLEDPREFR_NEW = 'X'
  • Either set “The Flag "Allow Refresh Function for individual Queries" in the Workbook Settings Dialog or Global Setting in tab “General”. 

The purpose of this blog is to explain some of the functional/installation changes you will see when you install precalculation patch 400 for SAP GUI 740 onwards.

From 740 precalculation patch 400  only one precalculation service file is delivered i.e SAP BW Precalculation Service Multi Instance.

 

Here I am explaining some new changes which you may observe after installation of 740 precalculation patch 400.

One of the Precalculation service file is removed from 740 Precalculation patch 400. i.e SAP BW Precalculation Service

Due to this change you may face some installation issues and functional issues related to removed service 'SAP BW Precalculation Service '.

 

If you face any such issues please follow the below information/steps

 

  1. There is no concept of upgrade of precalculation from older version to newer version.

          Precalculation is only for direct installation.

          Please check the Precalcserver installation document which is delivered along with Precalculation server in SAP     GUI  DVD.


     2. In case you have upgraded precalculation server from older patch to latest patch
           Ex: 740 precalculation patch 300 to 740 precalculation patch 400            

 

Issue:

  • You can still see two Precalculation service files. i.e SAP BW Precalculation Service & SAP BW Precalculation Service Multi Instance when you Goto Run -> Services.msc

as shown in below figure:

 

But as already mentioned above that from precalculation patch 400 only SAP BW Precalculation Service Multi Instance service file is delivered not the SAP BW Precalculation Service.

So the SAP BW Precalculation Service which you see in the list here is of older patch which won’t work.

 

  • Now when you try to uninstall precalculation server to remove the ‘SAP BW Precalculation Service’ using precalculation server patch400 or from control panel, Though uninstallation get successfully completed ‘SAP BW Precalculation Service’ still persists in the services list.

 

 

Solution:

 

  1. Run Precalculation patch300 instead of patch400 to do the clean installation. It will remove SAP BW Precalculation Service.
  2. Install Precalculation patch 400.

      Now you would see only one precalculation service i.e SAP BW Precalculation Service Multi Instance.

 

 

Note: Precalculation server must be in same patch level as Business Explorer.

 

 

For more information Refer 740 FEP 400 Precalculation note: 2100845

http://wiki.scn.sap.com/wiki/display/BI/Checklist+for+Precalculation+server?original_fqdn=wiki.sdn.sap.com


Best Regards,

Shiva

Earlier F4 (Filter Screen / Selector Dialog ) in BEx Analyzer Planning enabled query we were able to select only M or D as read modes and were not able to select read mode as P(planning ) .

With the new enhancement it is now possible to set the read mode as P for planning enabled query.


Following are the steps which needs to be followed to achieve the same.


1.  Read mode P can be activated by setting  parameter ANA_READMODE_P_REQUI = 'X' in transaction RS_FRONTEND_INIT

2. Create a real time info cube , in my case following is the structure of infoprovider

1.png

3. Now define Characteristic relationship between Currency and Country.

Navigate to transaction RSPLAN, click on infoprovider and select the real time cube and click on edit,

Go to the tab Characteristic Rels

and Click on create option , select the Char. Relationship type as Attribute from dropdown and select Characteristic master data builds basis  as Country and then check the option Derivation

Check Currency as Target Characteristic

Click on Save.


(More info on Characteristic relationships:

Characteristic Relationships It is used to test valid combination of records generated and derive the char values for other chars. This is at infoprovider level; it is applicable to all the aggregation levels of real time cube. Invalid combination: this is applicable in manual planning, when we are trying to generate a new combination which is not part of Char relation, it is assumed as invalid combination. Derive Characteristic: we can derive values for characteristics in real time cube, we define source and target characteristics in planning modeler.)

 

2.png

4. Then create the aggregation level.

5. Create an input enabled query on the aggregation level created.

6. Select the key figure which needs to be input enabled

3.png

4.png

7.Select the characteristic COUNTRY , in the extended tab , under  Filter Value Selection at   Query Execution select Characteristic Relationships

5.png

 

Do the same for characteristic CURRENCY as well.


8.Launch BEx Analyzer and open the above created query.


   Following is the data in the query

7.JPG


9.Now if we try to add new line say  GERMANY    EUR AUDI   2000

Invoke the selector dialog on Currency column, Click on settings


8.png


9.png


The Read mode – Planning mode should now be available.

Click on OK

 

10.Now invoke F4 on Currency field, select Single values , only EUR will be available for selection .



Hi BEx community,

 

Not rare a BEx Query goes into debug mode when you test it in transaction RSRT due to an exception raised during the query execution.

Here I will show you an easy step to increase drastically the chance to find the SAP note with the code correction you need to solve that issue, just by using the correct key words for a search.

 

When debugger shows up, it stops at the BREAK-POINT  below:

 

 

kba_fig_1.jpg

 

As the image suggests, it gives you the chance to find out the report id, include and code line where the exception is raised. Just double-click on each of the parameters commented and write-down this information.


In this example, the report id is a class and the include is the method of that class.

 

Class: CL_RSR_OLAP_VAR

Method: CONSTRUCTOR

Code line: 37

 

Now, just go to Object Navigator (SE80) transaction, open the respective class and method and locate the code line. In case the exception is raised on a report/form or function, search for that on the corresponding type (you can also go directly to SE38 for reports, SE37 for FMs or SE24 for class/methods) and open the source code.

 

kba_fig_02.jpg

 

Take a quick look at the source code to see if you can get additional information from it. In this case, we can easily see that something went wrong when FM RRI_REPORT_IMPORT_AND_CHECK was in process.

 

Now we know some very important technical key words to use on a search on Market Place.

 

I would try some combinations of key words on note search, like these ones for instance:

 

Search:  “CL_RSR_OLAP_VAR” “CONSTRUCTOR”

Search:  “CL_RSR_OLAP_VAR” “RRI_REPORT_IMPORT_AND_CHECK”

Search:  “CL_RSR_OLAP_VAR” OR “RRI_REPORT_IMPORT_AND_CHECK”

Search:  “RRI_REPORT_IMPORT_AND_CHECK”

 

This is a very basic technic to get the right technical terms for a good search but it will increase a lot the chance to find the correct note for the issue.

If you are keen in debugging, you can also set a breakpoint at the code line and debug the issue further by restarting query execution. You can get closer from the reason the exception happened and narrow down the issue even more.

 

Some other insights you can have is to check the package of the report id and find out which application component it belongs to. This can help either in the search to restrict notes for component or to open an incident to the right expertise area, in case you were not able to find the solution following these steps

 

Below is a screen-print showing how to find the application component.

 

kba_fig_3.jpg

 

Well, that’s it for today. I hope it helps.

 

Cheers,
Eduardo Provenzano

 

See other posts:

Terminations on BEx Queries due to missing/inconsistent logical indexes on SAP HANA

How to verify a Workbook last used date

Unexpected values in Bex Query? Check out SAP collective notes for incorrect/wrong data in BW queries

Hi BEx Community,

 

After you import a Support Package for your SAP NetWeaver BW you notice unexpected values/numbers when consuming a BEx Query (or even a BW Infoprovider directly) or reading characteristic values (F4 value help).  The unexpected data is propagated to all front-ends that consumes BEx Queries, such as Web Intelligence (WebI) and other BO tools, Bex Analyzer, Analysis for Office (AO), Business Planning and Consolidation (BPC), Java Portal and many others.

 

There are two possible reasons for that. Let’s take a look below:


  1. The new code contains an error that generates an incorrect value/number.
  2. The query uses one or more special functions or properties, each of which works properly on its own. However, if the functions or properties are used in combination with each other, they produce results that cannot be understood or that are difficult to understand.

 

In the first scenario this unexpected data is really a bug in the system so we can call that wrong/incorrect data; whereas the second scenario is just a misunderstanding of query results due to the query complexity. To distinguish between the two situations, SAP has some consulting notes that provide guidance on how to check that. See the notes below:

 

1151957 - Interesting facts about the OLAP Processor/Analytic Engine

1591837 - How to analyze query results

 

So let’s assume you have already gone through an in-depth analysis and now you pretty sure the issue is really about wrong/incorrect data.


The OLAP processor delivers complex optimizations that safeguard system performance even when complex InfoProviders and queries are processed. In rare cases, these optimizations can cause undesirable side effects or, in the worst case scenario, incorrect data. Generally, errors of this type are very difficult to identify and might not even be noticed by the end user.


Therefore, SAP recommends that you implement the corrections for all known problems associated with a specific Support Package level and for that, development support maintains collective notes about incorrect/wrong data for the most recent BW releases and SP levels.

 

These SAP Note groups together corrections for certain Support Package and higher levels. The corrections are listed in the related SAP Notes section, see screen print below.

 

14-10-2015 11-13-11.jpg

This is the beginning of the list of notes that address incorrect/wrong data available for SAP BW 7.40 SP 11 and higher.


 

It’s recommended to implement all SAP Notes in ascending order based on the SAP Note number.

 

And here is the list of collective notes according to BW releases and SP levels:

 

2223788 - Support Package 11 for SAP NetWeaver BW 7.40 -> incorrect data

2168124 - Support Package  9 for SAP NetWeaver BW 7.40 -> incorrect data

2010273 - Support Package  7 for SAP NetWeaver BW 7.40 delivers incorrect data

2223810 - Support Package 15 for SAP NetWeaver BW 7.31 -> incorrect data

2223790 - Support Package 13 for SAP NetWeaver BW 7.31 -> incorrect data

2010272 - Support Package 11 for SAP NetWeaver BW 7.31 delivers incorrect data

2223804 - Support Package 13 for SAP NetWeaver BW 7.30 -> incorrect data

2010270 - Support Package 11 for SAP NetWeaver BW 7.30 delivers incorrect data

1820182 - Support Package  9 for SAP NetWeaver BW 7.30 > incorrect data

 

 

In case that does not help and you need to open an Incident with SAP support , it helps a lot to simplify analysis, and thus speed-up overall incident processing, if you follow the tips described on the following notes:


1125883 - Simplified example query for analyzing problems

1904869 - Necessary Information for BW-BEX-OT-OLAP* Messages

 

Hope this helps.

 

Cheers,
Eduardo Provenzano

 

See other posts:

Terminations on BEx Queries due to missing/inconsistent logical indexes on SAP HANA

How to verify a Workbook last used date

Bex Query going into debug mode in RSRT? Check here how to find the solution

YTD Time Structure – Using SAP Standard Variable

 

 

A key figure nee to display under A structural Frame that include ,

 

YTD

LY YTD

LY Period

Dynamic Display of 12 Rolling Period

 

YTD.png

User Prompt: 0P_FPER (Fiscal Year Period Variable) – Depend on “Fiscal Year Variant”; we need to fixed the value of “Fiscal Year Variant” to a single variant.

  • YTD: 0I_BA104 (Based 0P_FPER variable to derive YTD Cumulative Value)
  • Last Year YTD: 0I_BA106  (Based 0P_FPER variable to derive YTD Cumulative for Year-1 Value)
  • Rolling Periods : Offset on 0P_FPER ( -1 to -11)
  • 0T_FPER: The Text variable of 0P_FPER to display dynamically, the period as label.

 

 

User Entered : 009.2015

YTD = Sum (001.2015 till 009.2015)

LY YTD =

Sum (001.2014 till 009.2014)

LY Period


For 009.2014

Dynamic Display of 12 Rolling Period


008.2015 / 007.2015 / 006.2015 / 005.2015 / 004.2015 / 003.2015 / 002.2015 / 001.2015 / 012.2014 / 011.2014 / 010.2014 / 009.2014

 

 

Assumption : Special Periods Excluded

Recently I met a situation that the customer can not delete the relevant entries from table RSDDCHNGRCHECK using the Clean-up report SAP_RSDDCHNGRCHECK_CLEANUP with option DELCNSID. Although the entries with some specific Changerun IDs can be identified in table RSDDCHNGRCHECK, the clean up did not work.

The report is provided by note 1495735 to clean up table RSDDCHNGRCHECK which was used during checking of changed records between DB and BWA after changerun.

When you run the report and select the option DELCNSID, manually entering the changerun ID in CNSID field will not work. You have to choose the CNSID from F4 list and then execute the report.

 

See the following screenshot.

 

SAP_RSDDCHNGRCHECK_CLEANUP.png

F4.png

 

F4 select.png

 

execute.png

 

Currently, per the coding of the program, the manual input CNSID will not get the needed internal parameter to have this report run through.

 

 

Best regards,
Cecilia

Recently I saw some customers raised incidents about the currency display in the query output. It's not very often to request the customizing currency display, but BW indeed provides the functionality to set the alternative currency display.

 

Requirement:
Scenario 1: you want the currency key displayed as what you want. For example, you hope to display currency key CNY as RMB.
Scenario 2: you are hoping to have the currency key be hidden in query output. 

Scenario 3: you want the currency key displayed before the key figure values, while the currency is shown after the value in normal way.

 

Solution:

The above requirement can be achieved by the "Set Alternative Currency Display" setting in transaction code SPRO.

 

Set Alternative Currency Display.png

 

The following three new entries will satisfy above requirement.

Set Alternative Currency Display 2.png

 

Actually this feature has been introduced in the following online help document.

 

SAP Library - XML: BW - OLAP

Thanks
Cecilia Li

Objective:

 

Every organization use multiple reports like Sales, Shipping, Billing and Profit & Loss to make good business decision. In this all areas we might have developed and used huge BEx reports, in all reports we might restrict (include/exclude) characteristics(like 0MATERIAL, 0PLANT) values 1001, 2002 for company codes, S01, S02 for Sales organization , Doc Type , Plant to meet business report requirement.

 

Every organization will expand their business, as part of business growth organization might merge or establish new plant, company codes, sales organizations etc.

 

Technical Impact due to organization changes.

 

Due to organization changes we might get into technical challenges/issues for example as part of business expand Company ABC new plant has been acquired (Plant 0567) and now I would like to include this new plant wherever already nearby plants (0545) included to same area manager to analysis inbound/outbound business data,  for this I need to search where existing plant 0545 is restricted so that I can include new plant 0567.

 

Approaches to find 0545.

 

  • Search manually respective reports in BEx designer like Filters Area, Default Area, Restricted Key Figure  and Selections, etc....might be restricted anywhere in report like cells, so its very tough job if its complex report's
  • Search in table level,  however its required skill like BEx tables and relationship between tables
  • Search through FM however its gives unstructured data, need to format and also it works one report at a time.

 

Solution "One Click Away"

 

So, I had written ABAP program which will give list of reports which are restricted by given Infoprovider/InfoObect and characteristics value.

 

 

Here we can search based on Multiprovider/InfoCube/DSO OR Infoset wise, we have to select Radio button based on requirement as like below


1.png

 

Example for Mulitprovider.


Example 1

 

Now, I am searching based on Mulitprovider/InfoCube and selections as like below


Multi/Cube Tech Name: ALL (Means all Mulitproviders and Infocubes)


Infoobject Tech Name: 0PLANT (For Plant) You can give more infoobjects at a time.


Infoobject Value          : ALL (Means for All Plants).



2.png

Output:


Provides list of reports restricted by All 0PLANT values


Example 2

 

Now, I am searching based on Mulitprovider/InfoCube and selections as like below


Multi/Cube Tech Name: ALL (Means all Mulitproviders and Infocubes)


Infoobject Tech Name: 0PLANT (For to Plant) you can give more infoobjects at a time.


Infoobject Value            : 0123 (Where Plant equal to 111).


3.png

 

Output


Provides list of reports where plant restricted by 1111


4.png

Example for InfoSet.

 

Example 1


Now, I am searching based on Infoset and selections as like below


Multi/Cube Tech Name : ALL (Means all Infosets)


Infoobject Tech Name  : 0PLANT (For Plant) You can give more infoobjects at a time.


Infoobject Value            : ALL (Means for All Plants).


5.png

Output:


Provides list of reports restricted by All 0PLANT values


Example 2

 

Now, I am searching based on Infoset and selections as like below


Multi/Cube Tech Name: ALL (Means all Infosets)


Infoobject Tech Name: 0PLANT (For Plant), You can give more infoobjects at a time.


Infoobject Value            : ALL (Where Plant equal to 1111).


6.png

Output


Provides list of reports where plant restricted by 1111

7.png


Attached ABAP Code.



Note:

  • Added code, provided option to  search ODS wise also,
  • And changed selection screen sequence for more convenient


Thanks for reading Blog.



In addition to the "Few important commands in WAD in SAP – Part 1", in this blog I would like to share few more useful commands those usually use in many reporting scenarios.

 

Context Web Item:

The Context Web Item is used to show many of the reporting properties such as Sort, Select Filter value, Remove Filter value, Broadcast, Go to (Report Report Interface), Export to Excel, Export to CSV, Global currency, Variable screen etc when we do right click on the reporting area in the report. The properties will work when there are proper settings maintained for the query in the back end.

 

Report Report Interface Command:


When there is a requirement to have a jump report from the existing query to another query by pressing a button. This is simply called RRI. To achieve this we need to follow the below procedure,

 

  1. Create an RRI functionality using the transaction RSBBS in the SAP BW backend.
  2. Open WAD and create a button.
  3. Add a Command "Command: RRI" to the button by mentioning the RRI functionality created in the backend. Please find the below screenshots for details on the settings and how to add properties,

 

 

Currency Conversion Command:


When there is a requirement to have a currency conversion on pressing a button. To achieve this we need to follow the below procedure,

 

  1. Create a Currency conversion using the transaction RSCUR in the SAP BW back end.
  2. Open WAD and create a button.
  3. Add a Command "Command: SET_CURRENCY_TRANSLATION" to the button by adding the properties for currency conversion in the back end. Please find the below screenshots for details on the settings and how to add properties,

 

 

Ticker Web Item:


Ticker Web Item is used to show the scrolling kind of information on the top of the Report. We can add some custom messages where we can understand few details by reading the scrolling information.

Web Application Designer (WAD) is used to design templates for the report embedded in it. Usually report simply provides an excel based output. In addition to this WAD can provide some extra functionalities or properties such as Links, Buttons, Back screens, Filter panels, Tickers, Drop downs, Radio buttons, List boxes, Hierarchical filter selection, Info Fields etc to the existing report. Here in this blog I would like to share few important commands used,

 

Suppression of Messages:


While executing the Bex Reports, sometimes we may come across some warning messages, which are not much harm for reports, but Users may feel that there is some issue with the reports. So in this case we should suppress those messages, this can be done through WAD using the Web Item "System Messages". We need to suppress the messages by selecting the property Visibility as "Hidden". Please find the below screenshot where the setting needs to be done,

 

 

Filter on Selected Record:


Sometimes users may like to filter a row based on a field (For Example: Organizational Unit). Please find the initial data available in the report below,

 

Organizational Unit

Organizational Manager

Employee

Location

Country

Sales

Karl

Nichol

New York

USA

Sales

John

Martin

New York

USA

Sales

Randy

Micheal

London

England

Finance

Eva

Jana

Munich

Germany

 

When User selects a row (Organizational Unit) on "Sales", User should see only data related to the Organizational Unit "Sales". Please find the data how it should display,

 

Organizational Unit

Organizational Manager

Employee

Location

Country

Sales

Karl

Nichol

New York

USA

Sales

John

Martin

New York

USA

Sales

Randy

Micheal

London

England

 

To achieve this type of functionality we should as below,

 

  1. Create a Button using the Web item "Button Group".
  2. Edit the description of the Button to "Filter Selected" and select a command "SET_SELECTION_STATE_BY_BINDING". Please find the details in the below screenshot,

 

 

3. Add the field for Selection after selecting above command.


 

As per the above screenshot, whenever user clicks on the row based on the field "Organizational Unit", it will filters out that data only and show as output.

 

How to remove filter after selecting a filter on a Row:


  1. Create one more Button and name as Back to Initial.
  2. Select a command "BACK_TO_INITIAL_DP_STATE". Please find the details in the below screenshot,

 

 

After selecting the above command, the screen of the report will move to the initial screen as below,

 

Organizational Unit

Organizational Manager

Employee

Location

Country

Sales

Karl

Nichol

New York

USA

Sales

John

Martin

New York

USA

Sales

Randy

Micheal

London

England

Finance

Eva

Jana

Munich

Germany

Hi BEx community,

 

These days I came across a requirement to find out when was the last used date of a certain Workbook. I could not find an easy way to enter the Workbook title and simply get the Workbook last used date information.

 

 

Anyway, I just found a three steps way to find out this useful information (let's say you want to clean-up some unused Workbooks and want to make sure it was not used for the last couple of years, for instance). Below I describe how I found the Workbook last used date.

 

 

1- Make sure the system is collecting statistics for the given Workbook:

 

Enter transaction RSDDSTAT on BW backend, go to tab 'Workbook' and make sure the statistics are turned ON for this specific Workbook, otherwise you might no rely on this guide, since the Workbook might have being used but no statistics are being generated.

You can find more information about Configuring Statisics Properties on this link.

 

27-07-2015 15-51-52.jpg

 

2- Get the workbook_id information:

 

The Workbook statistics are saved under the workbook_id information and not it's title, so we need to get this information before filtering the statistic table. Enter table RSRWBINDEXT on se16 transaction, filter attribute TITLE by the Workbook name and copy the value on WORKBOOKID attribute.

 

27-07-2015 15-52-19.jpg

 

3- Filter statistic table RSDDSTAT_OLAP to get last used date and other information:

 

With the workbook_id, enter table RSDDSTAT_OLAP on se16 transaction and filter attribute OBJNAME = workbook_id.

Make sure the values returned are NOT being truncate by the Maximum No. of Hits (You might need to use the CALDAY attribute to restrict the result-set if this is the case).

Once you have the results, you can sort it by CALDAY attribute in descending way. This same CALDAY will show you the last day the Workbook was used.

You can find other information such as UNAME: user that executed it; UTIME: time it was executed; etc.

 

27-07-2015 15-53-08.jpg

 

Hope this little Blog Post can help you to easily find out the Workbooks last used date for whatever reason you might need it.

 

 

Cheers,

Eduardo

 

See other posts:

Terminations on BEx Queries due to missing/inconsistent logical indexes on SAP HANA

Unexpected values in Bex Query? Check out SAP collective notes for incorrect/wrong data in BW queries

Bex Query going into debug mode in RSRT? Check here how to find the solution

Actions

Filter Blog

By author:
By date:
By tag: