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     [CLOSED] Show Values in the Report which are not present in the DB/Table

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Show Values in the Report which are not present in the DB/Table
 Login/Join
 
Ramkumar - Webfous
Guru
posted


If you see this report, Month and Year are By Fields and CAR1 CAR2 are across fields…

Code like

TABLE FILE XXX
SUM
COST
BY MONTH
BY YEAR
ACROSS CAR
END
-EXIT

Actually, July 10 has no data in the Database itself. But I need to show that as well in the Report like this, with all the across columns values as SPACE or Zeros(See the RED row).

What is the optimal way of getting this done ??

This message has been edited. Last edited by: Kerry,


Thanks,

Ramkumar.
WebFOCUS/Tableau
Webfocus 8 / 7.7.02
Unix, Windows
HTML/PDF/EXCEL/AHTML/XML/HTML5
 
Posts: 394 | Location: Chennai | Registered: December 02, 2009Report This Post
Anatess
Guru
posted Hide Post
You need to left-join a calendar table that contains each month as a record to the CARs table to get that result set.


WF 8.1.05 Windows
 
Posts: 333 | Location: Orlando, FL | Registered: October 17, 2006Report This Post
Doug
Expert
posted Hide Post
If I understand your question, then please consider "COLUMNS", as in: 
TABLE FILE CAR
SUM DCOST 
ACROSS COUNTRY COLUMNS 'ENGLAND' AND 'FRANCE' AND 'ITALY' AND 'JAPAN' AND 'W GERMANY' AND 'The USA'
END
Note that there's no data for 'The USA', but it's listed.




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Doug
Expert
posted Hide Post
BTW, this can be done with ROWS / OVER, as follows: BY COUNTRY ROWS 'ENGLAND' OVER 'FRANCE' OVER 'ITALY' OVER 'The USA'. Note this method can also be used for eliminating unwanted rows. Note that I omitted (no offense intended) 'JAPAN' and 'W GERMANY' this time.




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
njsden
Virtuoso
posted Hide Post
He also seems to need data for July even if it does not exist. So using BY ... ROWS ... might also help.

That, some McGyver or as Anatess said getting all needed combinations of Month/Year and OUTER join that to the actual measurements he needs. That actually sounds like MATCH .. OLD-OR-NEW ... there seems to be many an approach to this.


Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Doug
Expert
posted Hide Post
But, "sounds like MATCH .. OLD-OR-NEW cannot get info that's not in the DB. I'm thinking that, when the data "does not exist". it "does not exist". So, it, in turn cannont be extracted. The use of ROWS and COLUMNS is used a lot in "calendar" reports, where a month is missing.

So, Ramkumar - Webfous: What's your thoughts on this?
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Ramkumar - Webfous
Guru
posted Hide Post
Hi All,

Sorry for the much delayed reply.

Was off for a vacation, and was not lookin to the Forums past week.

I will try both the options. That left outer JOIN options seems to suit my requirement. But I Dynamically has to to create the CALENDAR focus mart based on the user inputs, and that is feasible I guess.

Thanks for all your overwhelmed response. Will try out options and let you know.


Thanks,

Ramkumar.
WebFOCUS/Tableau
Webfocus 8 / 7.7.02
Unix, Windows
HTML/PDF/EXCEL/AHTML/XML/HTML5
 
Posts: 394 | Location: Chennai | Registered: December 02, 2009Report This Post
Ramkumar - Webfous
Guru
posted Hide Post
Hi All,

Sorry for the much delayed reply.

Was off for a vacation, and was not lookin to the Forums past week.

I will try both the options. That left outer JOI
 
Posts: 394 | Location: Chennai | Registered: December 02, 2009Report This Post
Danny-SRL
Virtuoso
posted Hide Post
A perfect fit for McGuyver:

  
-* File Ramkumar01.fex
FILEDEF FSEQ DISK FOCALPOINT/FSEQ.MAS
TABLE FILE CAR
SUM SALES
BY COUNTRY 
BY SEATS 
ON TABLE HOLD AS H1
END

JOIN BLANK WITH SEATS IN H1 TO BLANK IN FSEQ AS M_
DEFINE FILE H1
BLANK/A1 WITH SEATS = ' ';
CSEATS/I3=DECODE COUNTER(1 2 2 4 3 5 ELSE 0);
CSALES/I5S=IF SEATS EQ CSEATS THEN SALES ELSE 0;
END
TABLE FILE H1
SUM CSALES 
BY COUNTRY
BY CSEATS
IF COUNTER LE 3
END


The FSEQ master (it is in the FOCALPOINT application):
  
 FILE=FSEQ, SUFFIX=FIX
  SEGNAME=SEG1
   FIELD=CONTROL, BLANK , A1, A1, $
  SEGNAME=SEG2, PARENT=SEG1, OCCURS=VARIABLE
   FIELD=WHATEVER, , A1, A1, $
   FIELD=COUNTER, ORDER, I4,  I4,$


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Anatess
Guru
posted Hide Post
I don't think you can just McGyver this because the only way you'll know that you have missing data is if you bounce it off a calendar. Make sense?

But, if your report is always going to have 12 rows, one row for each month of the calendar starting from January and end in December, then you can do the BY MONTH ROWS 'Jan' OVER 'Feb' OVER 'Mar'... etc.

If you're going by Month/Year with variable range, then the rows-over won't work. Joining to a calendar would work.

You can either build the calendar table using the minimum and maximum date values from your table with McGyver, or you can just join to an existing calendar table - data warehouses usually have these tables for payroll or project management applications.

This message has been edited. Last edited by: Anatess,


WF 8.1.05 Windows
 
Posts: 333 | Location: Orlando, FL | Registered: October 17, 2006Report 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     [CLOSED] Show Values in the Report which are not present in the DB/Table

Copyright © 1996-2020 Information Builders