November 16, 2011 at 10:43 am
I've got a rolling 3 month calculation set up in Analysis Services, and I want to report this total month by month in a report. Everything works fine in the cube browser and in Excel, and even in reporting services if I display all the dates from my cube. However, in the report I only want to show the 3 month totals for a 12 month period. For example, for the calendar year 2010 the value reported for January 2010 should also include November and December of 2009. I've set up a parameter in reporting services to filter the results from my cube to the 12 months I want, but when I do that my total for January 2010 only includes the January value. I'm pulling my hair out and can't seem to find anything about this anywhere online.
My 3 month total calculation looks like this:
Sum(
LastPeriods(3, [Date].[Calendar Month].CurrentMember),
Measures.[Fqhc Uds Encounters]
)
My MDX query for the report:
SELECT NON EMPTY {
[Measures].[Total Encounters],
[Measures].[Rolling 3 Month Encounters]
} ON COLUMNS,
NON EMPTY {
([Location].[Location Name].[Location Name].ALLMEMBERS * [Date].[Calendar Month].[Calendar Month].ALLMEMBERS )
}
DIMENSION PROPERTIES
MEMBER_CAPTION,
MEMBER_UNIQUE_NAME ON ROWS
FROM (
SELECT ( STRTOMEMBER(@FromDateDate, CONSTRAINED) : STRTOMEMBER(@ToDateDate, CONSTRAINED) )
ON COLUMNS
FROM (
SELECT ( STRTOSET(@LocationLocationName, CONSTRAINED) )
ON COLUMNS
FROM (
SELECT ( { [Encounter].[Fqhc Uds Flag].&[1] } )
ON COLUMNS FROM [CHAS])))
WHERE ( [Encounter].[Fqhc Uds Flag].&[1] )
CELL PROPERTIES
VALUE,
BACK_COLOR,
FORE_COLOR,
FORMATTED_VALUE,
FORMAT_STRING,
FONT_NAME,
FONT_SIZE,
FONT_FLAGS
November 16, 2011 at 2:22 pm
In your scenario what is the actual value that [Date].[Calendar Month].CurrentMember would return?
November 17, 2011 at 9:27 am
It's a text field formatted MonthName + Year, e.g. "November 2011"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply