I'm trying to create a dynamic date prompt in a report that uses an OLAP source. I am using a value prompt that pulls from the Month level of my date dimension. Unfortunately, by doing this, the prompt returns every month value in the date dimension, from Jan 2002 all the way to Dec 2010.
I would like to be able to dynamically control the choices in the prompt. For instance, I want it to return months 1 year prior to this month and 6 months hence. I found that I can filter the prompt query with a Between ... And statement while using the MUNS as values. Now, I want to be able to change the MUNS dynamically so it always looks at today's date. I figure, the best way for me to do this is by breaking up the MUN with an expression. I just don't quite know how to do that correctly.
For example, I would like to be able to dynamically change the month portion of the MUN below: Great_Outdoors_Company].[Years].[Years].[Year]->:[PC].[Years (Root)].[20040101-20041231]
This way I can go ahead and create a filter that goes something like:
[Month]
between
[Great_Outdoors_Company].[Years].[Years].[Year]->:[PC].[Years (Root)].[2004XX01-2004XX31]
and
[Great_Outdoors_Company].[Years].[Years].[Year]->:[PC].[Years (Root)].[2004YY01-2004YY31]
where XX = 12 months prior to today and YY = 6 months hence.
How do I insert those variables in the MUN? Or is there approach to this problem?
Thanks!
I feel like you are trying to build reports for an OLAP source using Relational methodology.
Instead of trying to access the MUN directly, use the OLAP functionality to restrict the available members. Disengaging my relational thought patterns from OLAP functionality was the biggest difficulty I had in learning the MDX language, (and therefore the usage of the OLAP functions)
I want to create a sample report, but the sample data doesn't lend itself to this very nicely since you want to provide a future set of members as well as a historical set.
I believe however, that trying to filter the text of the MUN isn't the right approach. You probably want to use something like the Tail function (i.e. tail(great_outdoors_company].[Years].[Years].[Quarter],6) will give you the last 6 quarters from the Quarter Level of the Date Heirarchy)
Can you provide me more insight as to why you want to include forward looking months? Also what is the end goal of the report?
MDXpressor,
Thanks for the response. The reason for doing this is to restrict the users' choice. We only want the user to be able to run reports for dates as far as 1 year prior and 6 months hence. We could probably apply this rule in the model itself, but that's just not an option right now.
I'll try using the tail expression and let you know if I have any success.
Thanks again.