Reputation: 341
A company I am working for requests a KPI concerning overtime hours booked. They want to measure if it's below their set threshold of 3%. There is a calculated member, named [Measures].[Percentage of Overtime Hours (%)], which can be used.
However, they want to limit the KPI to just the 2 departments they are available for.
I tried to limit the value by specifying this KPI Value expression:
([Measures].[Percentage of Overtime Hours (%)], {[Project].[Project Department].&[Department A], [Project].[Project Department].&[Department B]})
However, that gives me the following error message:
#Error The function expects a string or numeric expression for the argument. A tuple set expression was used.
On MSDN I could not find an answer to this. Google didn't yield me any decent results because I don't know what exactly to search for. Limiting a measure just gives me tons of articles about how the FILTER expression works, which seems not to be what I'm looking for.
How can I limit the KPI value to just the 2 departments?
I tried using SCOPE statement as suggested.
CREATE MEMBER CURRENTCUBE.[Measures].[Percentage of Overtime Hours (%) for AB] AS null;
SCOPE([Measures].[Percentage of Overtime Hours (%) for AB]);
This = NULL;
SCOPE([Project].[Project Department].&[Company A]);
This = ([Measures].[Percentage Overtime (%)], [Project].[Project Department].&[Company A]);
END SCOPE;
SCOPE([Project].[Project Department].&[Company B]);
This = ([Measures].[Percentage Overtime (%)], [Project].[Project Department].&[Company B]);
END SCOPE;
SCOPE([Project].[Project Department].[All]);
This = AGGREGATE({[Project].[Project Department].&[Company A], [Project].[Project Department].&[Company B]}, [Measures].[Percentage Overtime (%)]);
END SCOPE;
END SCOPE;
This however seems to push the same issue down one level deeper. It becomes clear when viewing [Measures].[Percentage of Overtime Hours (%) for AB] at the [Project].[Project Department].[All] level. It returns NULL. Any other suggestions?
Upvotes: 0
Views: 160
Reputation: 35605
You're getting the error because you're using a tuple (...)
and inside the tuple you have a single member, followed by a set:
( //<<braces indicate a tuple
[Measures].[Percentage of Overtime Hours (%)] //<<single member
,{
[Project].[Project Department].&[Department A] //<<two member set
,[Project].[Project Department].&[Department B]
}
)
If your adding this to your cube script
then can you do a scoped assignment?
I have very limited experience with these assignments but maybe something like the following:
CREATE MEMBER currentcube.[Measures].[Percentage of Overtime Hours (%) for AB]
AS null;
SCOPE( ({
[Project].[Project Department].&[Department A]
,[Project].[Project Department].&[Department B]
},
[Measures].[Percentage of Overtime Hours (%)]
));
this = [Measures].[Percentage of Overtime Hours (%)];
END SCOPE;
Upvotes: 0