usage of the "where" clause with a variable...

[system]

Hello,

I try to use a value enter by a user in a prompt as condition for one “where clause”.

I explain ;-)…

When the report is launched, a prompt ask for start and end dates, plus for one country name ().

I now need to find a solution to use the country choosen by the user as “where clause” in one formula…

The problem is that the where clause uses like that :

=<Revenue> where (<Country>="USA")

So the value of the “where clause” has to be beetween the " " signs…

I have try to create a mesure withe the value entered by the user through the prompt and to enter

=<Revenue> where (<Country>="<MyCountry>") 

but as no country called exists, i Have an undefined result…
I also have tried

=<Revenue> where (<Country>=<MyCountry>)

but it returns a syntax error…

Si is it possible to use that in the reports ???

Thanks for your help…

---

epfyffer (BOB member since 2007-01-09)

[system]

Do you mean in the report or the select statement.
There is a function called UserResponse.
UserResponse(DataProvider, character string)

Where DataProvider would be the dataprovider for and
character string is the string used as prompt.

Hope this helps.

---

SilentBob (BOB member since 2006-09-28)

[system]

Hi,

       I am ot sure but I Think You can solve your problem using the concatenation function in buniness objects where you can concatenate 

" (Double quotes) with your variable value from prompt.

Hope this will solve your problem!

---

prashil123 (BOB member since 2006-08-25)

[system]

Hi and thanks for your quick answer.

I know the UserResponse fonc, buit I cannot use it here…

In fact, I cannot use the value entered by the user like this, it combines the value entered by the user AND some other letters to filter.

example :

the user enter USA as country…

I have to use some filtering criteria that will be USA1.CONS, USA2.CONS, USA2.DETAIL etc…

But the same report will be available withe the same filtering criteria for other countries , example GER1.CONS, GER2.CONS, GER2:DETAIL, …)

So actually I have duplicated the report as many time as I have countries… But if I could know how to use this value in the Where clause, I would be able to create only one generic report, and in term of maintenance it would be really better…

Thks in advance !

---

epfyffer (BOB member since 2007-01-09)

[system]

@prashil123

Hi,

       I am ot sure but I Think You can solve your problem using the concatenation function in buniness objects where you can concatenate 

" (Double quotes) with your variable value from prompt.

Hope this will solve your problem!

Do you mean something like this :

= where (=Concatenation(",,"))

???

Because it do not work, returns a syntax error…

It seems that a " is needed just after the sign = th where(=

…

but thanks for the idea !

---

epfyffer (BOB member since 2007-01-09)

[system]

I was thinking something along the line of

= If UserResponse(DataProvider(<Country>) ,"YourPromptStringHere")="USA" then <revenue> Else if ....

---

SilentBob (BOB member since 2006-09-28)

[system]

@SilentBob

I was thinking something along the line of

= If UserResponse(DataProvider(<Country>) ,"YourPromptStringHere")="USA" then <revenue> Else if ....

Hi,

well, I donnot understand how you can use your code sample with my need.

The situation is the following :

I have let’s say 8 similar reports, each is specific for one country (USA, GER, SWI, AUT, etc…).
Each country is declined in 4 different consolidations (USA1.CONS, USA2.CONS, USA1.DETAIL, USA2.DETAIL, etc…) that exists as in the univers.
Actually, in my formulas I have entered each time the complete consolidation name (with the “where =“USA1.CONS””, …) to calculate some formulas and it works quite well.
If I could use a variable in the where clause, It would give me something like this : where = (&1.CONS)" this mean that in all my formulas, I would not have to change the consolidation name, I just would have to ask the in a prompt and the report would be OK for all my consolidations.

So can I use your code for that ??? [/code]

---

epfyffer (BOB member since 2007-01-09)

[system]

Sorry, I don’t think you can use this formula. I was posting just as you were, it came a little late .

Maybe when you can write (or copy to) a freehand sql statement, this would be better.

where
ConsolidationCode = @prompt('1. Select Country','A',{'USA','GER'},mono,free) || '.1CONS'

