SQL Server Performance

Date range

Discussion in 'SQL Server 2005 Analysis Services' started by skynet54, May 23, 2007.

  1. skynet54 New Member

    Hi,
    I have to count the number of invoices between two dates (starting date and ending date).
    I have a time dimension, and a another with the two dates.
    I've created a measure :



    CREATE MEMBER [CTNAV].[Measures].[Nb]
    AS count(
    Filter
    (
    [Bon Vouchers].[N° Bon Vouchers].[N° Bon Vouchers].Members,
    [Bon Vouchers].[Starting Date ].CurrentMember.MemberValue >=
    [Time].[Date].CurrentMember.MemberValue
    )
    )


    The problem is that if I display all the dates, each measure is at 0 but the total is ok (>0).


    Sorry for my english.

    Thanks for your help.
  2. satya Moderator

    Can you display or extract for each day?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  3. skynet54 New Member

    Thanks for your help.
    Actually I#%92ve tried those solutions:


    WITH MEMBER [Measures].[Nb2]
    AS count(
    Filter
    (
    [Bon Vouchers].[N° Bon Vouchers].[N° Bon Vouchers].Members,
    [Bon Vouchers].[Starting Date].CurrentMember.MemberValue >=
    [Time].[Date].CurrentMember.MemberValue
    )
    )
    Select
    [Measures].[Nb2] On Columns,
    [Time].[DateVal].[Months] On Rows
    From [CTNAV]
    Where [Time].[Years].&[2007-01-01T00:00:00]


    The result is :

    Nb2
    janvier 200726
    février 200726
    mars 200726
    avril 200726
    mai 200726
    ...

    If I replace >= by <= the result is the same.

    If I do the same test with days:
    Select
    [Measures].[Nb2] On Columns,
    [Time].[DateVal].[Date] On Rows

    All the results are :
    0 with >= [Time].[Date].CurrentMember.MemberValue
    26 with <= [Time].[Date].CurrentMember.MemberValue

    I#%92ve also tried that:



    WITH MEMBER [Measures].[Nb2]
    AS count(
    Filter
    (
    [Bon Vouchers].[N° Bon Vouchers].[N° Bon Vouchers].Members,
    [Bon Vouchers].[Starting Date].CurrentMember.MemberValue >=
    Tail(Existing [Time].[Date].[Date]).item(0).item(0).MemberValue
    )
    )
    Select
    [Measures].[Nb2] On Columns,
    [Time].[DateVal].Months On Rows
    From [CTNAV]
    Where [Time].[Years].&[2007-01-01T00:00:00]


    In both cases (months and days), all the results are :
    > : 0
    < : 26

    To finish, for information if I replace the dimension expression by [Bon Vouchers].[ Starting Date].CurrentMember.MemberValue >=
    CDATE("2007-03-28") for example, the count is correct

    Nb2
    janvier 20073
    février 20073

    ...
  4. ranjitjain New Member

    Hi,
    Try this:
    --build a set of dates for which you need the count
    with
    set daterange as
    [Time].[Day].&[2006-05-18T00:00:00]:[Time].[Day].&[2006-05-20T00:00:00]

    member measures.countvoucher as
    count(nonempty([Bon Vouchers].[N° Bon Vouchers].[N° Bon Vouchers].Members,measures.[Voucheramount]))

    select daterange on 1,measures.countvoucher on 0 from
    [cubename]

  5. skynet54 New Member

    Thanks for your help.
    Actually I've resolved my problem but I've another one.
    I've created my own time table and I've declared the type of my start date as a "DateStart" ...



    With Member [Measures].[NbC] As
    Count
    (
    Filter
    (
    [Bon Vouchers].[Bon Vouchers].Members,
    [Bon Vouchers].[Start Date].CurrentMember.MemberValue >=
    Tail(Existing [DimDate].[Date].[Date]).item(0).item(0).MemberValue
    )
    )
    Select [Measures].[NbC] On Columns,
    [DimDate].[DateHier].[Date] On Rows
    From [CTNAV]

    Now I want to create a named set to filter by dates.
    There is no problem if it's not dynamic :


    Filter
    (
    [Bon Vouchers].[N° Bon Vouchers].[N° Bon Vouchers].Members,
    [Bon Vouchers].[Start Date].CurrentMember.MemberValue >=
    CDate('2006-11-11')
    Tail(Existing [DimDate].[Date].[Date]).item(0).item(0).MemberValue
    )

    But I can't consider the current date of my Time dimension.


    Filter
    (
    [Bon Vouchers].[N° Bon Vouchers].[N° Bon Vouchers].Members,
    [Bon Vouchers].[Start Date].CurrentMember.MemberValue >=
    Tail(Existing [DimDate].[Date].[Date]).item(0).item(0).MemberValue
    )

    Is it possible to do this ?

    Thanks for your help.

Share This Page