Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Returning 0 Values - Please help
Go
New
Search
Notify
Tools
Reply
  
Returning 0 Values - Please help
 Login/Join
 
Platinum Member
posted
 
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.


WebFOCUS 7.7.02
Windows/SQL/CUBES
HTML/PDF/XLS
 
Posts: 117 | Registered: November 18, 2009Reply With QuoteReport This Post
Virtuoso
posted Hide Post
In what database is your data? Are your sure the Amount field contains 0 and not null?


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.


WebFOCUS 7.7.02
Windows/SQL/CUBES
HTML/PDF/XLS
 
Posts: 117 | Registered: November 18, 2009Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Reply With QuoteReport This Post
Virtuoso
posted Hide Post
You might want to take a look at this thread. Thuis concerns the same issue with a possible solution.


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1960 | Location: Netherlands | Registered: September 25, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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?


WebFOCUS 7.7.02
Windows/SQL/CUBES
HTML/PDF/XLS
 
Posts: 117 | Registered: November 18, 2009Reply With QuoteReport This Post
Expert
posted Hide Post
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


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6272 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.


WebFOCUS 7.7.02
Windows/SQL/CUBES
HTML/PDF/XLS
 
Posts: 117 | Registered: November 18, 2009Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Try this:

ENGINE MSOLAP SET EMPTY ON
 
Posts: 164 | Registered: March 26, 2003Reply With QuoteReport This Post
Platinum Member
posted Hide Post
tried but no success yet. Thanks for your suggestion.


WebFOCUS 7.7.02
Windows/SQL/CUBES
HTML/PDF/XLS
 
Posts: 117 | Registered: November 18, 2009Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Try this

SET ALL = ON
SET NODATA = 0
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Returning 0 Values - Please help

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.