February 21, 2011 at 2:39 am
Hey all,
My problem:
I'm trying to create a scope function that calculates exchange rates based on a date and currency AND a specified rate.
I have this working fine, but within my scope function I want to say - if Dimension.Attribute.member = "Latest" then use FXRate 1 otherwise use FXRate 2.
Now I even have that "working"... BUT that only works if the member is in my dataset... i.e. I can't use it as a parameter\filter.
I don't want to have to tell the users "you always have to have LatestFlag in every report... just hide the column"
I want to give the user the ability to set the report parameters before he starts analysing the data.
So here's a snippet of my code so far:
Scope ( { Measures.[Amount]} );
Scope( Leaves([ExchangeDate]), [Reporting Currency].[USD],Leaves([Currency]));
Scope( { Measures.[Amount]});
This = iif(
[Latest Flag].[Flag].CURRENTMEMBER.name = "Yes",
[Reporting Currency].[Local] / Measures.[Rate2],
[Reporting Currency].[Local] / Measures.[Rate]
);
End Scope;
End Scope;
End Scope;
I suspect I need to use another Scope instead of the iif - but I'm not sure how to implement.
Any ideas?
Oh it's probably important to note.
The FXRate table has two rates.
Rate is updated daily.
Rate2 is repeated for every currency everyday.
So irrevelant of the date, Rate2 will always be the latest rate for that currency.
The LatestFlag dimension is merely a table with yes and no and doens't have any relationship to any other table.
I'm just using it as a filter.
There is a flag on the FX table too - but I'm not using this as I need the date to be considered if it's not the latest rate.
February 21, 2011 at 3:11 am
I had a similar issue, though my MDX is somewhat basic I found a solution, however in instead of using a Current, we had to use different type of rates (Average Month End, Annual, Daily, etc), against different measures.
With a Scope statement you migh find this works
Scope ({[Latest Flag].[Flag].[Yes]});
This = [Reporting Currency].[Local] / Measures.[Rate2];
End Scope;
Scope ({[Latest Flag].[Flag].[No]});
This = [Reporting Currency].[Local] / Measures.[Rate];
End Scope
You may have to play with the code to get it to work, also adding a check for 0 rate values on the calculation line is befecial to remove thos annoying #Div/0 or #1NF errors.
As a general point Im not sure you need the Second Scoped Measure in the Scope Header, especially with just a single measure.
The only time you should need the second statement is if you want to apply different rates to different measures, even then its dropped into the Inner scope such as
Scope ( { Measures.[Amount], Measures.[Amount2],Measures.[Amount3]} ); //Measure Scope
Scope( Leaves([ExchangeDate]), [Reporting Currency].[USD],Leaves([Currency])); //Rate calendar Scope
Scope ({[Latest Flag].[Flag].[Yes]}); //Latest Flag Yes Scope.
Scope ( { Measures.[Amount]);
This = [Reporting Currency].[Local] / Measures.[Rate2];
End Scope;//Measure end scope
End Scope;// Flag 'Yes' End scope
Scope ({[Latest Flag].[Flag].[No]}); //Latest Flag No Scope
Scope ( { Measures.[Amount2],Measures.[Amount3]);
This = [Reporting Currency].[Local] / Measures.[Rate];
End Scope; //Measure end scope
End Scope; // Flag 'No' End scope
End Scope; //Rate calendar Scope end
End Scope; //First Scope End(Out measures)
HTH, and gives you a couple of ideas.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 21, 2011 at 4:33 am
Hey Jason!
Thanks alot for taking the time to respond.
Much appreciated.
Your post makes complete sense - and I have in actual fact tried that before.
So:
Scope ( { Measures.[Amount]} );
Scope( Leaves([ExchangeDateEntered]), [Reporting Currency].[USD],Leaves([Currency]));
// This = iif(
// [Latest Flag].[Flag].CURRENTMEMBER.name = "Yes",
// [Reporting Currency].[Local] / Measures.[Rate2],
// [Reporting Currency].[Local] / Measures.[Rate]
// );
Scope ({[Latest Flag].[Flag].[Yes]});
This = [Reporting Currency].[Local] / Measures.[Rate2];
End Scope;
Scope ({[Latest Flag].[Flag].[No]});
This = [Reporting Currency].[Local] / Measures.[Rate];
End Scope;
End Scope;
But unfortunately it does the same as the iif...
If you look at the screen shot that is how I get it working.
So having the values in the dataset works - but if I delete the value from the dataset and put it in the parameter list it doesn't change.
I'm not sure if it has something to do with the fact that Latest Flag isn't related at all in the dimension usage...
February 21, 2011 at 4:59 am
This also does the same:
Scope ( { Measures.[Amount]} );
Scope( Leaves([ExchangeDateEntered]),[Reporting Currency].[USD],Leaves([Currency]),[Latest Flag].[Yes]);
This = [Reporting Currency].[Local] / Measures.[Rate2];
End Scope;
Scope( Leaves([ExchangeDateEntered]),[Reporting Currency].[USD],Leaves([Currency]),[Latest Flag].[No] );
This = [Reporting Currency].[Local] / Measures.[Rate];
End Scope;
End Scope;
February 21, 2011 at 5:46 am
Tried this too:
Scope ( { Measures.[Amount]} );
Scope( Leaves([ExchangeDateEntered]), [Reporting Currency].[USD],Leaves([Currency]));
Scope ({[Latest Flag].[Flag].[Yes]});
Scope( { Measures.[Amount]});
This = [Reporting Currency].[Local] / Measures.[Rate2];
End Scope;
End Scope;
Scope ({[Latest Flag].[Flag].[No]});
Scope( { Measures.[Amount]});
This = [Reporting Currency].[Local] / Measures.[Rate];
End Scope;
End Scope;
End Scope;
I know I don't actually need the inner amount scope; Scope( { Measures.[Amount]});
I'm just clutching at staws here 🙂
February 21, 2011 at 6:05 am
Okay found the answer from a post on
http://social.msdn.microsoft.com
I never set the default member and I never set the isaggregate to false on the LatestFlag dimension...
So any of the scope will work above - but you have to set the above properties too.
Thanks again
February 21, 2011 at 8:23 am
_Tups_ (2/21/2011)
Okay found the answer from a post onhttp://social.msdn.microsoft.com
I never set the default member and I never set the isaggregate to false on the LatestFlag dimension...
So any of the scope will work above - but you have to set the above properties too.
Thanks again
Hi tups,
Sorry been in a meeting most of the day, I was just about to ask how the dimension was set up. I've done this on several projects I always seem to forget the isAggregatable to False and the default member.
You may want to consider adding a third option to the LatestFlag dimension called 'Base' this will allow you to manually validate the calculations (to some extent).
Just wait until you have to deal with rates at different grains, now thats fun. :crazy:
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply