Solved

Showing last three years dynamically with lastperiods

Posted on 2014-02-12
3
75 Views
Last Modified: 2016-02-18
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

0
Comment
Question by:gosi75
  • 2
3 Comments
 
LVL 20

Accepted Solution

by:
dsacker earned 500 total points
ID: 40182787
If you intentionally tagged your question with "MS SQL Server 2008," HIERARCHIZE did not exist then.
0
 

Author Comment

by:gosi75
ID: 40182797
Thanks for the reply, this was intentionally done so. How would I be able to rewrite my query instead of using HIERARCHIZE ?
0
 
LVL 20

Expert Comment

by:dsacker
ID: 40182883
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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now