Skip to Content
0
Former Member
Sep 05, 2013 at 08:34 AM

How to display selective formula in BEX

68 Views

Hi all,

I have monthwise YTD data in my cube and the user needs to have two queries built on this:

1. To display the month wise YTD data for selected profit centre and G/L account.

The user will input range of month for this to see the data of the month as desired. This is simple and I have done this.

2. The user needs to see the "FOR THE MONTH" data. This is achieved by subtracting YTD for that month and the YTD for the previous month.

"For the month" value- month 04 = YTD value for 4rth month - YTD value for 3rd month.

I have created a query with the selections one for each month to get the YTD data. These selections are restricted by the calmonth variable which calculates the months for the year from the user input ( as of now, I have asked user to enter only single year in the selection screen).

Now, I have created 12 formula to show the "for the month" value by subtracting the previous month YTD value in each formula.

Problems:

1. The query always displays all the 12 formula and hence 12 columns in the output thus confusing the user. Can the formulas displayed be restricted by any logic based on the user input of calmonth in the selection screen.

2. The user gets to enter only year in the selection screen, he wants to enter a range of months and see the value for those months only.

It is OK if all the 12 columns are displayed. But the months other than that entered by the user should be blank or should have 0.

Regards,

Saurabh Ojha