 |
|||||
|
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
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. | |||||
Read-Only TopicGo | Search | Notify | Tools |
| Returning 0 Values - Please help | Login/Join |
| Platinum Member |
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 | ||
|
| Virtuoso |
In what database is your data? Are your sure the Amount field contains 0 and not null? Thanks! Mickey
| |||||
|
| Platinum Member |
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 | |||
|
| Virtuoso |
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
| |||||
|
| Virtuoso |
You might want to take a look at this thread. Thuis concerns the same issue with a possible solution. GamP
| |||||||||
| ||||||||||
| Platinum Member |
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 | |||
|
| Expert |
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...
| |||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||
| Virtuoso |
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
| |||||
|
| Platinum Member |
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 | |||
|
| Platinum Member |
Try this: ENGINE MSOLAP SET EMPTY ON | |||
|
| Platinum Member |
tried but no success yet. Thanks for your suggestion. WebFOCUS 7.7.02 Windows/SQL/CUBES HTML/PDF/XLS | |||
|
| Virtuoso |
Try this SET ALL = ON SET NODATA = 0 | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |