SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

OLAP:re-writting stored value as calculated measures for correct result in Excel

Reply
Contributor
Posts: 22

OLAP:re-writting stored value as calculated measures for correct result in Excel

When using multiple multi-select filters I have noticed that some stored values return incorrect results in MS Excel (although I can replicate these in Enterprise Guide).  This occurs most frequently when more that one stored value is selected (where one of the stored values becomes the results of an unfiltered slicer).  This is mentioned in http://support.sas.com/kb/30/729.html.  And links to a blog outling this problem - stating that is due to the fact that MDX does not have an equivalent of SQL 'in' and instead temporary MDX sets are created and then used in the 'where' clause'.

WITH MEMBER

  [Store].[XL_QZX] AS 'Aggregate ( { [Store].[All Stores].[USA].[WA] , [Store].[All Stores].[USA].[CA] } )'

SELECT

  NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Product].[All Products]})}))

    DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS

FROM [Sales]

WHERE ([Measures].[Declining Stores Count], [Time].[1998].[Q3], [Store].[XL_QZX])

http://sqlblog.com/blogs/mosha/archive/2005/11/18/writing-multiselect-friendly-mdx-calculations.aspx

I have a number of colums in the dataset that the cube is created from in the form of binary values, 1 if a sale is made from that product, 0 is no sale is made.  Such that the total sales are the sum of the columns (the stored values specifies at build time in the cube).  How can I create a calculated measure so that regardless of the combination of dimensions/hirarchies that are specified the totals for each product are correct (currently, only one products total is correct when more than one product stored value is used).

Post a Question
Discussion Stats
  • 0 replies
  • 153 views
  • 0 likes
  • 1 in conversation