Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
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, 2009Report 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, 2003Report 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, 2009Report 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, 2003Report 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: 1961 | Location: Netherlands | Registered: September 25, 2007Report 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, 2009Report 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.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report 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, 2003Report 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, 2009Report This Post
Platinum Member
posted Hide Post
Try this:

ENGINE MSOLAP SET EMPTY ON
 
Posts: 164 | Registered: March 26, 2003Report 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, 2009Report This Post
Virtuoso
posted Hide Post
Try this

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

Read-Only Read-Only Topic

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

Copyright © 1996-2020 Information Builders