12 Months BEx Structure with Non-cumulative Key Figures

[Former Member]

Dear experts,

I am new to BI and would like to know how could I define the structure illustrated below in BEx Query Designer.

There are 3 time reference characteristics in the InfoCube (0CALYEAR, 0CALMONTH & 0CALDAY).

Firstly, user will key in a date, e.g. 12.05.2010, in the selection screen. In this case, balance brought from end of last year (31.12.2009) will be displayed in the first row, followed by month-end balance for each month (01 = Jan, 02 = Feb, etc.).

The bottom line shows the sum of each amounts (debit, credit & monthly balance) except accumulated balance.

Questions:

1. How can I define a selection for each month from the time reference chars? Can I achieve it without using Customer Exit?

2. Which aggregation type should I set for the accumulated balance in this scenario?

3. How can I define the opening balance in the first row and the accumulated balance in the last row? Using exception cell?

-

---

Month | Debit | Credit | Monthly Balance | Accumulated Balance

-

---

OB* | null | null | null | xxx

01 | -xxx | xxx | xxx | xxx

02 | -xxx | xxx | xxx | xxx

03 | -xxx | xxx | xxx | xxx

04 | -xxx | xxx | xxx | xxx

... | -xxx | xxx | xxx | xxx

... | -xxx | xxx | xxx | xxx

12 | -xxx | xxx | xxx | xxx

-

---

Sum | -xxx | xxx | xxx | xxx |

-

---

(OB = opening balance / balance brought forward)

Thanks in advance for the advice!

Regards,

Joon

[former_member182470]

This message was moderated.

[Former Member]

Hi Suman,

thanks for your response! I still have questions regarding Question 1.

1. Is it possible to achieve this without using Customer Exit?

2. By placing 0CALMONTH in the row, I will get the months listed only if there is any value for the month. E.g. if there is no data for April 2010, I will not see this row in the result. But I would like to have all 12 months displayed, even if the value is 0 (see example below). Can I make use of Structure to define this layout?

-

---

Jan xxx

Feb xxx

Mac xxx

Apr 0

May xxx

...

Nov 0

Dec 0

-

---

Moderator notice: do not ask for or offer points

Thanks,

Joon

Edited by: Matt on Jun 8, 2011 9:38 AM

[former_member182470]

Hi Joon,

If you want a fixed array of 12 columns for months, independently of the availability of transactional data, you can use a user-exit variable which calculates the first month of the year chosen by the user (if the user chooses "2010", the variable value should be "201001", which stands for January 2010). Then, you create a structure of 12 columns, assigning:

• First column (January): The user-exit variable. ("2010" -> "201001" which stands for January 2010).

• Second column (February): The user-exit variable with an offset of +1. ("2010" -> "201002" which stands for February 2010).

• Third column (March): The user-exit variable with an offset of +2. ("2010" -> "201003" which stands for February 2010).

...

• Twelfth column (December): The user-exit variable with an offset of +11. ("2010" -> "201012" which stands for December 2010).

If you prefer the second alternative, you can find a detailed how-to about user-exit variables at:

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/d09d4588-3832-2c10-e185-f778d9dbe...

Moderator notice: do not ask for or offer points

Regards,

Suman

Edited by: Matt on Jun 8, 2011 9:38 AM

[Former Member]

Hi Suman,

thanks for the idea. I think I know how to proceed.

Full points are awarded and thanks again

Regards,

Joon

[Former Member]

Yes you can make use of structures to get your layout.

Since you want Jan to Dec to displayed in the report output irrespective of the availability of data in the Infoprovider.

Create a Structure and then create 12 new selections inside that structure.

For the first selection, drag and drop 0calmonth and then restrict it with Jan and give description as you require.

For the Second selection, drag and drop 0calmonth and then restrict it with Feb and give description as you require.

Follow the similar steps for other selections as well.

By this way you can achieve your desired layout.

--- Thanks...

[former_member182470]

Hi Joon,

Thank you very much!!!!!

Regards,

Suman

[Former Member]

Hi rookie-sapbi,

If I drag 0CALMONTH into the selection, I can only choose fixed values, e.g. Jan 2009, Feb 2008 etc, right?

In this scenario, I need to determine the year dynamically. For example, if user enters 14.03.2011, I need to derive the year 2011 and create selections for the respective months like Jan 2011, Feb 2011, and so on.

Please kindly correct me if I am wrong. Thanks!

Regards,

Joon

[Former Member]

Joon,

If you want it to be dynamic, based on the user input.

then you should have one Customer-exit variable created for 0calmonth.

write logic in cmod to calculate first month based on the user input, for ex if user input is 14.03.2011 then the customer-exit should populate the value 201101 for the variable.

And then while restricting 0calmonth instead of entering the fixed values, select this variable.

for first selection in your structure just restrict with the variable.

and for your second selection increase the offset of the variable by +1

follow the same steps and increase the offset by 1 for the consecutive selections.

please revert if you need any help in writing the logic to populate the customer-exit variable.

--- Thanks...