November 5, 2014 at 4:00 am
Hi
i have one ssrs report from which i have to calculate YTd
i wrote below mdx expression but iam getting upto september only
how to get upto october data
and if it is december i have to calculate upto November
how to calculate this dynamically in mdx
MEMBER [Measures].[YTD] AS (SUM([Date].[Month].&[4]:[Date].[Month].&[DatePart("m",Now())],[Measures].[Amount]))
can any one suggest me how to do this...
November 5, 2014 at 5:18 am
The following query against AdventureWorks show two methods to get a YTD figure. Using the actual YTD function means having your date dimension and attributes set up with correct types.
WITH MEMBER [YTD1]
AS
SUM(YTD
([Date].[Calendar].CURRENTMEMBER),
[Measures].[Internet Order Count])
MEMBER [YTD2]
AS
SUM(
PERIODSTODATE
([Date].[Calendar].[Calendar Year],
[Date].[Calendar].CURRENTMEMBER),
[Measures].[Internet Order Count])
SELECT
{[Measures].[Internet Order Count], [YTD1], [YTD2]} ON 0
,
[Date].[Calendar].[Month] ON 1
FROM
[Adventure Works]
WHERE
[Date].[Calendar Year].&[2007]
November 6, 2014 at 2:33 am
Sorry i didn't got
i have to get get YTD to October for the financial Year
if current month is november i have to calculate upto October
if current month is december i have to calculate upto november
i want to get this data dynamically
can u pls suggest me how to do this...
November 6, 2014 at 2:47 am
I already have done - without knowing your setup I have just given you example code which you can use to apply the same logic to your own cube. Let me walk you through the code......
WITH MEMBER [YTD1]
AS
SUM(YTD
// Here we use the contextual member of a calendar hierarchy for the calculation (which as you'll see from the SELECT statement is month.
([Date].[Calendar].CURRENTMEMBER),
[Measures].[Internet Order Count]) -- This is what we want to calculate the year to date for
MEMBER [YTD2]
AS
SUM(
PERIODSTODATE
([Date].[Calendar].[Calendar Year], //This is the level unit we want to keep within for aggregating periods to date
[Date].[Calendar].CURRENTMEMBER), //These are the units we want to count by (again currentmember is contextual)
[Measures].[Internet Order Count])
//This does the same thing as the YTD1 calculation but isn't reliant on a correctly set up Date dimension.
SELECT
{[Measures].[Internet Order Count], [YTD1], [YTD2]} ON 0
,
// Because we've used currentmember in the above calculations then they will use this selection to aggregate the YTD figure
[Date].[Calendar].[Month] ON 1
FROM
[Adventure Works]
WHERE
//Here we're specifying a year - this could be a parameter to make a report "dynamic"
[Date].[Calendar Year].&[2007]
I hope that helps.
November 6, 2014 at 4:01 am
i wrote below expression but iam getting null values
MEMBER
[Measures].[YTD] AS
SUM(
PERIODSTODATE
([Date].[Month Name].&[April],[Date].[Month Name].CURRENTMEMBER),[Measures].[Amount])
can u pls rectify this one
so that i can achieve my target
November 6, 2014 at 4:18 am
Mkrish (11/6/2014)
i wrote below expression but iam getting null valuesMEMBER
[Measures].[YTD] AS
SUM(
PERIODSTODATE
([Date].[Month Name].&[April],[Date].[Month Name].CURRENTMEMBER),[Measures].[Amount])
can u pls rectify this one
so that i can achieve my target
Firstly, is [Month Name] a hierarchy? Because if it's not then this won't work.
Follow this pattern:
SUM(
PERIODSTODATE
([Date Dimension Name].[Hierarchy Name].[Year Member], //This is the unit by which you want to aggregate - in this case Year
[Date Dimension Name].[Hierarchy Name].CURRENTMEMBER),
[Measure Name])
http://msdn.microsoft.com/en-us/library/ms144925.aspx
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply