[Mondrian] CASE/WHEN Statement in Where clause

Pedro Alves pmgalves at gmail.com
Mon Jan 28 08:34:04 EST 2013


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>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20130128/3d41ad9d/attachment.html 


More information about the Mondrian mailing list