For example, if i have a time dimension (year, quarter,month) with 2003,2004
and 2004; another dimension is region(Coutry,State); a measure is population;

if there is no population data for April/2005 in MA in USA, then the April
and MA wouldn't be displayed in pivot table. how can i get around the
problem... thanks.

also,if the population measure returns nothing, can i assign 0 and display 0
in pivot table? if yes, how? thanks

"urgent" wrote:

To assign 0 to an empty measure, you can use calculated members (or
calculated cells, if you use SQL Server 2000 Enterprise Edition), like:

'iif(IsEmpty([Measures].[M1]), 0, [Measures].[M1])'

An earlier post in this NG suggests how to display empty data cells in
Excel and OWC Pivot Tables:

http://groups-beta.google.com/group/...rver.olap/msg/
018ea4445c1ba0a6?hl=en
From: "Ohjoo Kwon"
Date: Tue, 15 Feb 2005 01:50:57 +0900

Subject: Re: Empty cells in Excel

I could not find the way through UI operation either, but you can enable
it
by using Macros.

There is a good sample written by Peter.

http://groups.google.co.kr/gro*ups?h...thre*adm=2D000.
..

Of course, there is another alternative way. It is to use OWC.

First save as your Excel's pivottable to web page and enable select
checkbox
"empty rows" and "empty columns" at Report tab of "Command and Options"
dialog box. Click button, "Export to Microsoft Excel" and you get a
excel
file whose extension is "htm". Then save it as xls file again.

Ohjoo Kwon
www.olapforum.com


"Bender" <roman.i... (AT) avtenta (DOT) si> wrote



- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

but how about for measure not calculated? thanks

"Deepak Puri" wrote:

You can use calculated cells for such measures (if you have SQL Server
2000 Enterprise Edition).


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

I agree with Deepak. 'With' keyword is useful to create calculated members at
runtime using the actual members. instead of going for Calculated Cell i
think having a placeholder value for empty cells in the underlying database
could be the best solution.
Note: the example is based on "Foodmart 2000" sample database.

WITH MEMBER [Measures].[NonEmptyUnitSales] AS 'CoalesceEmpty(Measures.[Unit
Sales], 0)'
SELECT { { { [Time].&[1997].CHILDREN } * { [Measures].[NonEmptyUnitSales] }
} } ON COLUMNS ,
{ [Customers].[All Customers].CHILDREN } ON ROWS
FROM [Sales]


"urgent" wrote:

But if using calculated memeber, then i will lose the drill-thru feature in
pivot table, any more suggestions on this?

lots of thanks and your help really helpful.

"Nin" wrote: