• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 144
  • Last Modified:

MDX Query on SQL 2008 SSRS

For the MDX query below,

"FROM ( SELECT ( STRTOSET(@DIMDATEYearGroup, CONSTRAINED) ) ON COLUMNS"

Is it similar to the where query in TSQL, what the use of STROSET & CONSTRAINED ? The condition defined doesn't need to be a selection list in the query,  correct ?

Inaddition, what does the ON COLUMN mean ?

SELECT
NON EMPTY { [Measures].[Sales Last Year YTD], [Measures].[Sales YTD],[Measures].[Budget YTD] } ON COLUMNS,
NON EMPTY { ([DIM DATE].[Year Group].[Month].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( STRTOSET(@DIMDATEYearGroup, CONSTRAINED) ) ON COLUMNS FROM [MyCube])
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
0
AXISHK
Asked:
AXISHK
1 Solution
 
Vikas GargBusiness Intelligence DeveloperCommented:
HI,

On Column means that those fields will be on columns site if you consider the output as matrix.

Where clause is somewhat different in MDX
in SQL you assign a column or value to the where clause but in MDX

WHERE ({[Order Type].[Order Type].&[1],
        [Order Type].[Order Type].&[5],
        [Order Type].[Order Type].&[6]},
       [Customers].[Customers].&[1012])

Columns itself has assigned values

STRTOSET will prepare a set of member as per the condition

SELECT
{ [Measures].[Customer Count] } ON COLUMNS,
STRTOSET('[Customer].[Customer Geography].[Country].Members') ON ROWS
FROM
[Adventure Works]
Output :-
MDX StrToSet Function

http://mdxquery.com/strtoset-mdx-function/
0
 
AXISHKAuthor Commented:
Tks
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now