BusinessObjects Board

variable in where condition

building reports desktopintelligence
#1

Hi all,

Actually i ve to use a variable to compare in where clause…

for ex: where a=b.

Thanks.

avinesh.sukumar (BOB member since 2006-02-17)

#2

Hi,

Can you give us some more detail about what you intend to achieve.

Raja Saripalli :us: (BOB member since 2005-12-14)

#3

If you are unsuccessful in getting a report variable to compare against another variable – it’s because the syntax of the Reporter WHERE clause does not support it. Can’t do it.

See this entry from our Reporter FAQ: Why do I get a Syntax Error (DMB0007) when I try to Sum() Where ( > 2000)?

The syntax has to be WHERE <variable/object> = constant.

The FAQ cited above suggests a work-around for one situation, perhaps you can figure out another for your case.

Anita Craig :us: (BOB member since 2002-06-17)

#4

Hi all,

Thanks for the response.

The situation is like this:

We have a brand say x.for each brand we have to display the measure y .The thing is we have version.version means which month data.for example we have data loaded in january 2006 means then version is 200601 like that.

so now we have to display for every brand say the variance value.
for example for brand x the measure y should be y value in 200511 - y value in 200601.

this version values are got from users.so it is been displayed as prompt in querypanel.

so now the measue should be y where version = which is entered by user in prompt(from version) - y where version = which is entered by user in prompt(to version) .

So this is the scenario.the format of where clause is where (object)=constant.

so now in this constant place i ve to compare the value that the user gives.i ve used userresponse but still it shoots errors as only constants are allowed.

if anyone knows pl let me know the solution.

Thanks and Regards.

avinesh.sukumar (BOB member since 2006-02-17)

#5

avinesh wrote:

you say the variable y is user prompt and i suppose its only one value adn which means its constant. now i dont understand why you are talking about variable in where clause.

Can you display a rough example of what you are expecting.

Raja Saripalli :us: (BOB member since 2005-12-14)

#6

You need to look at Anita’s response. This is simply not possible. The Where operator does not allow for anything other than a constant. You can do some clever things with If-then-else and 1s and 0s and ranks. Those techniques are described in the FAQ that Anita pointed you towards.

Steve Krandel :us: (BOB member since 2002-06-25)

#7

Yes, you cannot use “where” directly, but you need to go in an alternate route, similar to this:

  1. capture the 2 user reponses and create 2 variables:
    from version user response
    to version user response

  2. create 2 flag variables:
    Is from version Yearmonth = if = then “y” else “n”

Is to version Yearmonth = if = then “y” else “n”

  1. where ( = “y”) - where ( =“y”)

Hope this helps!!

Mahi (BOB member since 2004-07-22)

#8

Hi mraty9,

Thanks for your response. I tried as u said first creating from version and to version.then created 2 flags one for from version and another for to version.Then in the formula i gave

measure where toversionflag=“y” - measure where fromversion=“y”.

it shows syntax error highlighting toversionflag.

Can you please say how to resolve this.

Thans once again.

Regards.

avinesh.sukumar (BOB member since 2006-02-17)

#9

You need to cut/paste EXACTLY what you typed in order for us to see what’s wrong.

measure where toversionflag="y" - measure where fromversion="y"

is no where near valid syntax.

Steve Krandel :us: (BOB member since 2002-06-25)

#10

Avinesh,

As Steve mentioned you need to show us exactly what syntax you are using.

Avinesh Wrote:

if this is what you really tried then the syntax is wrong.

Try this instead as mraty9 already mentioned

(measure where (toversionflag="y")) - (measure where (fromversion="y"))

Raja Saripalli :us: (BOB member since 2005-12-14)