Help with MDX Query

  • Hi,

    I have an MDX query, in which I'm filtering the data based on a where clause and it works fine.

    SELECT {[Measures].[A],[Measures].} ON COLUMNS,

    {[Dim1].[Hierarchy].Children} ON ROWS

    FROM

    [CUBE]

    WHERE

    {[Dim2].[Date].[2007-12-31]}

    Now I have modified the filter in the where clause of my query to following:

    SELECT {[Measures].[A],[Measures].} ON COLUMNS,

    {[Dim1].[Hierarchy].Children} ON ROWS

    FROM

    [CUBE]

    WHERE

    {[Dim2].[Date].[2007-12-31] OR [Dim2].[Date].[NULL] }

    This query returns the following exception:

    Mondrian Error:No function matches signature ' '

    any suggestions....

    Thanks,

    Raj

  • Hi,

    replace OR with , (comma).

    [font="Arial"]Tomislav Piasevoli[/font][font="Arial Narrow"] - Business Intelligence Specialist - [/font]http://www.softpro.hr

  • Hi Tomislav Piasevoli,

    Thanks for your reply.

    I have replaced 'OR' with comma (,) and its giving me a different error. Modified query looks like:

    SELECT {[Measures].[A],[Measures].} ON COLUMNS,

    {[Dim1].[Hierarchy].Children} ON ROWS

    FROM

    [CUBE]

    WHERE

    {[Dim2].[Date].[2007-12-31],[Dim2].[Date].[NULL] }

    and the error is: WHERE clause expression returned set with more than one element.

    Thanks,

    Raj.

  • OK, I think I know what you need.

    WITH MEMBER [Dim2].[Date].[MM]

    AS

    ' Aggregate( { [Dim2].[Date].[2007-12-31], [Dim2].[Date].[NULL] } ) '

    SELECT

    { [Measures].[A], [Measures]. } ON COLUMNS,

    { [Dim1].[Hierarchy].Children } ON ROWS

    FROM

    [CUBE]

    WHERE

    ( [Dim2].[Date].[MM] )

    Something like that. You should make an aggregate of two members and then put that multiple member (MM) aggregate in slicer.

    [font="Arial"]Tomislav Piasevoli[/font][font="Arial Narrow"] - Business Intelligence Specialist - [/font]http://www.softpro.hr

  • Thanks Tomislav Piasevoli, for your reply.

    When I changed my query to

    WITH MEMBER [Dim2].[Date].[MM]

    AS

    ' Aggregate( { [Dim2].[Date].[2007-12-31], [Dim2].[Date].[NULL] } ) '

    SELECT

    { [Measures].[A], [Measures]. } ON COLUMNS,

    { [Dim1].[Hierarchy].Children } ON ROWS

    FROM

    [CUBE]

    WHERE

    ( [Dim2].[Date].[MM] )

    it throws the following error:

    Mondrian Error:Hierarchy for calculated member '[Dim2].[Date].[MM] ' not found

    Thanks,

    Raj

  • IF I want use OR condition then What I have to Do ? Following query give me error : No function matches signature '<Member> OR <Member>'.

    WITH
    SET [~ROWS] AS
      {[Location_Cluster.default].[Location_Cluster].Members}
    member [Date].[Rang] as
    'Aggregate(([Date].[End_Date].[2017-01-28]:[Date].[End_Date].[2017-01-28]))'
    member [Taxonomy].[Name] as
     'Aggregate([Taxonomy].[Taxonomy_ID].[3] or [Taxonomy].[Taxonomy_ID].[178])'
    SELECT
    NON EMPTY {[Measures].[Total_Quantity], [Measures].[Total_Margin], [Measures].[Location_Count]} ON COLUMNS,
    NON EMPTY [~ROWS] ON ROWS
    FROM [APCS_SALES_CUBE] Where ([Date].[Rang],[Cluster.Cluster_Id].[3],[Taxonomy].[Name],[Company.Company_Name].[Compnay_Name].[2])

    If I replace OR with , then it will work, but I want OR condition.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply