April 3, 2012 at 12:43 pm
I'm trying to remove the % sign that comes back from my cube. I was going to use a combination of LEN & RIGHT to do this, but I can't seem to get the syntax.
WITH MEMBER Measures.ActualSiteKey
AS [Dim Person Position Summary].[Person Position Hierarchy].CurrentMember.Properties("Key")
SELECT {Measures.ActualSiteKey, [Measures].[FTE Variance %],(KPIValue("KPI FTE"), KPIGoal("KPI FTE"), KPIStatus("KPI FTE")} ON Columns
, [Dim Person Position Summary].[Person Position Hierarchy].[Site Key] ON Rows
FROM [FTEOLAP]
Where [Pay Cycle End Date].[Date].[Calendar Year].&[2012]
When I run the above, the FTE Variance literally has a % sign in it. I'm moving it from a staging table to a prod table, so I could clean it up after-the-fact, but I'm wondering if there is even such a notion as the typical String functions in MDX. I tried:
RIGHT([Measures].[FTE Variance %],2)
but it comes back:
Query (4, 160) Parser: The syntax for '}' is incorrect.
April 3, 2012 at 1:05 pm
I got it to work. The formatting is added by the cube, so we just needed to strip the formatting back out.
WITH MEMBER Measures.ActualSiteKey
AS [Dim Person Position Summary].[Person Position Hierarchy].CurrentMember.Properties("Key")
SELECT {Measures.ActualSiteKey, [Measures].[FTE Variance %]
, KPIGoal("KPI FTE"), KPIStatus("KPI FTE")} ON Columns
, [Dim Person Position Summary].[Person Position Hierarchy].[Site Key] ON Rows
FROM [FTEOLAP]
Where [Pay Cycle End Date].[Date].[Calendar Year].&[2012]
CELL PROPERTIES VALUE, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
April 4, 2012 at 5:12 am
Thanks for posting back the solution.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply