Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here




hello all,

I want to do a report where i want to show the sales made during the last 3 years given a particular month and year. this will month - year will change dynamically. presently if i have to do that report, i create separate metrics for each year month (say user wants compare last 2 years i create three metrics aug2000, aug2001, aug2002) combination. i was told that once can use transformation for such a report but didn't have much success with that. i also checked up vmall project but couldn't understand much about transformation. can anyone give some idea on how to accomplish this without creating sales amount metric for each year/month.

thanks for your replies


RE: Transformation!

- Create a Transformation Table (this_month_year,Last_month_year)
- Then, Create "Transformation" you need.

RE: Transformation!

thanks for your immediate reply... going thru your post i feel one should create a separate table for the transformation... can you give a more detailed explanation for the above process given my month and year field name are month_of_year and year respectively... i need to compare the last 2 years metric value.... in that case should i create one more field in that table....

thanks again


RE: Transformation!

Yes, you must create two tables:
- Last_Year(month_this_year,month_last_year)
_ Last_Year_2(month_this_year,month_2_year_ago)

Table One:

Table Two:

These tables let you create two Transformation.

Then, insert the transformation in the report you need.

Is that you need?

RE: Transformation!

You could also create a transformation without creating a table. All you have to do is calculate how many months each transformation would be. For example to do Last Year it would be "Year - 1". For doing the same thing Year,Month (200201,200202,200203 etc.) You can do the following Year,Month - 100. For example 200201 gives you 200101.

RE: Transformation!


thanks very much! yes i would like to accomplish that without creating a separate table.... my month and year are separate attributes and how do i do the transformation for a metric... i have a metric in which i have a prompt to select the year and month.... does the transformed metric takes these value and does the transformation or we have to supply the year and month for that metric as well.... moreover is it possible to change the metric name dynamically to the month-year combination for which it does the transformation...

thanks again


RE: Transformation!

Having a prompt in the metric should work fine. There is no way that I know of to change the name of the metric dynamically without writing some custom code against the SDK. I would also try creating an attribute that is bot Year and Month together. This will make it easier to get the values you want, otherwise if someone just picks the month you wont know what year that was for.

RE: Transformation!



many a thanks! i was able to do the transformation without creating a separate table.... 'bout the prompt i placed it at the report level and it works fine.... my only problem now is how to change the metric name dynamically to the year it has done the transformation coz displaying Last Year is confusing or one should change the current year-month metric name to reflect the current period so that users can relate what Last Year means...... what do u say????? in case u find out some way around plz do share with us....


RE: Transformation!

What i have found helpful is to include the time attribute that I am filtering on so that it is a little more obvious. For example I include date in the page by and when users get a report that says MTD Shipped and LY MTD Shipped they know what time it is referencing. I though of one way to dynamically name metrics but it's not the best solution. You need to create a custom group with elements that are different time periods and make the metric really simple like shipped sales. What happens is Microstrategy applies the name of the element to the metric when you run the report. The only problem is that it is either all of nothing with the elements.

RE: Transformation!

dynamic metric naming is not really available, unfortunately....you'll have to make do with something like "last year", "last 2 years"...and also follow reisw's suggestion of adding the time attribute on the report to show which is the selected year.

RE: Transformation!

Hi Sridharan,
If you are displaying the report in a document, you could try to create such "dynamic metric name" by using xsl.
any doubt cogubr@hotmail.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close