[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