0

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. *

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.