Transformation!

[Sridharan]

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


sri

 

[Guest_imported]

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

 

[Sridharan]

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

sridharan

 

[Guest_imported]

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:
Month_this_year,month_last_year
jun2002,jun2001
feb2002,feb2001
...............

Table Two:
Month_this_year,month_2_year_ago
jun2002,jun2000
feb2002,feb2000
...............

These tables let you create two Transformation.

Then, insert the transformation in the report you need.

Is that you need?

 

[reisw]

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.

 

[Sridharan]

reisw,

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

sridharan

 

[reisw]

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.

 

[Sridharan]

reisw,

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....

sridharan

 

[reisw]

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.

 

[nlim]

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.

 

[fukudahs]

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