Important Calculated members

Date Range- This is a good one

WITH SET [MyRange] AS
'{[Time].[2009].&[5].[22].[26] : [Time].[2009].&[6].[26].[24]}'
MEMBER [Time].[Range] AS 'Aggregate([MyRange],[Measures].CurrentMember)'

select {[Measures].[Sends],[Measures].[Views], [Measures].[Clicks]} on columns,
{[Zone Id].&[142]}
on rows
from [All Communication Analysis] where ([Time].[Range])


This is an OR query. I got this query from HERE

SELECT [Measures].[Internet Sales Amount] ON 0
FROM [Adventure Works]
WHERE(
{([Date].[Calendar Year].&[2003],[Customer].[Country].[All Customers])
,
([Date].[Calendar Year].[All Periods],[Customer].[Country].&[United States])})



WITH SET [Top5Camps] AS ' TopCount({Descendants( [Zone Id].&[14],3)},200,([Measures].[Sends])) '

select {[Measures].[Sends],[Measures].[Views],[Measures].[Clicks],[Measures].[Clicks Text],[Measures].[Clicks Html],[Measures].[Bounces],[Measures].[Soft Bounces],[Measures].[Hard Bounces],[Measures].[Cliks To Buy],[Measures].[Url Count]} on Columns,

[Top5Camps] DIMENSION PROPERTIES [Zone].[uniq views],[Zone].[uniq soft bounces], [Zone].[uniq hard bounces],[Zone].[uniq clicks html], [Zone].[uniq clicks text],[Zone].[uniq clicks],[Zone].[uniq bounces], [Zone].[Campaign Launch Date],[Zone].[Url Count], MEMBER_CAPTION, MEMBER_KEY on rows

from [Communication]

--Some of these below use custom code in reporting services. The queries are formed in visual basic. Maybe someday I'll take the time to make these real queries.

="select {[RunningTotalSubs]} on Columns, CrossJoin(TopCount( { [DMA].children}, 5000, ([RunningTotalSubs])),

{[Time].[2004].&[1].[1].[1], [Time].[2005].&[1].[1].[1],[Time]}) on rows


