CODEWITHSUNDEEP
ssasmdx
arunapavan
arunapavan

Reputation: 141

Expression for calculated member

How to get the data between two dates. I used the following expression:

(
   {[campaign].[campaign start date]:[campaign].[campaign end date]},
   [Measures].[sales]
 )

but it is giving null as result.

Upvotes: 0

Views: 880

Answers (1)

Magnus Smith
Magnus Smith

Reputation: 5963

It would help if you posted the full MDX you are using.

In the meantime, I'm guessing you need to put the time dimension into a filter (WHERE) and the measures onto rows or columns, with another dimension (just it's top-level member) present in order to return a single results cell:

SELECT {[Some other dimension].defaultMember} ON ROWS,
{[Measures].[sales]} ON COLUMNS
FROM [MyCube]
WHERE (AGGREGATE({[campaign].[campaign start date]:[campaign].[campaign end date]}))

Since the WHERE clause accepts only tuples, you have to use AGGREGATE to compress your date range from a set into a single item.

You can also try creating a calculated member and filter on that:

WITH MEMBER [Actual Time].[6th to 30th Nov 2006] 
AS 'Aggregate([Actual Time].[All Actual Time].[2006].[December].[6 December 2006] : [Actual Time].[All Actual Time].[2006].[November].[30 November 2006])', 
SOLVE_ORDER=0 
SELECT {whatever} ON ROWS,
{[Measures].[sales]} ON COLUMNS
FROM [MyCube]
WHERE ([Actual Time].[6th to 30th Nov 2006])

Upvotes: 1

Related Questions