Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Convert a sales value to the reference currency with the exchange rate of a specific date Expand / Collapse
Author
Message
Posted Friday, March 28, 2014 2:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 12:07 PM
Points: 120, Visits: 489
I Have 2 Dimensions: Time and Currency

I also have to fact tables, exchange rate and sales

Both fact tables have references to Time and Currency Dimension.

The user want to select using excel the sales in the local currency and the correspondent conversion in the reference currency (the reference currency is fixed) in the selected date.

To obtain the desired exchange date I need a date and a currency. The user select in excel a specific date but the currency must be the current currency in the sales table. I achived something like this in the query editor:

with

MEMBER [Measures].[Current Rate To Euro] AS (
([ISO Currencies].[ISO Curr Code].CurrentMember, [Time].[Date Id].CurrentMember, [Measures].[Exchange Rate To Euro]))

MEMBER [Measures].[Net Sales Euro] AS (
[Measures].[OOH Net Sales Dom Snapshot]/[Measures].[Current Rate To Euro]
)

SET NE_measures as {[measures].[Net Sales Value Domestic], [Measures].[Exchange Rate To Euro]}

member m_NonEmptyCheck as
Generate
(
NE_measures
,{Iif([Measures].CurrentMember, [Measures].CurrentMember, NULL)}
).Count = NE_measures.Count
,NON_EMPTY_BEHAVIOR =
(
{[measures].[Net Sales Value Domestic], [Measures].[Exchange Rate To Euro]}
)

select {[Measures].[Net Sales Euro], [Measures].[OOH Net Sales Dom Snapshot],[Measures].[Current Rate To Euro]} on 0,
Filter(
{[ISO Currencies].[ISO Curr Code].[ISO Curr Code]}*{[Time].[Date Id].&[2581]*[Customers].[PDU Description].[PDU Description]}
,m_NonEmptyCheck
) on 1
from [DM ISR DEV];


But I don't know how to translate the last select in a calculated measures in order to let the customer only to select this measures in the values area and the time in the filters.

Any comment would be appreciated

Kind Regards


Paul Hernández
http://hernandezpaul.wordpress.com/
https://twitter.com/paul_eng
Post #1555796
Posted Friday, March 28, 2014 3:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 12:07 PM
Points: 120, Visits: 489
Was not so difficult as I thought, however, it took me several hours to understand how could it work:

with

MEMBER [Measures].[Current Rate To Euro] AS (
([ISO Currencies].[ISO Curr Code].CurrentMember,[Time].[Date Id].CurrentMember, [Measures].[Exchange Rate To Euro]))

MEMBER [Measures].[Net Sales Euro] AS (
SUM([ISO Currencies].[ISO Curr Code].[ISO Curr Code],([Measures].[OOH Net Sales Dom Snapshot]/[Measures].[Current Rate To Euro]))
)

select {[Measures].[Net Sales Euro], [Measures].[OOH Net Sales Dom Snapshot],[Measures].[Current Rate To Euro]} on 0,
non empty {[Time].[Date Id].&[2580]} -- specific datatime, in my db is the 23 Jan. 2014
*{[Customers].[PDU Description].[PDU Description]} on 1 -- An attribute of the customer hier.
from [DM ISR DEV]
;


I think all of my confusions come from my Sql background. A calculated member is a member that is resolved by calculating an MDX expression to return a value, not a set, for this reason the key to solve my problem was the SUM function.


Paul Hernández
http://hernandezpaul.wordpress.com/
https://twitter.com/paul_eng
Post #1555813
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse