Replacing NULL measure with a value or zero

Question

Post reply

Replacing NULL measure with a value or zero

iBar

SSCertifiable

Points: 5872
More actions
March 8, 2012 at 10:41 am

#150839

Hi,

SS2005 Standard edition

Two measures have data on different dates. on some dates one measure has data but other dont and in that case the measure which do not have data shows as (null).

If i browse individual measure with date then no problem, of-course! However i need both in a report against same date but in case of null i want to change that "(null)" to zero.

-- Current Output ReportDate Col1 Col2 2007-12-01 00:00:00.000 369 1368 2007-12-02 00:00:00.000 (null) 1824 2007-12-03 00:00:00.000 615 (null)

Instead of handling this in SSRS with IsNothing() in every report, Can i use any general option to change such cases to zero?

-- Desired Output ReportDate Col1 Col2 2007-12-01 00:00:00.000 369 1368 2007-12-02 00:00:00.000 0 1824 2007-12-03 00:00:00.000 615 0

Any option while processing cube OR in SSRS?

Any new option is SSRS 2008?

Thanks.

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

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

Sean Lange SSC Guru Points: 286562 More actions · Answer 1

Well you could add an ISNULL to your query.



ISNULL(col1, 0) as col1

Or is that what you are trying to avoid? You have to either handle it in your query or in your ddl.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

iBar SSCertifiable Points: 5872 More actions · Answer 2

iBar

SSCertifiable

Points: 5872

March 8, 2012 at 4:50 pm

#1456886

I am trying to make SSRS report based on SSAS cube.

Questions are:

I want to Avoid IsNothing() because in my actual report then i need to use that too much?

How to use IsNULL in MDX?

Any other SSAS Cube processing option OR builtin SSRS report option to change such cross section NULLS to zero?

Thanks.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

You don't necessarily want to default the cube to replace nulls with 0s because then it'll set your averages and counts to be inaccurate. Unless, of course, you WANT to adjust those rollups, in which case I'd change the feeder/source data.

However, what you're dealing with is a display issue. As aggravating as it is, yes, your best bet is the IsNothing() all over creation in the report. There's no way I'm familiar with to have a report default all NULLs, in all columns, to be a certain value. If there's a way to do it on a single column I'm unfamiliar, it'd basically end up being the same as IsNothing() everywhere just better hidden.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

iBar SSCertifiable Points: 5872 More actions · Answer 4

OK. However, i am really not dealing with a display issue.

In my actual complex calculation, i want to add col1+col2 but problem in doing this is then i need to handle IsNothing() everywhere in every calculation and eventually in every report.

So what i was after to handle this is on a more general level handle this and then i dont need to care about handling this in every report.

So anything just clicked in your mind?

Thanks.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

MidBar (3/9/2012)

So anything just clicked in your mind?

Ah, so it's the sub-procedures to the reports you want to adjust for pre-calculations. Gotcha. Offhand, no. Most of the time either I've adjusted the base data or done those rollup calculations at the report level with field + field or the like, where the IsNothing() (ISNULL() in T-SQL) is converted and field+field takes the displayed value.

I'll have to leave this for folks who are more familiar with cube output options. I don't know of a way to get that to happen for you on output without an input change to the cube data.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA