Returning 0 Values - Please help

August 04, 2011, 08:56 AM

 
MONTH	AMOUNT
Jul-10	0
Aug-10	25
Sep-10	0
Oct-10	0
Nov-10	0
Dec-10	0
Jan-11	0
Feb-11	0
Mar-11	0
Apr-11	0
May-11	0
Jun-11	0

TABLE FILE XXXX 
SUM/PRINT AMOUNT
BY MONTH 
END

is returning the following

MONTH	AMOUNT
Aug-10	25


Please, how can I make it to return the 0's as in below:

MONTH	AMOUNT
Jul-10	0
Aug-10	25
Sep-10	0
Oct-10	0
Nov-10	0
Dec-10	0
Jan-11	0
Feb-11	0
Mar-11	0
Apr-11	0
May-11	0
Jun-11	0

thank you.

August 04, 2011, 10:06 AM

mgrackin

In what database is your data? Are your sure the Amount field contains 0 and not null?

August 04, 2011, 10:31 AM

umun

data source is Analysis Services cube. I will investigate if it contians 0 or null.

But if it contains null, can't I make it 0?
I have tried this but didn't work:

COMPUTE TEST/D12.2 = IF AMOUNT IS NULL THEN 0 ELSE AMOUNT;

Again, how can I check in webfocus if its null or not.

Any tip will be appreciated.

August 04, 2011, 11:03 AM

mgrackin

Try setting the ALL option to ON. This is used to tell WebFOCUS to return records even if a field referenced is null. You may also want to check the MFD to make sure the field definition indicates the field can be null if in fact it contains null values.

SET ALL = ON

August 04, 2011, 11:06 AM

GamP

You might want to take a look at this thread. Thuis concerns the same issue with a possible solution.

August 04, 2011, 04:05 PM

umun

quote:
Originally posted by mgrackin:
Try setting the ALL option to ON. This is used to tell WebFOCUS to return records even if a field referenced is null. You may also want to check the MFD to make sure the field definition indicates the field can be null if in fact it contains null values.

SET ALL = ON

The SET ALL = ON didn't work. The value in the cube are nulls but I will like them to be displayed as 0's in webfocus.

Any help?

August 04, 2011, 04:27 PM

Waz

Have you checked the SQL produced ?

Add this prior to the TABLE file.

SET TRACEOFF  = ALL
SET TRACEON   = SQLAGGR//CLIENT
SET TRACEON   = STMTRACE//CLIENT
SET TRACEON   = STMTRACE/2/CLIENT
SET TRACEUSER = ON
-RUN

August 04, 2011, 05:19 PM

mgrackin

Did you check the MFD for the data file to make sure the field definition indicates MISSING=ON? This indicates the field is allowed to be null.

August 05, 2011, 09:42 AM

umun

The MISSING=ON is set in the MFD

Here is what I got from trace:


 09.31.54 AF === SSASET    Filter list generated for MDX level:
 09.31.54 AF  [Time].[Time].[All].&[2010].&[Q3 2010].&[       201008]
 09.31.54 AF  :[Time].[Time].[All].&[2011].&[Q3 2011].&[       201107]
 09.31.54 AE === SSASET    MDX statement generated:
 09.31.54 AE  WITH
 09.31.54 AE  SET H21L3 as '
 09.31.54 AE  {[Time].[Time].[All].&[2010].&[Q3 2010].&[       201008]
 09.31.54 AE  :[Time].[Time].[All].&[2011].&[Q3 2011].&[       201107]}'
 09.31.54 AE  SET H21 as 'H21L3'
 09.31.54 AE  SELECT {
 09.31.54 AE  [Measures].[Amount]}
 09.31.54 AE  ON AXIS(0),
 09.31.54 AE  NON EMPTY H21 DIMENSION PROPERTIES
 09.31.54 AE  MEMBER_CAPTION ON AXIS(1)
 09.31.54 AE  FROM [Sales]
 0 NUMBER OF RECORDS IN TABLE=       11  LINES=     11
 09.31.55 AF === SSASET    Filter tree built for hierarchy [Brand].[Brand]:
 09.31.55 AF === SSASET    Root
 09.31.55 AF === SSASET    ==And
 09.31.55 AF === SSASET    ====Ignore
 09.31.55 AF === SSASET    ======Equal
 09.31.55 AF === SSASET    ========Field 53
 09.31.55 AF === SSASET    ========String constant:
 09.31.55 AF === SSASET    TYPEA
 09.31.55 AF === SSASET    Corrected filter tree:
 09.31.55 AF === SSASET    Root
 09.31.55 AF === SSASET    ==Filter
 09.31.55 AF === SSASET    ====Equal
 09.31.55 AF === SSASET    ======Field 53
 09.31.55 AF === SSASET    ======String constant:
 09.31.55 AF === SSASET    TYPEA
 09.31.55 AF === SSASET    Filter tree built for hierarchy [Country].[Country]:
 09.31.55 AF === SSASET    Root
 09.31.55 AF === SSASET    ==And
 09.31.55 AF === SSASET    ====Ignore
 09.31.55 AF === SSASET    ======Equal
 09.31.55 AF === SSASET    ========Field 55
 09.31.55 AF === SSASET    ========String constant:
 09.31.55 AF === SSASET    NG
 09.31.55 AF === SSASET    Corrected filter tree:
 09.31.55 AF === SSASET    Root
 09.31.55 AF === SSASET    ==Filter
 09.31.55 AF === SSASET    ====Equal
 09.31.55 AF === SSASET    ======Field 55
 09.31.55 AF === SSASET    ======String constant:
 09.31.55 AF === SSASET    NG
 09.31.55 AF === SSASET    Filter tree built for hierarchy [Currency].[Currency]
 09.31.55 AF === SSASET    Root
 09.31.55 AF === SSASET    ==And
 09.31.55 AF === SSASET    ====Ignore
 09.31.55 AF === SSASET    ======Equal
 09.31.55 AF === SSASET    ========Field 56
 09.31.55 AF === SSASET    ========String constant:
 09.31.55 AF === SSASET    USD
 09.31.55 AF === SSASET    Corrected filter tree:
 09.31.55 AF === SSASET    Root
 09.31.55 AF === SSASET    ==Filter
 09.31.55 AF === SSASET    ====Equal
 09.31.55 AF === SSASET    ======Field 56
 09.31.55 AF === SSASET    ======String constant:
 09.31.55 AF === SSASET    USD
 09.31.55 AF === SSASET    Filter tree built for hierarchy [Desc
 09.31.55 AF === SSASET    Root
 09.31.55 AF === SSASET    ==And
 09.31.55 AF === SSASET    ====Ignore
 09.31.55 AF === SSASET    ======Equal
 09.31.55 AF === SSASET    ========Field 65
 09.31.55 AF === SSASET    ========String constant:
 09.31.55 AF === SSASET    Vehicle Stocking
 09.31.55 AF === SSASET    Corrected filter tree:
 09.31.55 AF === SSASET    Root
 09.31.55 AF === SSASET    ==Filter
 09.31.55 AF === SSASET    ====Equal
 09.31.55 AF === SSASET    ======Field 65
 09.31.55 AF === SSASET    ======String constant:
 09.31.55 AF === SSASET    Vehicle Stocking
 09.31.55 AF === SSASET    Filter tree built for hierarchy [NewOld].[NewOld]
 09.31.55 AF === SSASET    Root
 09.31.55 AF === SSASET    ==And
 09.31.55 AF === SSASET    ====Ignore
 09.31.55 AF === SSASET    ======Equal
 09.31.55 AF === SSASET    ========Field 120
 09.31.55 AF === SSASET    ========String constant:
 09.31.55 AF === SSASET    New
 09.31.55 AF === SSASET    Corrected filter tree:
 09.31.55 AF === SSASET    Root
 09.31.55 AF === SSASET    ==Filter
 09.31.55 AF === SSASET    ====Equal
 09.31.55 AF === SSASET    ======Field 120
 09.31.55 AF === SSASET    ======String constant:
 09.31.55 AF === SSASET    New
 09.31.55 AF === SSASET    Filter list generated for MDX level:
 09.31.55 AF  [Group].[Group].[All].&[1].&[B].&[18]
 09.31.55 AF === SSASET    Filter list generated for MDX level:
 09.31.55 AF  [Time].[Time].[All].&[2010].&[Q3 2010].&[       201007]
 09.31.55 AF  :[Time].[Time].[All].&[2011].&[Q3 2011].&[       201107]
 09.31.55 AE === SSASET    MDX statement generated:
 09.31.55 AE  WITH
 09.31.55 AE  SET H1 as '{
 09.31.55 AE  FILTER({[Brand].[Brand].[Brand].ALLMEMBERS},
 09.31.55 AE  ([Brand].[Brand].CURRENTMEMBER.PROPERTIES("CAPTION") =
 09.31.55 AE  "TYPEA"))}'
 09.31.55 AE  SET H3 as '{
 09.31.55 AE  FILTER({[Country].[Country].[Country].ALLMEMBERS},
 09.31.55 AE  ([Country].[Country].CURRENTMEMBER.PROPERTIES("CAPTION") =
 09.31.55 AE  "NG"))}'
 09.31.55 AE  SET H4 as '{
 09.31.55 AE  FILTER({[Currency].[Currency].[Convt To Crrncy].ALLMEMBERS},
 09.31.55 AE  ([Currency].[Currency].CURRENTMEMBER.PROPERTIES("CAPTION") =
 09.31.55 AE  "USD"))}'
 09.31.55 AE  SET H7 as '{
 09.31.55 AE  FILTER({[Desc].[Desc].
 09.31.55 AE  ([Desc].[Desc].CURRENTMEMBER
 09.31.55 AE  "Vehicle Stocking"))}'
 09.31.55 AE  SET H10L3 as '
 09.31.55 AE  {[Group].[Group].[All].&[1].&[B].&[18]}'
 09.31.55 AE  SET H10 as 'H10L3'
 09.31.55 AE  SET H16 as '{
 09.31.55 AE  FILTER({[NewOld].[NewOld].[NewOld].ALLMEMBERS},
 09.31.55 AE  ([NewOld].[NewOld].CURRENTMEMBER.PROPERTIES("CAPTION") =
 09.31.55 AE  "New"))}'
 09.31.55 AE  SET H21L3 as '
 09.31.55 AE  {[Time].[Time].[All].&[2010].&[Q3 2010].&[       201007]
 09.31.55 AE  :[Time].[Time].[All].&[2011].&[Q3 2011].&[       201107]}'
 09.31.55 AE  SET H21 as 'H21L3'
 09.31.55 AE  SELECT {
 09.31.55 AE  [Measures].[Capital Amount2],
 09.31.55 AE  [Measures].[Capital Amount3],
 09.31.55 AE  [Measures].[Capital Amount4],
 09.31.55 AE  [Measures].[Amount]}
 09.31.55 AE  ON AXIS(0),
 09.31.55 AE  NON EMPTY CROSSJOIN(H1,
 09.31.55 AE  CROSSJOIN(H3,
 09.31.55 AE  CROSSJOIN(H4,
 09.31.55 AE  CROSSJOIN(H7,
 09.31.55 AE  CROSSJOIN(H10,
 09.31.55 AE  CROSSJOIN(H16,
 09.31.55 AE  H21))))))
 09.31.55 AE  DIMENSION PROPERTIES
 09.31.55 AE  MEMBER_CAPTION ON AXIS(1)
 09.31.55 AE  FROM [Sales]
 0 NUMBER OF RECORDS IN TABLE=       10  LINES=     10

Please, what can I do to return null as 0. I suspecting "NON EMPTY" as indicated in the trace log may be the cause.

August 05, 2011, 10:22 AM

EricH

Try this:

ENGINE MSOLAP SET EMPTY ON

August 05, 2011, 10:34 AM

umun

tried but no success yet. Thanks for your suggestion.

August 05, 2011, 02:29 PM

Prarie

Try this

SET ALL = ON
SET NODATA = 0

Prod:	WebFOCUS 7.6.10/8.1.04	Upgrade:	WebFOCUS 8.2.07	OS:	Linux	Outputs:	HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

- Using AS 8.2.01 on Windows 10 - IE11.
	in Focus since 1988