Hello,


Not exactly an Analysis Services question, but if anybody has run into
this before, I'd be glad to hear their ideas.

In am running AS2000, and have a cube that has several named sets
pertaining to my date dimension. Each named set returns a set of
months for different time periods (i.e. 0-12 months, 13-24 months, etc)

The named set logic works great. However, the users have a Crystal
Analytics report that they requested that lists each named set, the
members of the set, and totals for each set. See the diagram below for
an example.
When the named set is brought into the report, Crystal displays only
the members, and not the set to which they belong. This makes it
impossible to group the members according to the set that they belong
to. Has anybody run into a similar problem, or know of a suggestion?
I had considered creating these sets as a dimension, but that would
require updating the dimension table every month to get the new
buckets.

Thanks,
Justin

Time Span Month Value
0-12 March 2005 4
April 2005 6
....
0-12 Total 23

13-24 March 2006 10
April 2006 11
....
13-24 Total 50
----------------------------------
Total all spans 73

Here's an MDX query for the Foodmart Warehouse cube:

'{[Time].[1997].[Q1].[1]:[Time].[1997].[Q4].[12]}'
Set [13-24] as
'{[Time].[1998].[Q1].[1]:[Time].[1998].[Q4].[12]}'
Member [Time].[Total 0-12] as
'Aggregate([0-12])'
Member [Time].[Total 13-24] as
'Aggregate([13-24])'
Member [Measures].[Time Span] as
'iif(Count(Intersect({[Time].CurrentMember}, [0-12])) > 0
Or [Time].CurrentMember is [Time].[Total 0-12], "0-12",
iif(Count(Intersect({[Time].CurrentMember}, [13-24])) > 0
Or [Time].CurrentMember is [Time].[Total 13-24], "13-24",
"Unknown"))', FORMAT_STRING = '@'

Select {[Measures].[Time Span],
[Measures].[Warehouse Sales]} on columns,
{Descendants([Time].[1997], [Time].[Month]),
[Time].[Total 0-12],
Descendants([Time].[1998], [Time].[Month]),
[Time].[Total 13-24]} on rows
from Warehouse

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!