August 26, 2011 at 7:44 am
I have a requirement to display the last 8 weeks of sales data within a report. How can i achieve this? It should work dynamically updo yesterday date. in NOVAVIEW
Please give a MDX query for same
I have tried like this but i did not get result please help on this
Sum({HEAD (NonEmpty([Time Period].[Time Period].[Day]))},[Measures].CurrentMember)
Sum({ClosingPeriod([Time Period].[Time Period].[(All)],[Time Period].[Time Period].[All])},([Measures].CurrentMember))
Thanks in advance..
Kanagarajan.s
September 3, 2011 at 2:44 am
You will have to use Now() function to dynamically get what you want. Here is a snippet of code for AW @ month Level. You can appropriately change it to Week level. I have tried to give all the information that is required to get Week level.
With Member CurrentMonthNum As
Month(Now())
Member CurrentYearNum As
Year(Now()) - 9
Member CurrentWeekNum As
datepart('ww',Now())
Set CurrentMonth As
STRTOMEMBER("[Date].[Calendar].[Month].&["+CStr(CurrentYearNum)+"]&["+CStr(CurrentMonthNum)+"]")
Set Lag13FromCurrentMonth As
{
STRTOMEMBER("[Date].[Calendar].[Month].&["+CStr(CurrentYearNum)+"]&["+CStr(CurrentMonthNum)+"]").Lag(12)
:
STRTOMEMBER("[Date].[Calendar].[Month].&["+CStr(CurrentYearNum)+"]&["+CStr(CurrentMonthNum)+"]")
}
Member SalesForCurrentMonth As
[Measures].[Internet Sales Amount]
Member SalesForLast13Months As
SUM(
{
STRTOMEMBER("[Date].[Calendar].[Month].&["+CStr(CurrentYearNum)+"]&["+CStr(CurrentMonthNum)+"]").Lag(12)
:
STRTOMEMBER("[Date].[Calendar].[Month].&["+CStr(CurrentYearNum)+"]&["+CStr(CurrentMonthNum)+"]")
},
[Measures].[Internet Sales Amount]
)
SELECT {CurrentMonthNum,CurrentYearNum,CurrentWeekNum,SalesForCurrentMonth,SalesForLast13Months} ON 0,
CurrentMonth on 1 from
[Adventure Works]
/*
Similarly Create a dynamic set for week
1. In Adventure works, week is not part of the Calendar Hierarchy. To make it work for Weeks have a
hierarchy like Year-Quarter-Month-Week-Date
2. Once you have a hierarchy in place, create a dynamic set like how i have created for month. To form the right
format drop any member from the week level and dynamically form as required by it. Example: In my case
i had to form it as [Date].[Calendar].[Month].&[2001]&[8]
3. I have used Lag. You can also use LastPeriod. Having the set is the Key. The dynamically created set has to be
directly used in the SUM function. Creating a seperate set and then using that set in SUM will not work.
4. If your week member is different form, like week name then you may have to google for the appropriate datepart expression
*/
September 16, 2011 at 7:04 am
I have requirement we wants to show in cube browser window is yesterday sales data, , I have used time dimension table hierarchy is Year, month , week start date , day and hour
I have used mdx query for the same
{[Time Period].[Time Period].[Day].members.item([Time Period].[Time Period].[Day].members.count-1)}
But its returning only list of default measure, I need newly created measure callculation also should be show
Please help on this senario
Advance thanks
Kanagarajan.S
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply