Site icon Sibeesh Passion

Filters in MDX Queries

SibeeshVenu

Introduction

Today we will learn about filter conditions in MDX queries. There are so many conditions in MDX as we have SQL. I am going to discuss most used filter conditions in MDX. I hope you will like it.

Background

For the past few days I am working on the MDX queries. Since my applications data source were ADOMD data source, It was must to learn about MDX queries. If you are new to ADOMD, you can find out some tips here: ADOMD Tips

What is MDX?

Before going to start, we will see what MDX is?

  • MDX stands for Multidimensional Expression
  • It is a query language for OLAP databases like SQL for relational databases
  • It is also a calculation language
  • Its syntax is similar to spreadsheet formulas

    • If you are completely new to MDX, you can read basics here

    Expressions and Equivalent in MDX

    Now we will use this conditions in the MDX queries.I hope you are aware of MDX query basics now.

    MDX Filter Expression Examples

    To Check whether dimension value is empty
    To check whether a dimension value is empty or not, you need to add a filter condition as follows.
    [sql]
    {FILTER([My Dimension Group].[Dimension Name].[Dimension Name], Trim([My Dimension Group].[Dimension Name].CurrentMember.Name) = ”)}
    [/sql]

    To Check whether dimension value is Not Empty
    To check whether a dimension value is not empty or not, you need to add a filter condition as follows.
    [sql]
    {FILTER([My Dimension Group].[Dimension Name].[Dimension Name], Trim([My Dimension Group].[Dimension Name].CurrentMember.Name) <> ”)}
    [/sql]

    To Check whether dimension value Contains a particular value
    To check whether a dimension value Contains a particular value, you need to add a filter condition as follows.
    [sql]
    {FILTER([My Dimension Group].[Dimension Name].[Dimension Name], Instr([My Dimension Group].[Dimension Name].CurrentMember.Name, ‘My String Value’) > 0)}
    [/sql]

    To Check whether dimension value Does Not Contains a particular value
    To check whether a dimension value Does Not Contains a particular value, you need to add a filter condition as follows.
    [sql]
    -{FILTER([My Dimension Group].[Dimension Name].[Dimension Name], Instr([My Dimension Group].[Dimension Name].CurrentMember.Name, ‘My String Value’) > 0)}
    [/sql]

    To Check whether dimension value Starts With a particular value
    To check whether a dimension value Starts With a particular value, you need to add a filter condition as follows.
    [sql]
    {FILTER([My Dimension Group].[Dimension Name].[Dimension Name], Left([My Dimension Group].[Dimension Name].CurrentMember.Name, 5) = ‘My String Value’)}
    [/sql]

    To Check whether dimension value Ends With a particular value
    To check whether a dimension value Ends With a particular value, you need to add a filter condition as follows.
    [sql]
    {FILTER([My Dimension Group].[Dimension Name].[Dimension Name], Right([My Dimension Group].[Dimension Name].CurrentMember.Name, 5) = ‘My String Value’)}
    [/sql]

    To Check whether dimension value Equal a particular value
    To check whether a dimension value Equal a particular value, you need to add a filter condition as follows.
    [sql]
    {FILTER([My Dimension Group].[Dimension Name].[Dimension Name], [My Dimension Group].[Dimension Name].CurrentMember.Name = ‘My String Value Carolina’)}
    [/sql]

    To Check whether dimension value is NULL
    To check whether a dimension value is NULL, you need to add a filter condition as follows.
    [sql]
    {FILTER([My Dimension Group].[Dimension Name].[Dimension Name], [Measures].[Mesure Name] = NULL)}
    [/sql]

    To Check whether dimension value is NOT NULL
    To check whether a dimension value is NOT NULL, you need to add a filter condition as follows.
    [sql]
    NON EMPTY([My Dimension Group].[Dimension Name].[Dimension Name])
    [/sql]

    Following are the examples of queries which uses above mentioned expressions.

    Query 1
    [sql]

    SELECT {[Measures].[Mesure Name]} ON COLUMNS,
    ({FILTER([My Dimension Group].[Dimension Name].[Dimension Name], Trim([My Dimension Group].[Dimension Name].CurrentMember.Name) = ”)}) ON ROWS
    FROM [My Cube Name]

    [/sql]

    Query 2
    [sql]
    SELECT {[Measures].[Mesure Name]} ON COLUMNS,
    ({FILTER([My Dimension Group].[Dimension Name].[Dimension Name], Instr([My Dimension Group].[Dimension Name].CurrentMember.Name, ‘My String Value’) > 0)}) ON ROWS
    FROM [My Cube Name]
    [/sql]

    Query 3
    [sql]
    SELECT {[Measures].[Mesure Name]} ON COLUMNS,
    ({FILTER([My Dimension Group].[Dimension Name].[Dimension Name], Left([My Dimension Group].[Dimension Name].CurrentMember.Name, 5) = ‘My String Value’)}) ON ROWS
    FROM [My Cube Name]
    [/sql]

    Query 4
    [sql]
    SELECT {[Measures].[Mesure Name]} ON COLUMNS,
    ({FILTER([My Dimension Group].[Dimension Name].[Dimension Name], Right([My Dimension Group].[Dimension Name].CurrentMember.Name, 5) = ‘My String Value’)}) ON ROWS
    FROM [My Cube Name]
    [/sql]

    Query 5
    [sql]
    SELECT {[Measures].[Mesure Name]} ON COLUMNS,
    ({FILTER([My Dimension Group].[Dimension Name].[Dimension Name], [My Dimension Group].[Dimension Name].CurrentMember.Name = ‘My String Value Carolina’)}) ON ROWS
    FROM [My Cube Name]
    [/sql]

    Query 6
    [sql]
    SELECT {[Measures].[Mesure Name]} ON COLUMNS,
    ({FILTER([My Dimension Group].[Dimension Name].[Dimension Name], [Measures].[Mesure Name] = NULL)}) ON ROWS
    FROM [My Cube Name]
    [/sql]

    Query 7
    [sql]
    SELECT {[Measures].[Mesure Name]} ON COLUMNS,
    NON EMPTY([My Dimension Group].[Dimension Name].[Dimension Name]) ON ROWS
    FROM [My Cube Name]
    [/sql]

    That is for now 🙂

    Conclusion

    I hope someone found this article useful. Please share me your valuable thoughts and comments. Your feedback is always welcomed.

    Thanks in advance. Happy coding!

    Kindest Regards
    Sibeesh Venu

    Exit mobile version