Want all records - even records - null and not selected.

Business Warehouse

Moderators: Snowy, thx4allthefish

Post Reply

Want all records - even records - null and not selected.

Post by Guest » Sun Oct 19, 2003 2:52 pm

I want to see quantity for every posting date even if there has been no posting for a particular date I would like it to show up as zero.

If there are no records for a particular posting date the value returned is either null or no rows show up at all for that particular posting date depending on whether there are values for other columns.

I am able to use the suggestion of multiplying by +1 for values that show up as null values (since other columns have values). How do I deal with values that don't show up at all since no posting was made on that particular posting date.


Post by Guest » Mon Oct 20, 2003 3:59 am

read the SAP 'How to' documentation posted on SAPnet.


Post by Guest » Mon Oct 20, 2003 7:46 am

I searched through service.sap and could not locate the document - it has been referred to in this forum before - but I found many other how to docs but could not locate this one.

Any suggestions on where to locate it would be helpful - I have searched in the BW infoindex and could not find it.



Post by Guest » Mon Oct 20, 2003 8:44 am

You're right the Bxxxxxx's have removed it! wonder why?

Anyway here it is (not sure it will paste properly but if you give me your e-mail I can send it to you.

How to…
Display Unposted Values as Zero Values in Reporting


ASAP How to Paper

Applicable Releases: BW 2.0B, 2.1C
May 2001
1 Business Scenario
The BEx does not normally show characteristics that have no values. However, it occurs quite frequently that an enterprise requires zero values to be shown in reports although these zero values are not delivered in the DataSources. An example of this is a report on a chart of accounts. Even if certain accounts have not been posted with values, they still ought to be shown as having the value 0 in the report.

Normally this can be achieved by the use of structures. However, this involves incorporating all accounts that need to be displayed into the structure. This makes the query more complex and also increases maintenance efforts. Queries with complex structures also tend to run more slowly than queries with drilldowns.

The following describes a procedure for generating zero values from master data. There are two alternatives: In the first alternative the values are saved in an InfoCube; in the second alternative the zero values are generated using a RemoteCube.

2 The Result
The result is a report with a drilldown for the 'plant' and 'workcenter' characteristics. Values are displayed for all combinations of the 'plant' and 'workcenter' characteristics. If there is no value in the cube, zero values are shown.

Obviously, such a procedure does not distinguish between unposted zero values and posted zero values.

The following graphic shows the report without generated zero values:

The following graphic shows the same report with zero values for all other 'plant' and 'workcenter' master data combinations. It is also filtered by workcenter in order to retain a printable structure.

3 The Step By Step Solution
3.1 Generate a generic DataSource for necessary master data combinations
This step describes how to generate a generic DataSource for all master data combinations that have zero values. The zero values can then be stored in the InfoCube using this generic DataSource.

The following describes how to generate a generic DataSource for zero values for the InfoObjects 0PLANT and 0WORKCENTER.

1. Determine the master data tables for 0PLANT and 0WORKCENTER. One place you can find these is in the relevant InfoObject maintenance screen in the index tab 'Master data/texts'. In our example the master data tables are /BI0/PPLANT and /BI0/PWORKCENTER.
2. Use transaction SE11 to generate a database view. Choose the master data tables determined above as underlying tables in this view. Because the characteristic 0WORKCENTER is paired with the characteristic 0PLANT, you must insert a JOIN condition. Do not do this if characteristics are not compounded. This forms the Cartesian product of the involved characteristic values.
3. You must choose the keys of 0PLANT and 0WORKCENTER as fields for the view.
4. In the selection conditions for the view, you must specify that only active elements of both tables be selected. You do this by limiting the field OBJVERS to the value 'A'. You can also make other limitations here such as suppressing initial values.
5. Now save and activate the database view.
6. Use transaction SBIW in the BW system to specify a DataSource for transaction data that uses the database method just created as an extraction method.
7. The extraction method chosen is 'Extraction from view'. Enter the view created above and save it.
8. You can now use another screen to choose which of the DataSource fields are selectable. Choose both fields.
9. The DataSource has now been successfully created. You must now inform BW about it. You do this using the menu option 'Replicate DataSources'. This can be reached by going to the index 'Source Systems' in the Administrator Workbench and calling up the context menu for the entry that is the source system for the BW. After DataSources have been replicated the DataSource can be assigned to an InfoSource.
10. To do this, choose an existing InfoSource or create a new one. You must specify the BW system itself as the source system. You can directly assign 0PLANT and 0WORKCENTER in the transfer rules. The key figure 0QUANTITY and the corresponding unit 0UNIT are set to constant values. A routine is defined for 0CALMONTH that determines the calendar month from the current system date.
11. Activate the InfoSource. This can now be used to fill an InfoCube.

The routine for determining 0CALMONTH from the system date is defined as followed. The line in blue is the only one that needs to be added manually. The rest is generated by the system and is only given here so that you can see the entire coding.

G_T_ERRORLOG TYPE rssm_t_errorlog_int
ABORT LIKE SY-SUBRC. "set ABORT <> 0 to cancel datapackage
*$*$ begin of routine - insert your code only below this line *-*
* DATA: l_s_errorlog TYPE rssm_s_errorlog_int.

RESULT = sy-datum(6).
* returncode <> 0 means skip this record
* abort <> 0 means skip whole data package !!!
ABORT = 0.
*$*$ end of routine - insert your code only before this line *-*
3.2 Link the InfoSource to an InfoCube
3.2.1 Alternative 1: Fill a BasicCube with the InfoSource
In this step the InfoSource data fills a separate InfoCube. The additional zero values generated are therefore not mixed with the actual transaction data.

12. Copy the InfoCube whose zero values you want to display to another InfoCube.
13. Connect this InfoCube with the InfoSource defined above. You do this with update rules.
14. This zero value InfoCube can be filled using a corresponding InfoPackage.
15. In the example you can see that the InfoPackage is started on a monthly basis. When this is done, the initial values for each month are written to the cube.
3.2.2 Alternative 2: Connect an InfoSource to a RemoteCube
In this step the InfoSource data fills a separate InfoCube. The additional zero values generated are therefore not mixed with the actual transaction data. However, in contrast to alternative 1, the data is not physically saved - rather it is only read when required.
This has negative effects on performance if there is a large amount of data or many concurrent users.
In this case there might be no point in assigning the current date to the characteristic 0CALMONTH in the transfer rules. If necessary, modify the transfer rules appropriately. E.g. you could set 0CALMONTH to 01.1900 and then include this value as a filter or restriction in your query definition.
The example shown here will be run-capable from BW 2.0B SP 15/ BW 2.1C SP 7.

16. Copy the InfoCube whose zero values you want to display to another InfoCube. Create it as a RemoteCube and assign it to the InfoSource created previously.
3.3 MultiCube
The MultiCube is used for combining the cubes created in the previous sections into one cube for reporting purposes.

17. Create a MultiCube. Its underlying InfoCubes are the BasisCube plus the alternative 1 InfoCube OR the alternative 2 InfoCube.
18. Take all available characteristics and key figures into the MultiCube. The MultiCube refers to data from both underlying InfoCubes. You can define this using the button 'Identification'.
19. Create dimensions in the MultiCube to which you assign individual characteristics.
20. Save and activate the MultiCube. If you now start a report on the MultiCube, the drilldowns for 0PLANT and 0WORKCENTER in the report will display not only the characteristic value combinations with values in the cube, but also zero values for all other characteristic value combinations.
4 Remarks
The Cartesian product of all involved characteristic values can grow quite large. In this case it might make sense to create a DataSource per characteristic. In the example given, you would create a DataSource for 0PLANT and a separate DataSource for 0WORKCENTER.
Link these DataSources to an appropriate InfoCube. This cube should be a copy of the InfoCube whose zero values you want to display. Make sure that you fill all characteristics that are used in restrictions or in the query filter with meaningful values. You could use dummy values, e.g. ‘01.1900’ for 0CALMONTH, and include this dummy value into the restriction and/or filter.
Include the resulting InfoCubes into a MultiCube as described in step 3.2.

Posts: 2
Joined: Mon Feb 10, 2003 1:16 pm
Location: USA

Post by rvegesna1 » Thu Apr 15, 2004 12:48 pm

Create new formula as key figure*2/2, this will show 0.00 instead of null


Post by G1 » Thu Jan 13, 2005 9:27 pm

rvegesna1 wrote:Create new formula as key figure*2/2, this will show 0.00 instead of null
Thank you so much, This worked for me.
I was wondering searching the whole query designer as to how this could be done. Your formula did a trick.

Guest Rubens

Post by Guest Rubens » Mon Sep 12, 2005 1:10 pm

My friend did you find the this How to??

How to…
Display Unposted Values as Zero Values in Reporting

Could you send me it: rubens_barreto@terra.com.br

The description here does't have the grafics, so it's hard to difirenciate...

Posts: 1
Joined: Mon Oct 03, 2011 1:19 am

Re: Want all records - even records - null and not selected.

Post by franciscoaguiar » Mon Oct 03, 2011 1:23 am

Hello everyone,

I'm currently developing reports based on an old BW release, facing this exact problem.

Although it's been a while since the previous post, i have to ask:

Can someone send me that "How To" doc you were talking about, to francisco.aguiar@gmail.com ?


Post Reply