CODEWITHSUNDEEP
ssasbusiness-intelligenceolap-cube
user2859298
user2859298

Reputation: 1443

SSAS - Exclude Blank column

I have a Sales cube with the following setup :
Measure : AnalysisValue
Row : Account Code
Column : Analysis Record

I am trying to find out the values for each account grouped by the Analysis Record.

Example

This works great! My question is how can i exclude the (Blank) column? And how can i exclude the (Blank) column from the Grand Total?

Upvotes: 1

Views: 1932

Answers (2)

FrankPl
FrankPl

Reputation: 13315

If you are writing the MDX yourself, you can use NON EMPTY on every axis, i. e.

SELECT
       NON EMPTY
       ...
       ON COLUMNS,
       ...
       ON ROWS
  FROM myCube

to show empty rows, but not empty columns, or

SELECT
       ...
       ON COLUMNS,
       NON EMPTY
       ...
       ON ROWS
  FROM myCube

to show empty columns, but not empty rows, or

SELECT
       NON EMPTY
       ...
       ON COLUMNS,
       NON EMPTY
       ...
       ON ROWS
  FROM myCube

to not show empty rows or columns.

If you are using a tool that generates the MDX for you, you can in many cases set options if the NON EMPTY should be added to the MDX statement for you. In the Cube Browser in your picture, you would right click the grid, select "Report" and then check/uncheck "Empty rows" and/or "empty columns". There are similar options if you are using Excel Pivot tables.

Upvotes: 1

vhadalgi
vhadalgi

Reputation: 7189

You could try to filter [column].Members using NOT ISEMPTY() to exclude all the empty values, and then bottom_count the filtered set.

If empty values are stored in your underlying fact table data, by default they will be converted to zeroes when the cube is processed. You can use the Null Processing option on a measure to control whether null facts are converted into 0, converted to an empty value, or even throws an error during processing. If you do not want empty cell values appearing in your query results, you should create queries, calculated members, or MDX Script statements that eliminate the empty values or replace them with some other value.

see this

Upvotes: 1

Related Questions