How do I add values from ON ROWS?

• SQLHeap

  Hall of Fame

  Points: 3572

  More actions

  April 25, 2017 at 2:02 pm

  #342610

  I have this query, and need to change it to add 2 names because she had a last name change, but still needs 1 count for both names, but just her.

  In this example she is persons A and B.

  SELECT NON EMPTY { [Measures].[Cube] }

  ON COLUMNS, NON EMPTY { ([Underwriting Facts].[Underwriter].[Underwriter].ALLMEMBERS ) }

  DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( {

  ([Underwriting Facts].[Underwriter].&[A]+[Underwriting Facts].[Underwriter].[AB]),

  [Underwriting Facts].[Underwriter].&[A],

  [Underwriting Facts].[Underwriter].&,

  [Underwriting Facts].[Underwriter].&[C],

  [Underwriting Facts].[Underwriter].&[D],

  [Underwriting Facts].[Underwriter].&[E

  } )

  ON COLUMNS FROM [Cube By Date]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

  There is an exception to every rule, except this one...

• SQLHeap

  Hall of Fame

  Points: 3572

  More actions

  April 26, 2017 at 12:26 pm

  #1940084

  Ok, I've managed to figure it out, now to figure out how to order it...any help would be appreciated!

  WITH MEMBER [Underwriting Facts].[Underwriter].[All].[Amanda P-D]

  AS [Underwriting Facts].[Underwriter].&[Amanda D]+[Underwriting Facts].[Underwriter].&[Amanda P]

  SELECT NON EMPTY { [Measures].[UW Submissions] } ON COLUMNS, NON EMPTY { ( [Underwriting Facts].[Underwriter].[Underwriter].ALLMEMBERS ) }

  DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM (

  SELECT ( { [Underwriting Facts].[Underwriter].&[Andrea W],

  [Underwriting Facts].[Underwriter].&[Ann F],

  [Underwriting Facts].[Underwriter].&[Aubrey S]

  } ) ON COLUMNS,

  ORDER ( { [Underwriting Facts].[Underwriter].[Underwriter] }, [Measures].[UW Submissions], ASC )

  FROM [Submissions by Submission Date] )

  CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

  There is an exception to every rule, except this one...