Helpme undestand MDX exists function

existsmdxssas

After spend almost decade in OLTP world I got the courage to try to build my first CUBE.
I installed VS2012 and created Business Intelligence -> Analysis Services.
I did build Data Source Views, Cube and few Dimensions.
Everything was perfect in EXCEL until I get requests from users to do OUTER JOIN or EXISTS filtering in my cube.
I start googling and find that there is MDX Function EXISTS which cloud do the job for me.
Since this is MDX expression I have no clue where should I write that.
In cube Measures, In dimensions attributes ,Calculated Member or in Data Source View ?

Here is my business problem .
My DSV looks like
enter image description here

*Business case is to make EXCEL users be able to filter OSR_DUG_IMV Dimensions and Attributes, measuring table NABAVA , for members that do not EXISTS at table IZLAZI. *

Best Answer

If you are browsing in Excel and want to be able to see dimension attribute members that do not have corresponding rows in a fact table (measure returns nothing), there is a pivot table setting you can use.

Access the PivotTable Options dialog box by right clicking on the pivot table and choosing PivotTable Options. On the Display tab, check the box next to Show items with no data on rows. This will show all values of whatever hierarchy is in your rows regardless of if the measure in the Values section of your pivot table returns a non-blank result.

enter image description here

You can do the same thing for columns by checking the item underneath the one indicated in the image. This option must be set for each pivot table and will apply to all fields in the rows (or columns) when you check the box.

Related Solutions

MDX calculated member to band a measure

If you would have the age as an attribute in the People dimension in addition to having it as measure, you could define your bands as calculated members of this attribute hierarchy (calculated members do not need to be calculated measures, they can be members of any hierarchy!):

WITH Member [People].[Age].[0-26] AS
            Aggregate([People].[Age].[0] : [People].[Age].[26])
     Member [People].[Age].[26-51] AS
            Aggregate([People].[Age].[26] : [People].[Age].[51])
     Member [People].[Age].[Over 50] AS
            Aggregate([People].[Age].[51] : null)
SELECT 
  NON EMPTY {[Measures].[Count of People]} ON COLUMNS,
  NON EMPTY 
    [People].[Gender].[Gender]
    *
    [People].[Some].[Other Attributes]
    *
    { [People].[Age].[0-26], [People].[Age].[26-51], [People].[Age].[Over 50] }
    ON ROWS
FROM [MyCube]
WHERE ( ... slicer here .... )

Please note that I kept the border ages 26 and 51 in two bands each, as the names of your bands seem to suggest to do this. You can do this in MDX, as each definition of a calculated member is independent of the others, and hence it is not forbidden to have intersections between calculated members. But you probably would opt to adapt the expressions and only include each age in one band.

The way you tried it does not work, as a set in MDX consists of tuples, while you deliver a string from your IIF expression.

SSAS / MDX Query – Need row total

Try adding the All member into the set on rows:

SELECT 
  NON EMPTY 
    {[Measures].[Order Count]} ON 0
 ,NON EMPTY 
    {
        [Date].[Year].[Year].ALLMEMBERS*
        [Date].[Quarter].[Quarter].ALLMEMBERS*
        {
          [Order].[Customer].[Customer].ALLMEMBERS
         ,[Order].[Customer].[All]
        }
    }
  DIMENSION PROPERTIES 
    MEMBER_CAPTION
   ,MEMBER_UNIQUE_NAME
   ON 1
FROM [Order]
CELL PROPERTIES VALUE

Best Answer

Related Solutions

MDX calculated member to band a measure

SSAS / MDX Query – Need row total

Related Question