March 11, 2015 at 8:50 am
Hi,
I have encountered a bit of a problem.
I have written a report for a customer which has 2 calendar controls - Start Date and End Date
There is a requirement to then pull back a list of Comparative dates using those date ranges the user selects
In the background I convert those list of dates to a string using a Mult Valued parameter
Now in my MDX I am aiming to convert this to a SET -( I have changed the names of things to protect their data)
I am finding if I don't include the WHERE slicer my Comparative SET works fine.
However if I do include the WHERE slicer my Comparative SET brings back Nulls
I need to be able to pass the normal date (what the user selects in the WHERE slicer) and also to be able to use the
Comparative dates in certain measures
Interestingly the Comparative Dates work if I use a single MEMBER, then I see both the normal dates data and the Compare dates data, but I need to do this SET now.
Has anyone any ideas?
WITH
SET [CompareDates]
AS
--@CompareDatesUniqueMember
{
[Reporting Date].[Calendar Hierarchy].[Date].&[20140223]
,[Reporting Date].[Calendar Hierarchy].[Date].&[20140224]
,[Reporting Date].[Calendar Hierarchy].[Date].&[20140225]
,[Reporting Date].[Calendar Hierarchy].[Date].&[20140226]
,[Reporting Date].[Calendar Hierarchy].[Date].&[20140227]
,[Reporting Date].[Calendar Hierarchy].[Date].&[20140228]
,[Reporting Date].[Calendar Hierarchy].[Date].&[20140301]
}
MEMBER [Measures]. [Test_CP]
AS
AGGREGATE({[CompareDates]}
,[Measures].[Test Count]
)
SELECT
{
[Measures]. [Test_CP]
}
ON COLUMNS
,[Test].[Test].[Test Category]
ON ROWS
FROM [Test Cube]
--)
WHERE
(
[Date].&[20150222]: [Date].&[20150228]
)
March 18, 2015 at 4:17 pm
Duplicate thread...see responses here: http://www.sqlservercentral.com/Forums/Topic1667791-17-1.aspx
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply