April 17, 2012 at 12:19 am
That is great suggestion Chris. Would actually need to try that out but am quite confident that it will work well.
One last thing, if you have a query with User and the calculated measure [Measures].[Remaining Hours as Per Agreement], it should be able to work fine consider the Scope assignments, ie return calculation [measures].[Support Hours Bought] - [measures].[Duration in Hours]?
Cheers,
Jon
April 17, 2012 at 6:40 am
Jon,
Not sure that I understand your last question. Are you asking if the scope statement(s) will work if other dimensions are in play? If so, then the answer is "absolutely!". When querying a cube, you must supply one coordinate from each hierarchy that forms the space being queried. If the query does not specify a coordinate from a hierarchy, one must be assumed. Question for you: What is that coordinate (generally speaking)?
Chris
Chris Umbaugh
Data Warehouse / Business Intelligence Consultant
twitter @ToledoSQL
April 17, 2012 at 7:06 am
Chris,
Yes that is exactly what I meant! Yes so if another dimension comes into play the default member will be applied to the mdx query behind the scenes, and if there are no defualt values, first value would be used if am correct.
This might be slightly out of topic, but sometimes you might need to specify the EXISTING keyword for a dimension (to map each calculated column to the dimension selected). This is easily done in MDX script but is there any equivalent functionality when creating a query from the cube browser?
Cheers mate,
Jon
April 17, 2012 at 7:20 am
Great! I wanted to see where you were at in your understanding of the lattice. Looks like you've got it.
I'm do not understand what you are attempting with the EXISTING question. Can you give an example?
Chris Umbaugh
Data Warehouse / Business Intelligence Consultant
twitter @ToledoSQL
April 17, 2012 at 7:48 am
Well by definition, the EXISTING keyword is used when you need a set to be constrained by the expression’s context. I've been struck by lightning, answering the question myself, you would need to add EXISTING to the calculated measure as per below:
WITH
MEMBER [Measures].[Number of Products] AS
Count(
EXISTING [Product].[Product].[Product].Members
)
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Number of Products])
} ON COLUMNS,
{[Product].[Category].Members} ON ROWS
FROM [Step-by-Step]
Oh well 😉
Much appreciate your help Chris..
June 14, 2018 at 12:50 pm
I have the same issue. Can you guys help? Grand total not accounting for Practice Group ID = MSP
IIF(([Practice Group].[Practice Group ID].CURRENTMEMBER.MEMBER_KEY="MSP" OR [Practice Group].[Practice Group].CURRENTMEMBER.MEMBER_KEY="Managed Service Program") AND [Calendar].[Year].CURRENTMEMBER.MEMBER_KEY>=2018,[MSP Subscription Margin],[Measures].[Revenue] -[MSP Agreement Revenue] - [Measures].[Pay Amount Adjusted] - [Measures].[Utilization Bonus])
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply