OLAP Universes - Based on BEx Query - Month and Previous Month Functions

[Former Member]

Hi,

I created a Universe based on SAP BEx queries and I like to create a filter so that I use in Web Intelligence report to run for "Previous Month" data always. There are "Date" filelds in BEx Query(No Month info only date data). I have no knowledge of SAP BEx Queries and new to OLAP universes. Could you help.

How to create filter that show Previous Month.

Nanda Kishore

Edited by: Nanda Kishore B on Dec 26, 2010 6:15 AM

[former_member142702]

The easiest way (but NOT the most efficient one) is to create a local variable (Dimension) in your report with the following code:

=if (Month(CurrentDate())=1 AND Month([[MyDate]])=12 AND Year([[MyDate]])=(Year(CurrentDate())-1)) OR (Month(CurrentDate())>1 AND   Month([[MyDate]])=(Month(CurrentDate())-1)) then 1 else 0

where MyDate is the field from your query containing the data information

Activate the report filter area in your report design panel by pressing the Report filter icon and drag and drop your variable there and apply it to the entire report. Filter value 1 and you will get the data for the previous month.

Keep in mind that this approach is not optimal especially if you do have many rows of data delivered to your WebI report because filtering takes place only AFTER the data is retrieved. The Best practice here is to work with BEx variables.

How many rows of data does your WebI report normally fetches?

Regards,

Stratos

[Former Member]

Hi Stratos,

Thanks a lot.

I like to create a filter at Universe level. Any ideas how to create these at Universe Level.

There is lot of data and there is a limit of 100 k records at Universe level.

Please provide ideas on how to create a filter at universe level. Creation of these filters is easy if it is Oracle / MS SQL.

I am new to SAP BEx as I mentioned, so please help.

Thanks,

Nanda Kishore

Edited by: Nanda Kishore B on Dec 26, 2010 12:44 PM

[former_member142702]

Check the following document

http://help.sap.com/businessobject/product_guides/boexir31SP3/en/xi31_sp3_sap_olap_univ_en.pdf

Assuming you are using BO XI 3.1, you have the option to add your own MDX filters in your universe

Still MDX has some limitations and I do not believe that it is not possible to create filter at universe level that will filter the values for the previous month. This is indeed much easier to do when you have a univers ebased on a relational data source but for SAP BW basedf universes this is not the case.

If you want to have this implemented in your universe you HAVE to modify your BEx query and add there the appropriate filter.

Regards,

Stratos

[Former Member]

Nanda Kishore

In Report: For Month object are you getting previous month data or not??

YES, then

>> create Prompt @Query level or Universe.

>> @BEx, create variable which points to current month -1 always.

>> @WebI Report: Create function or variable, like follows:

Prev Month: =Month(CurrentDate)-1 , Pls check it as I have no connectivity to my server now.

I'm Back

[Former Member]

Hi BOCP BOE,

Welcome Back!!

Could you please elaborate on this please: create Prompt @Query level or Universe. I am looking for a solution to create filters at Universe level only.

I am new to SAP BEx Query based universes, may I request to provide Syntax.

Thanks in Advance.

Nanda Kishore

[Former Member]

Nanda Kishore

Create two variables in BEx , 1 for Current Month & another CM-1.

Use Calendar as default showing option for user to input date. -- Optional

Now design Universe, which comes up with two prompts, those are applied at Universe level.

Export it and do reporting.

Consult any BI guy who can help you, it's difficult for me to give syntax, without knowing Characteristics/Attributes/KF's & Infocube structure.

I'm Back

[Former Member]

Thanks