The || part is for concatenation.
I don’t know how to use the @prompt in a universe, when I add a prompt it’s always an @variable when I look at the sql.
you’d still end up with having to create a different report for every consolidation code. Or do you want it all in 1 report?

---

SilentBob (BOB member since 2006-09-28)

[system]

@SilentBob

I don’t know how to use the @prompt in a universe, when I add a prompt it’s always an @variable when I look at the sql.
you’d still end up with having to create a different report for every consolidation code. Or do you want it all in 1 report?

No in fact I donn’t care to create one report for each conso, it would be really great if I could just create one valiable (called USA or GER or AUT) on each report and that I donnot have to modify the formulas…

Now, I never used sql statement… What’s the way I have to use it ?

Many thanks for your time !

---

epfyffer (BOB member since 2007-01-09)

[system]

I think the easiest way is to create your select statement using the universe, then use the SQL Viewer to select and copy the select statement to a Free-Hand SQL report.

If you used a prompt in the universe it will be the @variable in the free-hand sql WHERE statement. Replace this with an @prompt.

There is more information on @prompt in the BusinessObjects User Guide’s pdf (should be in the help menu). We’re using version 5.1.5 and it’s on page 131.

---

SilentBob (BOB member since 2006-09-28)

[system]

What about something like this;

=<Revenue> where (<Country> = Left(<Consolidation Code>,3))

?

(I must be missing something, as this solution seems too easy!!)

---

jac (BOB member since 2005-05-10)

[system]

from the original post:

I now need to find a solution to use the country choosen by the user as “where clause” in one formula…

So, I thought there had to be user input somehow .
I guess there are many solution depending on what you prefer as outcome.

I’m a little confused though. We are talking about a where clause in an sql statement, right? Not in formula used in the report?
Or do you need the userresponse from the prompt (in sql) to be used in a formula on the report?

---

SilentBob (BOB member since 2006-09-28)

[system]

There is a FAQ about this:

FAQ: Reporter

Why do I get a Syntax Error (DMB0007) when I try to Sum() Where ( > 2000)? The syntax of WHERE Clause is: measure Where (dimension = (number or character string or date, dimension = …)) This means that the WHERE Clause in Business Objects Reporter only allows the “=” operator, and the right side of the equation has to be a constant as well (and not another report variable etc.). For example: Sum(<Sales>) Where (<Year> = 2000) will work Work around for original problem: Create ano…

Essentially you have to create two variables, one that captures the UserResponse() value and sets a flag, and then use that flag within the Where clause of the next variable.

---

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

[system]

Hi ,

    Just try out this 

   [b]=<Revenue> where (<Country>=concatenate(" " " ,<MyCountry>,""")[/b]

Don’t copy paste

Tyieng to give idea…

Hope this idea will help…

---

prashil123 (BOB member since 2006-08-25)

[system]

Hy guys,

Sorry for my delayed ansver, I had 10 days off at work !

So I have tried the tip to cratate a boolean variable, this works. But in fact its only partially answer to my need. In fact, I would like that the user choose USA for example, and I need to use in my formula "

=<Revenue> where (<Consolidation Code>>="USA1.CONS")

(this is what I have “hardcoded” in my formulas now. But as I had to reproduce the report for as many countries as I have to analyse it is not really usable.

So how could I use your tip to create this boolean with my need to use the enterred consolidation (USA, GER, SWE, …) and to use it in my formulas
= where (>=“USA1.CONS”)
= where (>=“USA1.DET”)
= where (>=“GER1.CONS”)
= where (>=“GER1.DET”)
= where (>=“SWE1.CONS”)
= where (>=“SWE1.CONS”)
etc…

I have try the concatenation and the & but this returns an error…

Just for you to know, I have actually 27 reports to update / maintain / modify just because of I cannot use this where clause as I want ! Grrrrr… Yesterday, just to change a little thing in the report, it just took me the complete day !

Pffffffff…

---

epfyffer (BOB member since 2007-01-09)