Structures in BEx Query Designer

[Former Member]

Hi experts,

I want to configure a report like the following one:

Table
	ene-15	feb-15	mar-15	abr-15	may-15
Beginning Balance	100,00	107,00	107,00	120,00	125,00
Movement 1	2,00	10,00	4,00	8,00	12,00
Movement 2	20,00	10,00	13,00	7,00	8,00
Movement 3	15,00	20,00	4,00	10,00	5,00
Ending Balance	107,00	107,00	120,00	125,00	140,00

Ending Balance = Beggining Balance + Movement 1 + Movement 2 - Movement 3.

And Beggining Balance is equal to previous month Ending Balance.

As you can see, I'm using "0CALMONTH" in the column selections. But I also need to define Beggining Balance as a selection depending on 0CALMONTH due "Beggining Balance is equal to previous month Ending Balance". I know it is not technically possible because I cannot use the same characteristic in both, row and column, selections.

Which would be the solution for this case?

Thanks in advance.

Regards,

Juana

[yasemin_kilinc · Accepted solution]

Hi Juana,

There are a few alternative ways that come to my mind. The easiest would be adding a characteristic such as zcalmonth to your infoprovider, use one of them in the columns and use the other in rows. But this alternative reuires reloading of the infoprovider. If you are not allowed to make changes in modelling side, then you can use cell definitions for creating this template, but it will be much more effort than the other alternative.

Hope it gives an idea

Regards

Yasemin...

[Former Member · Accepted solution]

Hi Yasemin,

Could you detail the cell definition process I should configure?

Thanks in advance.

Ana

[former_member206621 · Accepted solution]

Juana,

I have a query with similar requirements and we solved it by doing this:

• add a structure in the Columns (our structure is based on selections on the current period because we always have to give an overview for the last two years) --> I do not know what your requirements are here?
• add a structure in the Rows (structure for beginning balance, hired, ...)

When you have 2 structures (one in Columns and one in Rows) you can make use of Cells.

In Cells the definition for Amont Ending Balance ene-15 should be the same as the definition for Amount Beginning Balance feb-15

Kind regards,

Lieselot

[yasemin_kilinc · Accepted solution]

Hi Juana,

Sorry for the late response. Lieselaot has mentioned clearly what I suggested.

[Former Member · Accepted solution]

Thank you, Lieselot and Yasemin.

I could solve the issue.

[KodandaPani_KV]

HI,

can you try with the marcos may be it will work.

[ccc_ccc]

Hi Juana,

Requirement bit confusing , please explain more detail

Regards,

Nanda

[Former Member]

Here below I'm giving further information about the report. It's a headcount summary built on standard HR 0PA_C01 infocube.

	ene-15	feb-15	mar-15	abr-15	may-15
Beginning Balance	100,00	107,00	107,00	120,00	125,00
Hired	2,00	10,00	4,00	8,00	12,00
Terminations	15,00	20,00	4,00	10,00	5,00
Ending Balance	107,00	107,00	120,00	125,00	140,00

Ending Balance = Beggining Balance + Hired - Terminations.

And Beggining Balance is equal to previous month Ending Balance.