Documentações e Exemplos MDX (IF, Percentual, Dois Campos Calculados, alerta vermelho e verde)

Amigo leitor,

Para aqueles que usam MDX com o Mondrian/Pentaho seguem abaixo alguns exemplos interessantes:

Exemplo de Consulta MDX com Percentual

with member [Measures].[Qtde Percentual] as '([Measures].[Qtde] / [Decisao.Status].[Todos])', FORMAT_STRING = "Percent"
select NON EMPTY {[Measures].[Qtde], [Measures].[Qtde Percentual]} ON COLUMNS,
  NON EMPTY {[Decisao.Status].[Todos], [Decisao.Status].[DEFINITIVA], [Decisao.Status].[NAO DEFINITIVA]} ON ROWS
from [Tramitacao]

Exemplo de Consulta MDX com IF

WITH
   MEMBER [Product].[Beer and Wine].[BigSeller] AS
  IIf([Product].[Beer and Wine] > 100, "Yes","No")
SELECT
   {[Product].[BigSeller]} ON COLUMNS,
   Store.[Store Name].Members ON ROWS FROM Sales

Exemplo com dois campos calculados

WITH
   MEMBER [Measures].[Special Discount] AS
   [Measures].[Discount Percentage] * 1.5,
   FORMAT_STRING = 'Percent'
  
   MEMBER [Measures].[Special Discounted Amount] AS
   [Measures].[Reseller Average Unit Price] * [Measures].[Special Discount],
   FORMAT_STRING = 'Currency'
  
SELECT
   {[Measures].[Special Discount], [Measures].[Special Discounted Amount]} on COLUMNS,
   NON EMPTY [Product].[Product].MEMBERS  ON Rows
FROM [Adventure Works] WHERE [Product].[Category].[Bikes]

Exemplo com alerta (vermelho e verde)

with member [Measures].[Variance Percent] as '([Measures].[Variance] / [Measures].[Budget])', format_string = IIf(((([Measures].[Variance] / [Measures].[Budget]) * 100.0) > 2.0), "|#.00%|style='green'", IIf(((([Measures].[Variance] / [Measures].[Budget]) * 100.0) < 0.0), "|#.00%|style='red'", "#.00%"))
select NON EMPTY {[Measures].[Actual], [Measures].[Budget], [Measures].[Variance], [Measures].[Variance Percent]} ON COLUMNS,
  NON EMPTY Hierarchize(Union(Crossjoin({[Region].[All Regions]}, Union(Crossjoin({[Department].[All Departments]}, {[Positions].[All Positions]}), Crossjoin({[Department].[All Departments]}, [Positions].[All Positions].Children))), Crossjoin({[Region].[All Regions]}, Union(Crossjoin([Department].[All Departments].Children, {[Positions].[All Positions]}), Crossjoin([Department].[All Departments].Children, [Positions].[All Positions].Children))))) ON ROWS
from [Quadrant Analysis]

Exemplo com alerta (vermelho e verde) criado por um aluno durante um curso

with member [Measures].[Contribuicao] as '([Measures].[Venda] / [Loja].[Todos])', FORMAT_STRING = IIf((([Measures].[Contribuicao] * 100.0) < 30.0), "|#.00%|style='green'", "|#.00%|style='red'")
select NON EMPTY {[Measures].[Venda], [Measures].[Contribuicao]} ON COLUMNS,
 NON EMPTY Crossjoin({[Grupo].[Todos]}, {[Loja].[Todos], [Loja].[SUPERMERCADO DEMOSTRACAO  LJ.III               ], [Loja].[SUPERMERCADO DEMOSTRACAO  LJ.IV                ], [Loja].[SUPERMERCADO DEMOSTRACAO LJ.I                 ], [Loja].[SUPERMERCADO DEMOSTRACAO LJ.II                ], [Loja].[SUPERMERCADO DEMOSTRACAO LJ.V                 ], [Loja].[SUPERMERCADO DEMOSTRACAO LJ.VII               ]}) ON ROWS
from [Venda_Classificacao]

Documentações MDX

Mondrian
http://mondrian.pentaho.com/documentation/olap.php

Microsoft
   * Key Concepts in MDX (MDX)
   * MDX Query Fundamentals (MDX)

SAS
   * MDX Introduction and Overview
   * MDX Queries and Syntax
   * MDX Usage Examples
   * MDX Functions