March 11, 2015 at 1:35 pm
Hi,
I realise my previous post may have been a bit hard to understand so I have re-created what I would like to do in the AdventureWorks database.
I am expecting to see values for both the measures as the Sales Summary perspective certainly has figures in there.
Why is it when I create a SET of dates and use that SET in the function I get no values returned, only if I omit the WHERE slicer and I need the WHERE slicer in as
I want to compare against 2 separate date ranges.
WITH
SET [CompareDates]
AS
{
[Date].[Calendar Date].[Date].&[20050701]
,[Date].[Calendar Date].[Date].&[20050702]
,[Date].[Calendar Date].[Date].&[20050703]
,[Date].[Calendar Date].[Date].&[20050704]
}
MEMBER
[Measures].[InternetSales_Compare]
AS
AGGREGATE([CompareDates], [Measures].[Internet Sales Count])
SELECT
{
[Measures].[Internet Sales Count]
,[Measures].[InternetSales_Compare]
}
ON COLUMNS
, [Promotion].[Promotions].[Promotion Category]
ON ROWS
FROM
[Sales Summary]
WHERE
(
[Date].[Calendar Date].[Date].&[20050801]:[Date].[Calendar Date].[Date].&[20050831]
)
March 11, 2015 at 4:31 pm
You are creating 2 mutually exclusive sets of dates here. The values of the calculated measures are determined after the data is filtered by the where-clause.
Think of it this way...first a sub-cube is created with only dates between 8/1 and 8/31 (as per your where-clause). Then your calculated measure/member code is executed, which creates a set with dates from month 7...but there is no data in the sub-cube for month 7.
Instead of using the where-clause, I'd recommend that you create another calculated set:
set CompareDates2
as
{
[Date].[Calendar Date].[Date].&[20050801]:[Date].[Calendar Date].[Date].&[20050831]
}
March 12, 2015 at 1:57 am
Thanks.
I did have this working but instead of using a SET I used separate members to do the dates like I did in the WHERE slicer
and this worked out the 2 separate dates perfectly.
However now the user wants a range of dates for the Comparison dates and it doesn't seem to like that now.
Your suggestion was one of the things I did try and because we have calculated measures in the cube which work out percentages I cannot simply wrap the dates round them.
Besides this bit of code uses about 100 measures.
I am trying this a different way of passing these dates through as a string and then doing a STRTOSET but I get an error with my syntax and don't know why it is erroring.
WITH
MEMBER [CompareDates]
AS
"
[Date].[Calendar Date].[Date].&[20050701]
,[Date].[Calendar Date].[Date].&[20050702]
,[Date].[Calendar Date].[Date].&[20050703]
,[Date].[Calendar Date].[Date].&[20050704]
"
MEMBER
[Measures].[InternetSales_Compare]
AS
AGGREGATE
(STRTOSET
([CompareDates]),[Measures].[Internet Sales Count])
March 12, 2015 at 9:41 am
LittleKitten (3/12/2015)
Thanks.I did have this working but instead of using a SET I used separate members to do the dates like I did in the WHERE slicer
and this worked out the 2 separate dates perfectly.
However now the user wants a range of dates for the Comparison dates and it doesn't seem to like that now.
Your suggestion was one of the things I did try and because we have calculated measures in the cube which work out percentages I cannot simply wrap the dates round them.
Besides this bit of code uses about 100 measures.
I am trying this a different way of passing these dates through as a string and then doing a STRTOSET but I get an error with my syntax and don't know why it is erroring.
WITH
MEMBER [CompareDates]
AS
"
[Date].[Calendar Date].[Date].&[20050701]
,[Date].[Calendar Date].[Date].&[20050702]
,[Date].[Calendar Date].[Date].&[20050703]
,[Date].[Calendar Date].[Date].&[20050704]
"
MEMBER
[Measures].[InternetSales_Compare]
AS
AGGREGATE
(STRTOSET
([CompareDates]),[Measures].[Internet Sales Count])
Your member assignment is not correct...see the syntax reference here: https://msdn.microsoft.com/en-us/library/ms144782.aspx
March 13, 2015 at 12:50 pm
I solved it in the end!
I did this with my member assignment
WITH
MEMBER [CompareDates]
AS
"{
[Date].[Calendar Date].[Date].&[20050701]
,[Date].[Calendar Date].[Date].&[20050702]
,[Date].[Calendar Date].[Date].&[20050703]
,[Date].[Calendar Date].[Date].&[20050704]
}"
and then with that I can do
STRTOSET([CompareDates])
This solved my issue and I was able to view the figures for any range of comparative dates that the user chose
In SSRS I have 2 date ranges
the user selects the dates they want
Using those dates a bit of T-SQL finds the range of comparative dates I need from those 2 date ranges
I have this set as a mult value parameter.
I the pass that multi value parameter to a sting
Then it is that string I pass through to my MDX doing a STRTOSET
I see comparative figures against my normal date figures and it works really well
Thanks for responding
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply