October 10, 2019 at 11:13 pm
I have a fact table (document granularity) with 3 amount measures and other qtys measures, those 3 amount measures are not in same currency but related to a type of a visualization.
Reports can have three types of visualization, Document view, Company view, Company Group view.
If you see through document view you should view all kinds of currencies, depending of the document (GBP, EUR, CAN, USA, AUD, etc).
If you see through company view you should see this regarding the currency associated to the company (i.e. Company 1 is EUR, Company 2 is USD, etc).
Company Group is always EUR.
Example:
fact table
+---------------------------------------------------------------------------+
¦ Fact Document ¦
¦---------------------------------------------------------------------------¦
¦ Company ID ¦ Doc ID ¦ Mtr Document ¦ Mtr Company ¦ Mtr Company Group ¦
¦------------+-------------+--------------+-------------+-------------------¦
¦ 1 ¦ 101 ¦ 100 ¦ 90 ¦ 95 ¦
¦------------+-------------+--------------+-------------+-------------------¦
¦ 2 ¦ 102 ¦ 250 ¦ 150 ¦ 120 ¦
¦------------+-------------+--------------+-------------+-------------------¦
¦ 3 ¦ 103 ¦ 400 ¦ 200 ¦ 170 ¦
+---------------------------------------------------------------------------+
dim document
+------------------------------------------------------------------------+
¦ Dim Document ¦
¦------------------------------------------------------------------------¦
¦ Doc ID ¦ Document Currency ¦ Company Currency ¦ Company Group Currency ¦
¦--------+-------------------+------------------+------------------------¦
¦ 101 ¦ USD ¦ GBP ¦ EUR ¦
¦--------+-------------------+------------------+------------------------¦
¦ 102 ¦ CAN ¦ USD ¦ EUR ¦
¦--------+-------------------+------------------+------------------------¦
¦ 103 ¦ AUD ¦ USD ¦ EUR ¦
+------------------------------------------------------------------------+
dim visualization type (Manual and Static dimension with no change)
+-------------------------------------------------+
¦ Visualization type ID ¦ Visualization type Name ¦
¦-----------------------+-------------------------¦
¦ 1 ¦ Document view ¦
¦ 2 ¦ Company view ¦
¦ 3 ¦ Company Group view ¦
+-------------------------------------------------+
dim currency
+----------+
¦ Currency ¦
¦----------¦
¦ EUR ¦
¦ USD ¦
¦ GBP ¦
¦ CAN ¦
+----------+
I want to choose visualization type and dynamically select the correct measure (what you have done).
But I also want to select dynamically the currency associated to that measure. So I want to link one dimension to the other so I can have only a currency attribute and I don't want to have 3 different currency attributes.
Desirable output:
Document view selected
+-----------------------------+
¦ Doc Id ¦ Amount ¦ Currency ¦
¦---------+--------+----------¦
¦ 101 ¦ 100 ¦ USD ¦
¦ 102 ¦ 250 ¦ CAN ¦
¦ 103 ¦ 450 ¦ AUD ¦
+-----------------------------+
Company view selected
+-----------------------------+
¦ Doc Id ¦ Amount ¦ Currency ¦
¦---------+--------+----------¦
¦ 101 ¦ 90 ¦ GPB ¦
¦ 102 ¦ 150 ¦ USD ¦
¦ 103 ¦ 200 ¦ USD ¦
+-----------------------------+
Company Group view selected
+-----------------------------+
¦ Doc Id ¦ Amount ¦ Currency ¦
¦---------+--------+----------¦
¦ 101 ¦ 95 ¦ EUR ¦
¦ 102 ¦ 120 ¦ EUR ¦
¦ 103 ¦ 170 ¦ EUR ¦
+-----------------------------+
Is it possible to do this?
All the best and thank you!
Ricardo Castro
UPDATE
I did this below and I am getting the values 1, 2 or 3 as I select the type in "dim visualization type"
CREATE MEMBER [Coin]
AS CASE WHEN [visualization type].[visualization type].CurrentMember.MEMBERVALUE = 'Document view'
THEN 1
ELSE (CASE WHEN [visualization type].[visualization type].CurrentMember.MEMBERVALUE = 'Company view'
THEN 2
ELSE 3 END) END,
VISIBLE = 1 ;
I need to get the attribute values. I have tried:
[Document].[Document Currency].firstchild
STRTOMEMBER("[Document].[Document Currency].MEMBERS")
STRTOMEMBER('[Document].[Document Currency].[Document Currency].FirstChild', CONSTRAINED)If I put the follow code I get the "all" that is the only one I don't want 😛 :
CREATE MEMBER [Coin]
AS CASE WHEN [visualization type].[visualization type].CurrentMember.MEMBERVALUE = 'Document view'
THEN [Document].[Document Currency].CurrentMember.Properties("Name")
ELSE (CASE WHEN [visualization type].[visualization type].CurrentMember.MEMBERVALUE = 'Company view'
THEN [Document].[Company Currency].CurrentMember.Properties("Name")
ELSE [Document].[Company Group Currency].CurrentMember.Properties("Name") END) END,
VISIBLE = 1 ;
Any thoughts?
October 12, 2019 at 12:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply