BusinessObjects Board

Use date variable in report filter

building reports webintelligence
#1

Hi,

Consider a variable named DynamicDate with this formula:

=FormatDate(RelativeDate(CurrentDate(); -
DayNumberOfMonth(CurrentDate())); “yyyyMM”)

When run anytime in August 2013, it returns “201307”

I would like to use this variable a filter in my report - i.e. DimYearMonth = DynamicDate

I want to do this for report scheduling purposes - we always schedule our production reports to run on the first or second of the month - with an “as of” date for the previous month.

In this example, when this scheduled report runs on August 2, 2013 the filter for DimYearMonth will be equal to “201307”

Any ideas on how to make something like this work?

P.S. I’m aware of how to do this at the Universe level by using SQL in a pre-defined condition. My issue is that this is a multi-source Universe and that solution does not work in 4x… SAP has confirmed this.

sunsethill :canada: (BOB member since 2007-01-29)

Question related to Yesterday data ?
#2

Hello,

I didn´t understand your issue. You want to use the calculated date as a query filter ?

rgoulart :brazil: (BOB member since 2011-08-21)

#3

That is exactly what I want to achieve.

sunsethill :canada: (BOB member since 2007-01-29)

#4

Okay,

what are your date objects ? You have any date objects like yyyyMM ? If so, what is the last ocurrence of that in 08/2013 ?

rgoulart :brazil: (BOB member since 2011-08-21)

#5

Yes, I have a date object in YYYYMM format. If the report is run after the end of a month (i.e. August 2), I want the report to be filtered on 201307.

sunsethill :canada: (BOB member since 2007-01-29)

#6

If you run your report with just the this object, with no filters what do you get ? My solution will be based on this .

rgoulart :brazil: (BOB member since 2011-08-21)

#7

I’m not sure I follow. I have the variable created that calculates “201307” - how do I use this to filter the data in my YYYYMM dimension object?

The YYYYMM dimension is currently a filter in the report with a prompt.

sunsethill :canada: (BOB member since 2007-01-29)

#8

In webi, as far as I know, you can´t use a user object as a query filter. So your solution will have to be at query side with your Universe objects.
If your last dimension object for the yyyyMM dimension , at 08/02/2013, is 201307 , please take a look at pic1, if is 201308, see pic2. If you have future values for your
Otherwise we´ll have to discuss it further.
I´m on 3.1 but think that works for 4.x
Assume that [dat base] is your DimYearMonth
pic2.PNG
pic1.PNG

rgoulart :brazil: (BOB member since 2011-08-21)

#9

Thanks for the reply and I do appreciate your assistance. Unfortunately by the time the report is run, the YYYYMM dimension is already populated with 201308 in the database - hence the need to filter on 201307… (i.e. previous year/month)

sunsethill :canada: (BOB member since 2007-01-29)

#10

Try the solution on pic2. Do you have access to web rich client 3.1 ? If so, I can send you a wid with the solution.

rgoulart :brazil: (BOB member since 2011-08-21)

#11

I do have access to Webi Rich client. Thank-you.

sunsethill :canada: (BOB member since 2007-01-29)

#12

Hi , sorry for the delay. I cannot generate a .wid with the solution for this. But I wrote a ppt trying to explain my solution. Any doubts, please contact me on the e-mail.
Cheers,

Rogerio
previous period.ppt (372.0 KB)

rgoulart :brazil: (BOB member since 2011-08-21)