<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<div class="moz-cite-prefix">For reference If one day someone need
to do the some thing.<br>
<br>
Using a Pedro's tip I did:<br>
<br>
with set [Custom] as 'iif("All"="All", {[Tempo].[2011]},
Crossjoin({[Tempo].[2011]}, {[Tipo Despesa].[Honorario]}))'<br>
select NON EMPTY {[Measures].[ConstStr]} ON COLUMNS, NON EMPTY<br>
Order({[Convenio].[Todos convenios].Children},<br>
[Measures].[Porcentagem], DESC) ON ROWS<br>
from [Fatur]<br>
where [Custom]<br>
<br>
Regards.<br>
<pre class="moz-signature" cols="72">--
Kleyson Rios
Gestor de TI
Coordenador de BI / Banco de Dados
Gerência de Tecnologia da Informação
Secretaria de Estado da Saúde de Goiás
(62) 3201-3707
Se não pode vencer pelo talento, vença pelo esforço. (Dave Weinbaum) </pre>
Em 28/1/2013 11:34, Pedro Alves escreveu:<br>
</div>
<blockquote
cite="mid:CAC_LyUj0zpqT0nr6QWFQ73GLCj7c+QuJUvtMfU67p8kZL5pxcw@mail.gmail.com"
type="cite">Kleyson, you're using cde - instead of doing the if at
mdx level, just pass the appropriate param already set
<div><br>
</div>
<div><span></span><br>
<br>
On Monday, January 28, 2013, Kleyson Rios wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
My dashboard has a combobox where the options are:<br>
- All (static one)<br>
- Desp. Medicas (come from a select)<br>
- Honorarios (come from a select)<br>
<br>
So, I need create filters based on the selection.<br>
<br>
If my user select "All" my MDX should be:<br>
<br>
select NON EMPTY {[Measures].[ConstStr]} ON COLUMNS, NON EMPTY<br>
Order({[Convenio].[Todos convenios].Children},<br>
[Measures].[Porcentagem], DESC) ON ROWS<br>
from [Fatur]<br>
where {[Tempo].[2011]}<br>
<br>
But if my user choose "Honorarios" should be:<br>
<br>
select NON EMPTY {[Measures].[ConstStr]} ON COLUMNS, NON EMPTY<br>
Order({[Convenio].[Todos convenios].Children},<br>
[Measures].[Porcentagem], DESC) ON ROWS<br>
from [Fatur]<br>
where Crossjoin({[Tempo].[2011]}, {[Tipo
Despesa].[Honorario]})<br>
<br>
<br>
The same idea in SQL would be:<br>
<br>
select fields<br>
from table<br>
where case when ${type} = 'All' then 1=1<br>
else type = 'Honorarios'<br>
end<br>
<br>
<br>
<br>
I've read a lot trying to find the answer but nothing until
now.<br>
<br>
Thanks for the help.<br>
<br>
Regards.<br>
<br>
<br>
<br>
--<br>
Kleyson Rios<br>
Gestor de TI<br>
Coordenador de BI / Banco de Dados<br>
Gerência de Tecnologia da Informação<br>
Secretaria de Estado da Saúde de Goiás<br>
(62) 3201-3707<br>
<br>
Se não pode vencer pelo talento, vença pelo esforço. (Dave
Weinbaum)<br>
<br>
<br>
----- Original Message -----<br>
From: "Matt Campbell" <<a moz-do-not-send="true">mcampbell@pentaho.com</a>><br>
To: "Mondrian developer mailing list" <<a
moz-do-not-send="true">mondrian@pentaho.org</a>><br>
Sent: Friday, January 25, 2013 5:52:52 PM<br>
Subject: Re: [Mondrian] CASE/WHEN Statement in Where clause<br>
<br>
<br>
<br>
<br>
<br>
Kleyson's example didn't actually have a Boolean in the WHERE,
it had a CASE statement that evaluates conditionally to a set.
That's supported, although a little odd. The following query
will run fine in Mondrian:<br>
<br>
<br>
<br>
select from sales WHERE<br>
<br>
case when 'All' = 'All' then gender.f else gender.m end<br>
<br>
<br>
<br>
Kleyson, can you give a little background on the intent of
your query? There are probably more straightforward ways to
construct it.<br>
<br>
<br>
<br>
<br>
<br>
-----Original Message-----<br>
From: <a moz-do-not-send="true">mondrian-bounces@pentaho.org</a>
[mailto:<a moz-do-not-send="true">mondrian-bounces@pentaho.org</a>]
On Behalf Of Julian Hyde<br>
Sent: Friday, January 25, 2013 2:31 PM<br>
To: Mondrian developer mailing list<br>
Subject: Re: [Mondrian] CASE/WHEN Statement in Where clause<br>
<br>
<br>
<br>
You've read the tutorial and you still think you can put a
boolean expression in the WHERE clause? Read it again.<br>
<br>
<br>
<br>
Julian<br>
<br>
<br>
<br>
<br>
<br>
On Jan 25, 2013, at 10:30 AM, Kleyson Rios < <a
moz-do-not-send="true">kleyson.rios@saude.go.gov.br</a> >
wrote:<br>
<br>
<br>
<br>
> I Julian,<br>
<br>
><br>
<br>
> Thanks for the answer.<br>
<br>
><br>
<br>
> I've already read the tutorial.<br>
<br>
><br>
<br>
> I am asking because in Microsoft website I saw an example
using CASE/WHEN for select.<br>
<br>
><br>
<br>
> I need to use the same idea for the WHERE.<br>
<br>
><br>
<br>
> Can you give me a way in how to do that ?<br>
<br>
><br>
<br>
> Thanks in advance.<br>
<br>
><br>
<br>
> Kleyson Rios.<br>
<br>
><br>
<br>
> ----- Mensagem original -----<br>
<br>
> De: "Julian Hyde" < <a moz-do-not-send="true">jhyde@pentaho.com</a>
><br>
<br>
> Para: "Mondrian developer mailing list" < <a
moz-do-not-send="true">mondrian@pentaho.org</a> ><br>
<br>
> Enviadas: Sexta-feira, 25 de Janeiro de 2013 16:14:45<br>
<br>
> Assunto: Re: [Mondrian] CASE/WHEN Statement in Where
clause<br>
<br>
><br>
<br>
> You are making a mistake that many people make when
learning MDX. You are assuming that the MDX WHERE clause has
the same purpose as the SQL WHERE clause. MDX WHERE clause
does not contain a boolean expression. I suggest that you read
a tutorial on MDX.<br>
<br>
<br>
<br>
><br>
<br>
> Julian<br>
<br>
><br>
<br>
><br>
<br>
> On Jan 25, 2013, at 10:12 AM, Kleyson Rios < <a
moz-do-not-send="true">kleyson.rios@saude.go.gov.br</a> >
wrote:<br>
<br>
><br>
<br>
>><br>
<br>
>> Hi,<br>
<br>
>><br>
<br>
>> Is it possible use CASE/WHEN in Where clause ?<br>
<br>
>><br>
<br>
>> For example:<br>
<br>
>><br>
<br>
>> select NON EMPTY {[Measures].[ConstStr]} ON COLUMNS,
NON EMPTY<br>
<br>
>> Order({[Convenio].[Todos convenios].Children},<br>
<br>
>> [Measures].[Porcentagem], DESC) ON ROWS from [Fatur]
where CASE WHEN<br>
<br>
>> 'All' = 'All' then {[Tempo].[2011]}<br>
<br>
>> ELSE Crossjoin({[Tempo].[2011]}, {[Tipo
Despesa].[Honorario]})<br>
<br>
>> END<br>
<br>
>><br>
<br>
>> Thanks in advance.<br>
<br>
>><br>
<br>
>> Kleyson Rios.<br>
<br>
>> _______________________________________________<br>
<br>
>> Mondrian mailing list<br>
<br>
>> <a moz-do-not-send="true">Mondrian@pentaho.org</a><br>
<br>
>> <a moz-do-not-send="true"
href="http://lists.pentaho.org/mailman/listinfo/mondrian"
target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br>
<br>
><br>
<br>
> _______________________________________________<br>
<br>
> Mondrian mailing list<br>
<br>
> <a moz-do-not-send="true">Mondrian@pentaho.org</a><br>
<br>
> <a moz-do-not-send="true"
href="http://lists.pentaho.org/mailman/listinfo/mondrian"
target="_blank">http://lists.pentaho.org/ma</a></blockquote>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
Mondrian mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org</a>
<a class="moz-txt-link-freetext" href="http://lists.pentaho.org/mailman/listinfo/mondrian">http://lists.pentaho.org/mailman/listinfo/mondrian</a>
</pre>
</blockquote>
<br>
</body>
</html>