View Single Post
  #5 (permalink)  
Old January 16th, 2012, 12:34 PM
GWadeCF GWadeCF is offline
Registered User
Points: 15, Level: 1
Points: 15, Level: 1 Points: 15, Level: 1 Points: 15, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2011
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Code from Cube Member Dataset1

Hi, Grant,

Thanks for your response. I'm really looking forward to getting this report running. It's going to be extremely helpful.

Here's the code for DataSet1 of the Cube Member report, straight from the download:

Code:
------------------------------------------------------------------------------------------------------------------------------------
-- Grant Paisley
-- Angry Koala
-- 28 Nov 2008
------------------------------------------------------------------------------------------------------------------------------------
WITH
--/*
MEMBER [Measures].[Mbr_Key]   AS iif(@pDriver="Date", @pDateMbr ,
                                                           iif(@pDriver="Rows", @pRowMbr,
                                                           iif(@pDriver="Columns", @pColMbr,
                                                           iif(@pDriver="Filter", @pFilterMbr,
                                                           @pDateMbr ))))
--*/
--MEMBER [Measures].[Mbr_Key]   AS StrToValue(@pMbrKey)


MEMBER [Measures].[Measure_Value] AS StrToValue(@pMeasure)

MEMBER [Measures].[Measure_Label] AS StrToValue(@pMeasure + ".Member_Name")

MEMBER [Measures].[RowFocus] AS -- returns hierarchy all level value if changing rows otherwise current focus
       strtovalue( '"' +  [Measures].[Mbr_Key] + '"' )

-- MEMBER [Measures].[Row_Key]   AS StrToValue( @pRowMbr + ".Hierarchy.Currentmember.Uniquename" )
MEMBER [Measures].[Row_Key]   AS StrToValue( [Measures].[Mbr_Key]  + ".Hierarchy.Currentmember.Uniquename" )

MEMBER [Measures].[Row_Label] AS StrToValue( [Measures].[Mbr_Key] + ".Hierarchy.CurrentMember.Member_Caption" )
MEMBER [Measures].[Row_Level] AS StrToValue( [Measures].[Mbr_Key] + ".Hierarchy.CurrentMember.Level.Ordinal" )
MEMBER [Measures].[Row_Level_Name] AS StrToValue( [Measures].[Mbr_Key] + ".Hierarchy.Level.Name" )
MEMBER [Measures].[Row_Hierarchy_Name] AS StrToValue( [Measures].[Mbr_Key] + ".Hierarchy.Name" )
MEMBER [Measures].[Row_Hierarchy_UniqueName] AS StrToValue( [Measures].[Mbr_Key] + ".Hierarchy.UniqueName" )
MEMBER [Measures].[Row_Dimension_Name] AS StrToValue( [Measures].[Mbr_Key] + ".Dimension.Name" )
MEMBER [Measures].[Row_Dimension_UniqueName] AS StrToValue( [Measures].[Mbr_Key] + ".Dimension_Unique_Name" )


MEMBER [MEASURES].MbrIsAncestor AS  
       strtovalue("IsAncestor( " + [Measures].[Mbr_Key] + ".hierarchy.currentmember, " + [Measures].[Mbr_Key] + " )"
                      + " or ( " + [Measures].[Mbr_Key] + ".hierarchy.currentmember is " + [Measures].[Mbr_Key] + " )"
       )


SELECT NON EMPTY {

  [Measures].[Measure_Label] ,

 [Measures].[Mbr_Key],
 [Measures].[RowFocus],
 [Measures].[Row_Key],
 [Measures].[Row_Label],
 [Measures].[Row_Level],
 [Measures].[Row_Level_Name],
 [Measures].[Row_Hierarchy_Name],
 [Measures].[Row_Hierarchy_UniqueName],
 [Measures].[Row_Dimension_Name],
 [Measures].[Row_Dimension_UniqueName],
 [Measures].[MbrIsAncestor],

 [Measures].[Measure_Value] 


} ON COLUMNS, 

NON EMPTY {
 (
--  STRTOMEMBER(@pDateMbr) : STRTOMEMBER(@pDateMbr + ".lag(" +  Str([Measures].[Lag] - 1) + ")" )

--*
--     STRTOSET("{" + [Measures].[RowFocus] + ".parent.siblings, "+ [Measures].[RowFocus] + ".siblings, " + [Measures].[RowFocus] + ".children}" )

 STRTOSET(
   "{" +
   GENERATE( 
      ASCENDANTS(STRTOMEMBER([Measures].[RowFocus]) )
      ,STRTOVALUE([Measures].[RowFocus] + ".Hierarchy.CURRENTMEMBER.Uniquename")
      ,".siblings, "
   )
   + ","
   + [Measures].[RowFocus] + ".children"
   + "}"
 )

 )
} 
ON ROWS 

FROM [Adventure Works]
And here's the error message I get when I try to run the query in the Query Designer:

TITLE: Microsoft Visual Studio
------------------------------

Query preparation failed.

------------------------------
ADDITIONAL INFORMATION:

Parser: The syntax for ',' is incorrect. (Microsoft SQL Server 2008 R2 Analysis Services)

------------------------------
BUTTONS:

OK
------------------------------

I get the same issue if I replace the last STRTOSET code with the code you provided.

Thanks again for your help.
Reply With Quote