BusinessObjects Board

Limiting an object based on two variables being equal

building reports desktopintelligence
#1

I am trying to limit a value based on the month, quarter, and year that the billings take place.

The following works perfectly as I intend.

=<Bill. Net (Val)> Where (<Month (Mmm)(data)> = ‘4/1/2006’)

I want to do the following and can’t figure out how to do it:

=<Bill. Net (Val)> Where (<Month (Mmm)(data)> = ‘<Month (Mmm)(current_date)>’)

This gives me a #SYNTAX error.

<Month (Mmm)(current_date)> is a variable pulled from a provider that provides the current week, month, quarter and year. There is only one result.

We are on a manufacturing calender (4,4,5). Hence, I cannot use dates based on the actual calendar date to figure out the current month.

I have tried many things but have failed miserably.

I am using version 5.1.9.

Thanks in advance.

:hb:

gravey :us: (BOB member since 2006-04-11)

#2

A) Are you using multiple data providsers?
B) Ask your data architect and/or universe designer to incorporate a smart calendar table to handle such things.

Andreas :de: (BOB member since 2002-06-20)

#3

Hi Gravey,

Create a variable with the formula:

=if(<Month (Mmm)(data)> = '<Month (Mmm)(current_date)>) then "Y" else "N"

name the variable something like , then recreate your formula using

=<Bill. Net (Val)> Where (<Date Is Current> = "Y")

I have tested it using the Island resorts univ and it works fine.

Let me know if it works.

Cheers

Jeff

jeffH :uk: (BOB member since 2006-03-31)

#4

A) For this test, I have 2 providers… one for the data and one to pull the current month. In the future, this report will have about 6 providers.

B) It will be close to impossible for me to get them to change anything.

gravey :us: (BOB member since 2006-04-11)

#5

Hi Jeff-

I think there is something wrong with the variable formula you posted. The ’ brings me an error. If i remove it, I do not encounter a error but it returns “Y” for every month. I tried something similar to that but with 1 and 0 instead. I am guessing that I am having problems with the IF satements.

Greg

gravey :us: (BOB member since 2006-04-11)

#6

Jeff-

Could you attach your file for me to look at? Unfortunately, my information is confidential or I would atach my file. I am not interested in getting fired! :wink:

Do you think it matters if I am trying to filter this in a crosstab.

Essentially, I have a chart with monthly, quarterly, and yearly billings side by side along with many other categories. I want to do this with the fewest amount of providers as possibles as BO is REALLY slow, and I am very interested in a quicker file.

Thanks.

Greg

gravey :us: (BOB member since 2006-04-11)

#7

The basic issue you’re facing is that the “Where” clause does not allow anything other than equality (meaning = ) tests. So the suggestion you were offered is a typical workaround for that.

It’s covered in this FAQ if you want to look at another example.

Dave Rathbun :us: (BOB member since 2002-06-06)

#8

Dave-

Thanks for your message.

However, I completely understand the concepts and am using them already. The problem is that if I use the variables instead of static values, I get an error, #IERR.

gravey :us: (BOB member since 2006-04-11)

#9

In those cases, I often start with a very basic block (table) that contains all of the elements that you are working with. Get the formula working, then start removing objects that you don’t want displayed. The #IERR often comes because you have referenced something in a formula that is not present in the block. Being in the data provider is not enough, it has to be in the block if it is part of the formula context.

Dave Rathbun :us: (BOB member since 2002-06-06)

#10

Dave,

I have a block with the following.

Month, Sales Area, Billings, and Billings (to be applied the code.)

Variable: current_month (dimension)
“dynamic:”

= If (<Month (Mmm'YYYY)(results)>=<Month (Mmm'YYYY)(current)>) Then "Y" Else "N"

static works perfectly:

= If (<Month (Mmm'YYYY)(results)>='4/1/2006') Then "Y" Else "N"

Column filtered for month

=<Bill. Net (Val)> Where (<month_current>="Y")

It returns the #IERR error. Am I correct that you wanted me to throw everything into block and then hide the stuff i did not want displayed? In this case, it would be month.

I have also tried the inlist method. everything works when I enter in static values instead of the variable.

The current provider returns one row of results and the data matches perfectly and is linked.

could this be a problem with the way the universe is setup?

Thanks again.

gravey :us: (BOB member since 2006-04-11)

#11

The problem could be that Month (Mmm’YYYY)(current) is not within the datablock. If this is the case, you could use calculation contexts to deal with the issue.

jac :australia: (BOB member since 2005-05-10)

#12

Hi,
if any of your variable which is being used in the another vaiabes calculation then you must have that variable hidden even though it’s not being used in the report

Omkar Paranjpe :us: (BOB member since 2006-02-13)