Showing last three years dynamically with lastperiods

I'm trying to get the last three years to show in my report, but when I try to use the .currentmember I get the error:"The CURRENTMEMBER function expects a hierarchy expression for the 1 argument. A member expression was used."

Here is my query where I hard code the year and it works. but I would like to have the lastperiod part something like:
LASTPERIODS(3, [Date].[Calendar].[Calendar Year].currentmember but that gives me the above error.

Any idea why this does not work?

SELECT
HIERARCHIZE( { LASTPERIODS(3, [Date].[Calendar].[Calendar Year].&[2010]) } ) * { [Measures].[Internet Sales Amount] }

ON COLUMNS,
[Product].[Category].[Category]
ON ROWS
FROM [Adventure Works]

Open in new window

gosi75Business Intelligence ConsultantAsked:
Who is Participating?
 
dsackerContract ERP Admin/ConsultantCommented:
If you intentionally tagged your question with "MS SQL Server 2008," HIERARCHIZE did not exist then.
0
 
gosi75Business Intelligence ConsultantAuthor Commented:
Thanks for the reply, this was intentionally done so. How would I be able to rewrite my query instead of using HIERARCHIZE ?
0
 
dsackerContract ERP Admin/ConsultantCommented:
Could you post a small sampling of the expected rows. Thanks. Also, we don't have cubes (per se) here, so aren't using SSAS. If I can help from a SSMS query point-of-view, I'm glad to. If someone else chimes in with an SSAS solution, please award them.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.