from [consumers] where ( [Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "])"

WITH

MEMBER [Measures].[Variant Percentage] AS
'(( [Time].[2004].&[1].[1].[1], [RunningTotalSubs] ) -( [Time].[2005].&[1].[1].[1], [RunningTotalSubs] ))/( [Time].[All Time], [RunningTotalSubs] ) '

select {[RunningTotalSubs],[Measures].[Variant Percentage] } on Columns, CrossJoin(TopCount( { [DMA].children}, 5000, ([RunningTotalSubs])),

{[Time].[2004].&[1].[1].[1], [Time].[2005].&[1].[1].[1],[Time]}) on rows


from [consumers] where ( [Zone Id].&[14])

WITH SET [Top5Camps] AS

' Descendants([Zone Id].&[14].&[74],2) '

SELECT {[Measures].[Sends],[Measures].[S Sends],[Measures].[Views],[Measures].[Uniq Views],[Measures].[Clicks],[Measures].[Uniq Clicks],[Measures].[Clicks Text],[Measures].[Uniq Clicks Text],[Measures].[Clicks Html],[Measures].[Uniq Clicks Html],[Measures].[Bounces],[Measures].[Uniq Bounces],[Measures].[Soft Bounces],[Measures].[Hard Bounces],[Measures].[Cliks To Buy],[Measures].[Url Count],[Measures].[F2f Count]}

="select { { ([Age Group].[All Age Group], [Gender].[All Gender]) },{ ([Age Group].[All Age Group], [Gender].[Male]) },{ ([Age Group].[All Age Group], [Gender].[Female]) },{ ([Age Group].[All Age Group], [Gender].[Unknown]) },{ ([Age Group].[13 and 17], [Gender].[All Gender]) },{ ([Age Group].[18 and 24], [Gender].[All Gender]) },{ ([Age Group].[25 and 34], [Gender].[All Gender]) } ,{ ([Age Group].[35 and 44], [Gender].[All Gender]) },{ ([Age Group].[45 and 54], [Gender].[All Gender]) },{ ([Age Group].[over 55], [Gender].[All Gender]) },{ ([Age Group].[Unknown], [Gender].[All Gender]) }

} on Columns, NON EMPTY { TopCount( { [Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "].children}, 10, ([RunningTotalSubs])) } on Rows from [consumers] where ("&

iif(Parameters!dt.Value="d",Code.getCubeDate(Parameters!p1.Value, Parameters!dt.Value),"[Time]") &",[RunningTotalSubs])"

="select { { ([Age Group].[All Age Group], [Gender].[All Gender]) },{ ([Age Group].[All Age Group], [Gender].[Male]) },{ ([Age Group].[All Age Group], [Gender].[Female]) },{ ([Age Group].[All Age Group], [Gender].[Unknown]) },{ ([Age Group].[13 and 17], [Gender].[All Gender]) },{ ([Age Group].[18 and 24], [Gender].[All Gender]) },{ ([Age Group].[25 and 34], [Gender].[All Gender]) } ,{ ([Age Group].[35 and 44], [Gender].[All Gender]) },{ ([Age Group].[45 and 54], [Gender].[All Gender]) },{ ([Age Group].[over 55], [Gender].[All Gender]) },{ ([Age Group].[Unknown], [Gender].[All Gender]) }

} on Columns, NON EMPTY { TopCount( { [Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "].children}, 10, ([RunningTotalSubs])) } DIMENSION PROPERTIES [Zone Id].[Campaign Type Id].[Key], [Zone Id].[Campaign Type Id].[Name] on Rows from [consumers] where ("&

iif(Parameters!dt.Value="d",Code.getCubeDate(Parameters!p1.Value, Parameters!dt.Value),"[Time]") &",[RunningTotalSubs])"

ON COLUMNS,

Order([Top5Camps], [Top5Camps].currentmember.properties("Campaign Launch Date") ,desc) DIMENSION PROPERTIES [Zone].[Campaign Launch Date], [Zone].[Url Count], MEMBER_CAPTION, MEMBER_KEY

ON ROWS
from [All communication analysis]

WITH SET [Top5Camps] AS

' Descendants([Zone Id].&[14].&[74],2) '

SELECT {[Measures].[Sends],[Measures].[S Sends],[Measures].[Views],[Measures].[Uniq Views],[Measures].[Clicks],[Measures].[Uniq Clicks],[Measures].[Clicks Text],[Measures].[Uniq Clicks Text],[Measures].[Clicks Html],[Measures].[Uniq Clicks Html],[Measures].[Bounces],[Measures].[Uniq Bounces],[Measures].[Soft Bounces],[Measures].[Hard Bounces],[Measures].[Cliks To Buy],[Measures].[Url Count],[Measures].[F2f Count]}

ON COLUMNS, [Top5Camps] DIMENSION PROPERTIES [Zone].[Campaign Launch Date], [Zone].[Url Count], MEMBER_CAPTION, MEMBER_KEY

ON ROWS
from [All communication analysis]

="WITH SET [Top5Camps] AS ' STRTOSET(IIF(ISERROR(STRTOVALUE(""[Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "].&[" & Code.EDecrypt(Parameters!PId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "].&[" & Code.EDecrypt(Parameters!CTId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "]"")), ""[Zone Id].&[3].&[45].&[4311].Children"", ""[Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "].&[" & Code.EDecrypt(Parameters!PId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "].&[" & Code.EDecrypt(Parameters!CTId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "].Children"")) '

="select {[Measures].[Sends],[Measures].[S Sends],[Measures].[Views],[Measures].[Uniq Views],[Measures].[Clicks],[Measures].[Uniq Clicks],[Measures].[Clicks Text],[Measures].[Uniq Clicks Text],[Measures].[Clicks Html],[Measures].[Uniq Clicks Html],[Measures].[Bounces],[Measures].[Uniq Bounces],[Measures].[Soft Bounces],[Measures].[Hard Bounces],[Measures].[Cliks To Buy],[Measures].[Url Count],[Measures].[F2f Count]} on Columns,

[Top5Camps] on rows

from [All Communication Analysis]"

="WITH SET [Top5Camps] AS ' STRTOSET(IIF(ISERROR(STRTOVALUE(""[Zone Id].), ""[Zone Id].&[3].&[45].&[4311].Children"", ""[Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "].&[" & Code.EDecrypt(Parameters!PId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "].&[" & Code.EDecrypt(Parameters!CTId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "].Children"")) '

="select {[Measures].[Sends],[Measures].[S Sends],[Measures].[Views],[Measures].[Uniq Views],[Measures].[Clicks],[Measures].[Uniq Clicks],[Measures].[Clicks Text],[Measures].[Uniq Clicks Text],[Measures].[Clicks Html],[Measures].[Uniq Clicks Html],[Measures].[Bounces],[Measures].[Uniq Bounces],[Measures].[Soft Bounces],[Measures].[Hard Bounces],[Measures].[Cliks To Buy],[Measures].[Url Count],[Measures].[F2f Count]} on Columns,

[Top5Camps] on rows

from [All Communication Analysis]"

="WITH SET Top5Camps AS ' Descendants( [Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "], 3)'

SELECT {[Measures].[Sends],[Measures].[S Sends],[Measures].[Views],[Measures].[Uniq Views],[Measures].[Clicks],[Measures].[Uniq Clicks],[Measures].[Clicks Text],[Measures].[Uniq Clicks Text],[Measures].[Clicks Html],[Measures].[Uniq Clicks Html],[Measures].[Bounces],[Measures].[Uniq Bounces],[Measures].[Soft Bounces],[Measures].[Hard Bounces],[Measures].[Cliks To Buy],[Measures].[Url Count],[Measures].[F2f Count]}

ON COLUMNS, [Top5Camps] DIMENSION PROPERTIES [Zone].[Campaign Launch Date], MEMBER_CAPTION, MEMBER_KEY

ON ROWS
from [All communication analysis]"

type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception

="WITH SET Top5Camps AS ' Descendants( [Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "].&[" & Code.EDecrypt(Parameters!PId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "].&[" & Code.EDecrypt(Parameters!CTId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "], 1)'

SELECT {[Measures].[Sends],[Measures].[S Sends],[Measures].[Views],[Measures].[Uniq Views],[Measures].[Clicks],[Measures].[Uniq Clicks],[Measures].[Clicks Text],[Measures].[Uniq Clicks Text],[Measures].[Clicks Html],[Measures].[Uniq Clicks Html],[Measures].[Bounces],[Measures].[Uniq Bounces],[Measures].[Soft Bounces],[Measures].[Hard Bounces],[Measures].[Cliks To Buy],[Measures].[Url Count],[Measures].[F2f Count]}

ON COLUMNS, [Top5Camps] DIMENSION PROPERTIES [Zone].[Campaign Launch Date], [Zone].[Url Count], MEMBER_CAPTION, MEMBER_KEY

ON ROWS
from [communication ]"



One link I came accross when using deminsion properties. Try not to make queries with properties because they are slow. But if you have to:

http://www.sqlservercentral.com/blogs/mysqllearnings/archive/2009/04/15/key-column-of-a-dimension-in-ssas.aspx

Each product has a weight associated with it. The requirement is to filter all the products whose weight is 5.

To achieve this, weight should be the member property of the product. This time, it cannot be made as a key column. But, again instead of using ".properties" function, functions like "EXISTS" can be used.

A general way of writing this would be

Filter ([Prod Dim].[Prod Hier].[SKU Lvl].members,[Prod Dim].[Prod Hier].[SKU Lvl].currentmember.properties("Weight") = 5)

In this case, it has to filter each member by getting the property of each member and checking whether the weight is less than 5. Instead of using the ".properties" function, it can be written using "EXISTS" function.

Exists ([Prod Dim].[Prod Hier].[SKU Lvl].members,filter([Prod Dim].[Weight].[Weight].members,[Prod Dim].[Weight].currentmember is [Prod Dim].[Weight].[5]))

Also, it is better to use the function "IS" instead of "=" because IS function eliminates the conversion of the given string to a member.

Because of doing this, there was a considerable improvement in the query performance. These were the scenarios where I felt natural key was useful when specified as the key column.