BusinessObjects Board

How to use case to select filters in where clause

building semantic layer
#1

Hi,
I have an object on wich I have to apply 2 conversions operations based on prompts. That´s working ok and I have made it as follows:
SELECT CLAUSE:

(dbo.FtLavouraPermanenteES.valorProducao * dbo.FtIndiceConversaoMoedas.indice)*(dbo_FtConversaoPrecoDestino.indice / dbo.FtConversaoPreco.indice)

WHERE CLAUSE:

(dbo.DimMoeda.descricao IN (@Prompt(Choose the destination currency', 'A', 'BDIPES\Moeda Destino Padrão', MONO, CONSTRAINED))) AND (dbo_DimAnoPrecoDestino.ano IN (@Prompt(Choose the index year', 'N', 'BDIPES\Ano Preço Destino Padrao', MONO, FREE)))  AND (dbo.DimIndicePreco.descricao IN (@Prompt('Choose the conversion index', 'A', 'BDIPES\Índice Preço Destino Padrão', MONO, CONSTRAINED)))

This is working ok. My problema is that I have to give user the choice NOT TO do this conversions and get only the value of dbo.FtLavouraPermanenteES.valorProducao. I have been trying to this with CASE WHEN command but I don´t know how to deal with the WHERE clause. Can anyone help me please?

apitol :brazil: (BOB member since 2006-08-02)

#2

And the version is 5.1.3.

apitol :brazil: (BOB member since 2006-08-02)

#3

Hi apitol,

Do you mean that you don’t want the prompts in the WHERE-clause to appear, when the user has chosen NOT to do the conversions?

If so, that is impossible: all the prompts will appear, even when they don’t need to…

Johan

JdVries :netherlands: (BOB member since 2006-02-06)

#4

Hi Johan,
Thanks for the reply. It´s exactly that what I want.
What if I used another object? I´ve tried this too with @variable but it didn´t work either.
Andréa

apitol :brazil: (BOB member since 2006-08-02)

#5

You can use a tip I use to make an ‘ALL’ selection like.

First, create a dimension called ‘%’ or ‘NO CONVERSION’ with de the definition ‘%’ or ‘NO CONVERSION’

Then, in the conditions part, add near to your condition a condition like
‘NO CONVERSION’ =
and link them with OR.

If the user times ‘NO CONVERSION’ the second condition will be always true and the first one wont interfear. If he selects something else, it will work normally.

Hope my english is not too bad

( ^ _ ^ )

anissmansouri (BOB member since 2006-01-11)

#6

Hi anissmansouri, I don´t know how to use the ‘all’ option you refered to. Can you please give me an example? Thanks a lot
Andréa

apitol :brazil: (BOB member since 2006-08-02)

#7

Take a look into FAQ: Designer

MarcoR :us: (BOB member since 2006-03-29)