May 20, 2014 at 4:34 am
Hi,
I am trying to write this MDX query. I am trying to select the Number of planned jobs (open, closed) by the customer name Acme.
If I do this in the WHERE clause I get the correct figures for Acme. However if I use EXISTS it seems to ignore the customer filter 'Acme' and gets the figures for all customers.
The thing is I don't want to do it in the WHERE clause as eventually I will be adding a named set into the filter for DatePeriod and I cannot add it in the WHERE clause.
Has anyone got any ideas please?
WITH
//--params
//--test
SET [VarPeriod] AS PeriodsToDate([KPI Planned Date].[Calendar].[Calendar Year],
STRTOMEMBER("[KPI Planned Date].[Calendar].[Calendar Month].&["+Format(now(), "yyyyMM")+"]"))
SET [VarCustomer] AS [Customer].[Customer Ref].&[Acme]
//
SELECT
[Measures].[Number of Planned Jobs] on Columns
,
//non empty
//(
EXISTS
([Job Status].[Job Status Type].[Job Status Type].Members
,[Customer].[Customer Ref].&[Acme])
//
//)
--[Job Status].[Job Status Type].[Job Status Type]
on Rows
from [TestCube]
--where
--(
--[Customer].[Customer Ref].&[Acme]
--)
May 20, 2014 at 7:20 am
You should not need the exists at all. In fact remove just that word and leave the rest and see if it gives what you want (you may need a NON EMPTY before depending on what you want).
When you use exists it makes sure that it only shows members from the first set (Job Status) that exist with the second set (ACME). But it is doing that independent of your measure, and then query the resulting job status set against that measure.
You don't want that.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply