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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy