[Mondrian] CASE/WHEN Statement in Where clause
Kleyson Rios
kleyson.rios at saude.go.gov.br
Thu Jan 31 10:11:28 EST 2013
For reference If one day someone need to do the some thing.
Using a Pedro's tip I did:
with set [Custom] as 'iif("All"="All", {[Tempo].[2011]},
Crossjoin({[Tempo].[2011]}, {[Tipo Despesa].[Honorario]}))'
select NON EMPTY {[Measures].[ConstStr]} ON COLUMNS, NON EMPTY
Order({[Convenio].[Todos convenios].Children},
[Measures].[Porcentagem], DESC) ON ROWS
from [Fatur]
where [Custom]
Regards.
--
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)
Em 28/1/2013 11:34, Pedro Alves escreveu:
> Kleyson, you're using cde - instead of doing the if at mdx level, just
> pass the appropriate param already set
>
>
>
> On Monday, January 28, 2013, Kleyson Rios wrote:
>
> My dashboard has a combobox where the options are:
> - All (static one)
> - Desp. Medicas (come from a select)
> - Honorarios (come from a select)
>
> So, I need create filters based on the selection.
>
> If my user select "All" my MDX should be:
>
> select NON EMPTY {[Measures].[ConstStr]} ON COLUMNS, NON EMPTY
> Order({[Convenio].[Todos convenios].Children},
> [Measures].[Porcentagem], DESC) ON ROWS
> from [Fatur]
> where {[Tempo].[2011]}
>
> But if my user choose "Honorarios" should be:
>
> select NON EMPTY {[Measures].[ConstStr]} ON COLUMNS, NON EMPTY
> Order({[Convenio].[Todos convenios].Children},
> [Measures].[Porcentagem], DESC) ON ROWS
> from [Fatur]
> where Crossjoin({[Tempo].[2011]}, {[Tipo Despesa].[Honorario]})
>
>
> The same idea in SQL would be:
>
> select fields
> from table
> where case when ${type} = 'All' then 1=1
> else type = 'Honorarios'
> end
>
>
>
> I've read a lot trying to find the answer but nothing until now.
>
> Thanks for the help.
>
> Regards.
>
>
>
> --
> 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)
>
>
> ----- Original Message -----
> From: "Matt Campbell" <mcampbell at pentaho.com>
> To: "Mondrian developer mailing list" <mondrian at pentaho.org>
> Sent: Friday, January 25, 2013 5:52:52 PM
> Subject: Re: [Mondrian] CASE/WHEN Statement in Where clause
>
>
>
>
>
> 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:
>
>
>
> select from sales WHERE
>
> case when 'All' = 'All' then gender.f else gender.m end
>
>
>
> Kleyson, can you give a little background on the intent of your
> query? There are probably more straightforward ways to construct it.
>
>
>
>
>
> -----Original Message-----
> From: mondrian-bounces at pentaho.org
> [mailto:mondrian-bounces at pentaho.org] On Behalf Of Julian Hyde
> Sent: Friday, January 25, 2013 2:31 PM
> To: Mondrian developer mailing list
> Subject: Re: [Mondrian] CASE/WHEN Statement in Where clause
>
>
>
> You've read the tutorial and you still think you can put a boolean
> expression in the WHERE clause? Read it again.
>
>
>
> Julian
>
>
>
>
>
> On Jan 25, 2013, at 10:30 AM, Kleyson Rios <
> kleyson.rios at saude.go.gov.br > wrote:
>
>
>
> > I Julian,
>
> >
>
> > Thanks for the answer.
>
> >
>
> > I've already read the tutorial.
>
> >
>
> > I am asking because in Microsoft website I saw an example using
> CASE/WHEN for select.
>
> >
>
> > I need to use the same idea for the WHERE.
>
> >
>
> > Can you give me a way in how to do that ?
>
> >
>
> > Thanks in advance.
>
> >
>
> > Kleyson Rios.
>
> >
>
> > ----- Mensagem original -----
>
> > De: "Julian Hyde" < jhyde at pentaho.com >
>
> > Para: "Mondrian developer mailing list" < mondrian at pentaho.org >
>
> > Enviadas: Sexta-feira, 25 de Janeiro de 2013 16:14:45
>
> > Assunto: Re: [Mondrian] CASE/WHEN Statement in Where clause
>
> >
>
> > 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.
>
>
>
> >
>
> > Julian
>
> >
>
> >
>
> > On Jan 25, 2013, at 10:12 AM, Kleyson Rios <
> kleyson.rios at saude.go.gov.br > wrote:
>
> >
>
> >>
>
> >> Hi,
>
> >>
>
> >> Is it possible use CASE/WHEN in Where clause ?
>
> >>
>
> >> For example:
>
> >>
>
> >> select NON EMPTY {[Measures].[ConstStr]} ON COLUMNS, NON EMPTY
>
> >> Order({[Convenio].[Todos convenios].Children},
>
> >> [Measures].[Porcentagem], DESC) ON ROWS from [Fatur] where CASE
> WHEN
>
> >> 'All' = 'All' then {[Tempo].[2011]}
>
> >> ELSE Crossjoin({[Tempo].[2011]}, {[Tipo Despesa].[Honorario]})
>
> >> END
>
> >>
>
> >> Thanks in advance.
>
> >>
>
> >> Kleyson Rios.
>
> >> _______________________________________________
>
> >> Mondrian mailing list
>
> >> Mondrian at pentaho.org
>
> >> http://lists.pentaho.org/mailman/listinfo/mondrian
>
> >
>
> > _______________________________________________
>
> > Mondrian mailing list
>
> > Mondrian at pentaho.org
>
> > http://lists.pentaho.org/ma
> <http://lists.pentaho.org/mailman/listinfo/mondrian>
>
>
>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20130131/71eac380/attachment-0001.html
More information about the Mondrian
mailing list