Is it possible to make a query return 0 for all non existing values?

I've created a query from Foodmart:
with set MonBud as '{[Time].[1997].[Q3].[7]:[Time].[1997].[Q3].[7].lag(3),
[Time].[1998].[Q3].[7]:[Time].[1998].[Q3].[7].lag(3)}'
select
{[Measures].[Store Cost],[Measures].[Store Sales]} on columns,
{MonBud} on rows
from [Sales]

It returns values for 1997, but not for 1998. Is it possible to make it
return 0 instead of Null or Nothing?

All help appreciated!

Kaisa M: Lindahl

Hi Kaisa,

If you have AS 2000 Enterprise Edition, try this:

'{[Time].[1997].[Q3].[7]:[Time].[1997].[Q3].[7].lag(3),
[Time].[1998].[Q3].[7]:[Time].[1998].[Q3].[7].lag(3)}'
Cell Calculation [ForceNull] for '(Measures.AllMembers)'
as '0', CONDITION = 'IsEmpty(CalculationpassValue(
Measures.CurrentMember, -1, RELATIVE))'
select
{[Measures].[Store Cost],[Measures].[Store Sales]} on columns,
{MonBud} on rows
from [Sales]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

I have Standard Edition, but it still worked.
THanks a lot!

Kaisa
"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

What datatype are the padded values?
The query works well against the OLAP cube, I get a string of 0s. But when I
try using the query in a Reporting Services 2000 report, it will just
display #Error. I get an error message saying
The value expression for the textbox 'textbox8' uses an aggregate function
on data of varying data types. Aggregate functions other than First, Last,
Previous, Count, and CountDistinct can only aggregate data of a single data
type.

I'm trying to do a SUM(). How can I make my 0s be the same datatype as the
other numbers?



Kaisa M. Lindahl



"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

I tried it out using Deepak's way on Adventure Works. Besides padding
the (null) values to 0, Deepak's way also changed all the non-zero
values to (null), which was not expected. Is that what it is supposed
to do? [ I am using AS 2005 RTM ]

#1 - no padding
with set MonBud as
'{[Date].[Calendar].[Month].&[2003]&[7]:[Date].[Calendar].[Month].&[2003]&[7].lag(3),

[Date].[Calendar].[Month].&[2004]&[7]:[Date].[Calendar].[Month].&[2004]&[7].lag(3)}'

select {[Measures].[Reseller Freight Cost],[Measures].[Reseller Sales
Amount]} on columns,
{MonBud} on rows
from [Adventure Works]

#2 - padding with 0
with set MonBud as
'{[Date].[Calendar].[Month].&[2003]&[7]:[Date].[Calendar].[Month].&[2003]&[7].lag(3),

[Date].[Calendar].[Month].&[2004]&[7]:[Date].[Calendar].[Month].&[2004]&[7].lag(3)}'

Cell Calculation [ForceNull] for '(Measures.AllMembers)'
as '0', CONDITION =
'IsEmpty(CalculationpassValue(Measures.CurrentMemb er, -1, RELATIVE))'
select {[Measures].[Reseller Freight Cost],[Measures].[Reseller Sales
Amount]} on columns,
{MonBud} on rows
from [Adventure Works]