none
MDX equivalent of T-SQL query RRS feed

  • Question

  • I am working on a query to get latest Invoice Date per Company in a cube and struggling to get the result.

    Following is the T-SQL equivalent of the MDX which I am trying to build:

    SELECT co.CompanyCode,Max(InvoiceDate) Invoice FROM FctInvoice fct
    inner join DimCompany co on fct.CompanyKey=co.CompanyKey
    where CompanyCode in ('ABC','DEF','GHI','IJK')
    group by co.CompanyCode

    I was able to fetch the latest Invoice date in cube but couldn't integrate Company into the query. Following is what I managed to get.

    SELECT {} ON COLUMNS ,
    {BOTTOMCOUNT(
    NONEMPTY(
    ORDER(
    {([Date Invoiced].[Date Invoiced].[Day].members) },
    [Date Invoiced].[Date Invoiced].CurrentMember.MEMBER_KEY,
    ASC)
    ),1) }
    ON ROWS
    FROM [Company Sales];

    Thanks in advance.

    Wednesday, June 7, 2017 3:14 PM

All replies

  • Something like the following should work:

    WITH MEMBER [Measures].[LastDate] AS
    TAIL(
        NONEMPTY( 
    [Date Invoiced].[Date Invoiced].[Day].members, [Measures].[Invoice Amt])
    ).Item(0).Name

    SELECT {
    [Measures].[LastDate]} ON COLUMNS ,
    {[Company].[CompanyCode].[ABC]
    [Company].[CompanyCode].[DEF]
    [Company].[CompanyCode].[GHI]
    [Company].[CompanyCode].[UK]}
    ON ROWS

    FROM [Company Sales];

    Note that you don't need to order members by their key unless you have overridden the default settings as attribute members are always ordered by their keys by default. And bottomcount does an implied sort as well, so it's much more efficient to use Tail() to get the last member (tail has a optional second arguement for how many members to return which defaults to 1). Because Tail() actually returns a set of members (even though this set only has 1 member) I use the item(0) to get the first member in this set. It's also a best practice to use an explicit measure when using the NONEMPTY function otherwise it will scan all your measures which can be much slower and could produce incorrect results in some edge cases.


    http://darren.gosbell.com - please mark correct answers

    Wednesday, June 7, 2017 11:00 PM
    Moderator
  • Hi Karanjeet,

    Thanks for your question.

    Please kindly mark the helpful replies as an answers. By doing so, it will benefit all community members who are facing similar issues. Your contribution is highly appreciated.

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Wednesday, June 14, 2017 1:34 AM
    Moderator