If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Modify MUNs for dynamic date prompting

Started by acabugason, 07 Sep 2007 11:08:55 AM

Previous topic - Next topic

acabugason

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!

MDXpressor

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?
No, a proof is a proof. What kind of a proof? It's a proof. A proof is a proof, and when you have a good proof, it's because it's proven.

-Jean Chretien

acabugason

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.