June 9, 2010 at 5:44 pm
Hi There,
I have a query to show the customers for the last 7 days ending on a certain date. So, I tried the query:
With LastPeriod:
WITH
MEMBER [Time].[Financial].TOTAL AS
SUM(LastPeriods(7,[Time].[FINANCIAL].[Date].&[2010-05-07 00:00:00] ))
SELECT {
LastPeriods(7,[Time].[FINANCIAL].[Date].&[2010-05-07 00:00:00]),[Time].[Financial].TOTAL} ON COLUMNS,
{FILTER([Customer].[Customer].[All Customer].Children,[Time].[Financial].TOTAL <> 0)} ON ROWS
FROM [SALES]
WHERE ([Periodicity].[View].&[1], [Measures].[ASales$] ,[State])
But this works too slow and takes upto 15 seconds to show the data. But when I don't use the lastperiods function and write the query as:
Without LastPeriod:
WITH
MEMBER [Time].[Financial].TOTAL AS
SUM({ [Time].[FINANCIAL].[Date].&[2010-05-01 00:00:00] , [Time].[FINANCIAL].[Date].&[2010-05-02 00:00:00] ,
[Time].[FINANCIAL].[Date].&[2010-05-03 00:00:00] ,
[Time].[FINANCIAL].[Date].&[2010-05-04 00:00:00] ,
[Time].[FINANCIAL].[Date].&[2010-05-05 00:00:00] ,
[Time].[FINANCIAL].[Date].&[2010-05-06 00:00:00] ,
[Time].[FINANCIAL].[Date].&[2010-05-07 00:00:00]
})
SELECT {
[Time].[FINANCIAL].[Date].&[2010-05-01 00:00:00] , [Time].[FINANCIAL].[Date].&[2010-05-02 00:00:00] ,
[Time].[FINANCIAL].[Date].&[2010-05-03 00:00:00] , [Time].[FINANCIAL].[Date].&[2010-05-04 00:00:00] ,
[Time].[FINANCIAL].[Date].&[2010-05-05 00:00:00] , [Time].[FINANCIAL].[Date].&[2010-05-06 00:00:00] ,
[Time].[FINANCIAL].[Date].&[2010-05-07 00:00:00] ,[Time].[Financial].TOTAL} ON COLUMNS,
{FILTER([Customer].[Customer].[All Customer].Children,[Time].[Financial].TOTAL <> 0)} ON ROWS
FROM [SALES]
WHERE ([Periodicity].[View].&[1], [Measures].[ASales$] ,[State])
Can anyone help me here, what is the problem with my lastperiods function query?
Thanks in advance,
Munish Sharma
June 16, 2010 at 10:07 am
If you are using parameters you can utilise a range of dates:
StrToMember(@ProgramStartDate) : StrToMember(@ProgramEndDate)
If not you can use the same syntax:
[Time].[FINANCIAL].[Date].&[2010-05-01 00:00:00] : [Time].[FINANCIAL].[Date].&[2010-05-07 00:00:00]
In your Date Hierarchy, is there an entry for every Year, Month, Day, Hour, Minute, Second?
You may be able to reduce query time by removing the 00:00:00 from your range.
gsc_dba
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply