Hide NULL Rows

Question

Post reply

Hide NULL Rows

erdem1973

SSCommitted

Points: 1843
More actions
September 30, 2010 at 3:21 pm

#227227

Hello Experts,
How can I hide NULL rows in my SSRS report? I want to filter this cellset based on my "Sale Unit" measure. If it is null then its row should be completely hidden.
Here is my MDX Query behin the report:
Any idea?
[p]
WITH
MEMBER Measures.[DC Stock Level] AS SUM([Dim Date].[WeeklyCalendar].[Week Of Year].members(0):ClosingPeriod([Dim Date].[WeeklyCalendar].[Week Of Year]), Measures.[DcInventory] )
MEMBER Measures.[Store Stock Level] AS SUM([Dim Date].[WeeklyCalendar].[Week Of Year].members(0):ClosingPeriod([Dim Date].[WeeklyCalendar].[Week Of Year]), Measures.[StoreInventory] )
SELECT NON EMPTY { [Measures].[Sale Unit],[Measures].[DC Stock Level],[Measures].[Store Stock Level] } ON COLUMNS,
NON EMPTY { ([Dim Product MAX].[Product].[Style Code].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [HC_WEEKLYSALES] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
[/p]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

erdem1973 SSCommitted Points: 1843 More actions · Answer 1

There are three measures in this report. And I think it is something about NONEMPTY keyword.

Any idea?

erdem1973 SSCommitted Points: 1843 More actions · Answer 2

I have tried to apply following on textbox:

Select -- > Sale_Unit -- > Properties --> Visibility --> Hidden Expressions:

iif(IsNothing(Fields!Sale_Unit.Value),True,False)

and report still shows the NULL Sale Unit rows

Abs-225476 Hall of Fame Points: 3483 More actions · Answer 3

Hi, I think you need to use the isnull function and return a value for the Sale_Unit when it is null

So if the null vlaue for the Sale_Unit is 0 then you can change the conditional suppression to

=iif(Fields!Sale_Unit.Value=0,True,False)

The isnull function in sql would be isnull(Sale_unit,0) this would return a zero for null values but I don't know if there is a function in mdx which does the same job.

erdem1973 SSCommitted Points: 1843 More actions · Answer 4

Thank you for your reply.

In my MDX you can see a snippet such as:

[p]

SELECT NON EMPTY { [Measures].[Sale Unit],[Measures].[DC Stock Level],[Measures].[Store Stock Level] } ON COLUMNS

[/p]

This code part returns all NON EMPTY measures of my cube. But my purpose is to select nonempty Sale Unit measure and its dependant measures.

For example I have used Hidden property of the text boxes. But it is not useful for me. Because I have to reduce my cellset based on NON NULL values 🙂

Cody Konior SSCarpal Tunnel Points: 4827 More actions · Answer 5

I don't know about MDX, but I do know about RS's Hidden parameter and how it sometimes doesn't do what you expect. For example, sometimes it leaves blank space and the blank space will vary depending on which output format (PDF, HTML, etc). It drives me bonkers.

In cases like that, you can create a group on the row (even a static one, like group on the expression "=1"), then set a Filter like:

=IIf(IsNothing(Fields!Sale_Unit.Value),0,1)

(set the type to Integer)

EQUALS

1

In a similar way, I often use this to filter out headings in a table for subsections that don't have any data, by creating a static group around them and setting the filter:

[Count(FieldName)]

GT

0